ColdFusion Muse

MS SQL and Instance Names - DTS Package Gotcha

I recently had to alter a large DTS package for a customer. He sent me an IP address and a username and password with access to the server and permissions to the databases and package. My first step was to open my little "client network utility" and add an alias for the database server, let's call it "marksDtsClient". Then I went and registered "marksDTSClient" in Enterprise manager so I could work with the package. I was able to open the package designer and see all the steps as well as the SQL in the execution modules. It's when I went to save changes I ran into a big "uh oh". I received a "server not found" type error.

Well that's just silly. Obviously I'm connected to the server and manipulating objects and browsing properties - why would it think the server did not exist? Let me "esplain it to you Lucy". As a part of this DTS the previous user had added 2 "connections" with separate properties. Based on what he wanted to do he used the appropriate connection. The connection property is that little "serverish" icon that exists on the page but may not have step arrows pointing to it or away from it (at least that is how he had designed it). They were almost unnoticeable. When you open the properties of an execution step you see the name of the step and a drop down that says "use connection" - which listed both of his 2 connections.

When I went to save a query the query parser was attempting to verify the steps in my SQL code - including the "use" steps based on the connection properties. Something about the connection properties was in error and causing that connection to fail - which means I could not save any changes to SQL code.

The Issue Identified

Looking carefully at the connection object in the SQL package designer you will note that one of them is "use Server". When I look at this property I noticed that the "server instance name" for his server was (naturally) not "marksDtsClient", but something else based on his own instance name (let's say "bobsSqlServer")- or perhaps based on the broadcast instance name of the server if he were on the local network (I had no way of knowing). The connection request was failing because it was pointed to an alias that was not valid for the machine I was using at the time. When it tried to make a connection to save the package it tried to resolve "bobsSqlServer". It failed to find it on the network (I was working remotely) and it failed to find it in my list of aliases - so it threw an error.

The Issue Resolved

To fix it I deleted the server registration in Enterprise manager, changed the alias in the "client network utility" to match the package ("bobsSqlServer") and then re-registered the server. I was able to save and execute without a problem from then on.

One Gotcha

Remember, if you are planning to schedule a DTS to run periodically using the scheduler you should use the actual instance name of the SQL server as your alias. If you do not, the server will have the same issue to deal with that I had above - it will be unable to resolve an alias it knows nothing about.