So how do you get your app to work flawlessly on Bob’s computer with Office 2000 and Mary’s computer with Office 2007? A little trick called VBE. Visual Basic Extensions! We are going to loop through our projects references, when we encounter either a reference we know is Word, Excel or Outlook - we’re going to remove it, and re-add the latest version. We don’t need to worry if the reference is broken or not, since removing and re-adding costs very little.

We identify references by using a GUID. VBA has a GUID for each reference. Even if you are using Excel 2003 or Excel 2007 - the GUID remains the same. What changes is the Major and Minor versions when loading them via VBA code. However, by specifying the versions as 0, 0 we get to force VBA to load the LATEST version. Thus making our code dynamically load whichever version is installed on the target machine.

For now, I’m only going to worry about three GUIDs. Word, Outlook and Excel. I’ll write later on how to retrieve the GUID ids, but if your clever the following code is enough for you to work from. With our new found knowledge of what we’re doing, how do we do it? Here are two functions that will help you put the mystery together.

To remove references we may have to these three COM objects.
Function RemoveOfficeReferences()
Dim chkRef As Reference
For Each chkRef In Application.References
Select Case chkRef.Guid
Case "{00062FFF-0000-0000-C000-000000000046}"
Application.References.Remove chkRef
Case "{00020813-0000-0000-C000-000000000046}"
Application.References.Remove chkRef
Case "{00020905-0000-0000-C000-000000000046}"
Application.References.Remove chkRef
End Select
Next
End Function


To add references we need for these three COM objects.

Function AddOfficeReferences()
On Error GoTo errhandler
'this bit of code will add the references the first part is the
'Function is .AddFromGuid(Guid, Major, Minor)
'Leave the Major and Minor @0 to retrieve latest version.

End Function

These are just samples, there are much more eloquent ways to do the above, but this gives you an idea! Now go out, and develop without fear of version issues!

Here is the setup. Access 2007 Database - Upsized/Migrated to SQL Server (2008 in my case). Everything worked fine before, exporting a Text File, or a Spreadsheet of the data. Now when you click on the button - It crashes the application. The stranger part - it doesn’t happen EVERY TIME!?

No in fact this problem, doesn’t even happen if you use the OutPutTo function instead…….

So after, all the tinkering eliminating the much more obvious offenders, what was the little damn stinker?! A UnionQuery…. A pesky UnionQuery was bombing out the .Transfer. It worked fine when I ran it, Access didn’t complain about it, nor did SQL server.

What I found was a pesky little ORDER BY on one of the UNION members. This trashed the .Transfer for some reason. SO there you have it. Remove your ORDER BY in one of your UNION statements, and life should be grand again!

Most people are tearing their hair out after giving up their much loved T-Zones by mistake, or for temporarily switching a phone, where T-Zones wouldn’t work correctly. Then you wanted to go back, and viola it was no longer an option. Most people tried the Web2Go for 9.99 and got the not compatible screenie, yikes! WTF?!

Behold T-Mobile Internet for Phones. 9.99 for 100 MBs, yup that sucks. But as everyone knows, if T-Mobile doesn’t have a plan for your phone, this is what your going to be offered. However, if you do a little digging, you will soon find another 9.99 Data Plan, with UNLIMITED access. This plan uses the Internet2.VoiceStream.Com APN for access.

The plan name is the T-Mobile Unlimited Shadow data access plan. Thats right, the T-Mobile Shadow. A WINDOWS SmartPhone, with a Fav 5s home screen. Since this phone is really not pushed in to the SmartPhone market, I guess T-Mobile thought no one would notice this 9.99 UNLIMITED SMARTPHONE dataplan floating around.

How can you get it? The easiest way, is to simply log into your account, @ T-Mobile.com, select the “This is Not my Phone” option, and switch your phone to a T-Mobile Shadow. Go to Service Options, and viola 9.99 unlimited shadow data plan is now available to be added to your plan!

Sure its $4 more, but it beats whining, pleading, and endless calls to get your T-Zones back, plus it works!

The problem: You are using .NET 3.0 or 3.5, and you need to connect to an older Web Service. Specifically, you need to connect to a WSDL that is secured. The WebService address could be something such as: https://www.here.com/service.wsdl

When you use Visual Studio to make this connection it does many things incorrectly. The big error you are likely to encounter, less any specifics about the WSDL you are trying to connect to is:

Type TEXT/XML is not valid, Type Application/XML was expected.

In one easy little new Binding, we can make this all go away. For the novices here is a detailed explanation.

Take this custom binding here

Stick it into your Bindings section in App.Config and point the EndPoint to use that binding. You should be off and running in NO TIME!

Well here is the solution, and only a one big limit. If you have a Tab Page, it SHOULD have a control on it. This will break depending on the situation if a Tab Page is blank, only because this specific event may NOT be fired.


Dim pasttab As Integer
Private Sub Detail_Paint()
'the detail paint will be fired, when the tab is changed
Dim activetab As Integer
activetab = Me.TabCtl0.Value
If Not activetab = pasttab Then
pasttab = activetab
'put code for the fired event of a 'Click here

End If
End Sub


Leave any questions in the comments.

This post will cover, SQL Server Integrated Services (SSIS), and namely one question, with several requirements. You want to execute an SSIS package remotely, perhaps from an ASP.NET webpage? You have a need to do this adhoc, with multiple users. You need to set variables inside the package, hell why else would you go through all the hassle to run this on demand?!

Ok so this sounds like a big issue. First, this is not a beginners SSIS post. If you are looking for how to set custom parameters through execution of SSIS this is not the place to start. However that said, I will provide a brief overview of how it works.

SSIS allows you to use the DTEXEC command (a windows executable, and SQL server command) to execute SSIS packages stored in various locations (local, ssis package store). The following option in a command line allows you to set a variable at runtime.

/SET "\package.variables[VariableName]“;”Value”

By setting these commands at runtime, you just increased the power of your SSIS capabilties 10 fold. You can now user the power and localization of the SQL server to bear the grunt work of many data tasks, while you leave your application server’s resources left open for other user crunching. Now we are on the path for removing mindless data routines out of code, into a proper visual designer, and enchancing out application performance by offloading data routines to something that is dedicated to data!

Ok with that little bit of education out of the way lets continue! We’re assuming that you understand SSIS and SQL Server enough, to be able to Create an SSIS package, upload it to your server and execute it from a SQL Agent Job. This includes making a proxy. In addition to this, you will need a user that belongs to msdb, and can execute dts jobs. (dtsadmin)

Now for the code! This is converted, revamped from dtRemoteExec in C# found on codeplex. There were a few issues I found with this process in daily use. I have started to modify (it is by no means perfect) this code, which can be used in enterprise wide applications. Eventually I would like to have the code build the DTS Exec string for you, but this is still needed.

Here is a little explanation of how things work first! I almost forgot. You will need a DTS execution string like this.

"/SQL "\Maintenance Plans\SSISPACKAGE" /SERVER "b3studios" /USER username /PASSWORD password /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING V /SET "\package.variables[ExportLocation]“;”C:\Export”

I prefer to store the package with in the SSIS Package store. HOWEVER!!! Do not use a /DTS for calling the package, which is the default when using SSIS Package Store. This will almost certinly fail, or produce sporatic results. Instead, I prefer to execute the package from the SQL Server, using the /SQL command, let me know your thoughts in the comments.

Ok wheew mouth full, and I should rewrite a lot of this! But I’m too busy to do so! Here is the code, dubbed RemoteSSIS, based on dtexecremote, and rewritten in VB.NET

Here are the calls you will use from your application.


Dim oRemote As New RemoteSSIS

If oRemote.iscomplete Then
Return oRemote.ClearJob()
End If


Here is the code you can add to any module in your project. Add the following references,

Microsoft.SqlServer.ConnectionInfo
Microsoft.SqlSever.SMO
Microsoft.SqlSever.SmoEnum
Microsoft.SqlServer.SqlEnum

End Class


Today’s development on broadcatchR is going well. Thanks Josh for the name! Hopefully he gets time to work on this project. Right now I am currently working on getting a XMLTV feed going. Yes, that is right if anyone on the Internets stumbled across this I am working on getting some sort of XMLTV feed going. It maybe free to the Internets, but it maybe select cities.

I am currently working out the details. This will take some time, but I do have something rough working, and I hope to have the Twin Cities, programming guides for the next few weeks available at b3studios, through broadcatchR.

broadcatchR.com carries my latest blog post about broadcatchR related news, let me know what you think about the cross post? I’m lazy.