Worst Practice - Bad Connection Strings and Bad Info in Sysprocesses

,

I've discussed other worst practices (WP) in other articles here on the site

- things that strike me as being so far from being a best practice it would be

better to do almost anything besides what is being done! Here's a small one that

can make your life a bit nicer if fixed.

Do a quick select on sysprocesses and look at the program_name column. Most

of the system spids will have a null, you'll see Query Analyzer and maybe

Enterprise Manager. Have any that say 'Visual Basic'? Or other generic types of

names? Or if you look a little harder, any instances where you have the same

hostname but a different net address? If you have some of either you're a victim

of a baaaaad developer. 

How does program_name and hostname get populated? Something SQL figures out

on it's own? Not at all. When you set up a connection in your application you

can specify tons of different options in your connect string. Here's how you

open a simple ADO connection in VB:

'set reference to Microsoft ADO

dim cn as adodb.connection

set cn= new adobb.connection

cn.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=master;Data Source=EG\ONE"

Do a select against sysprocesses now and you'll get the following values:

    Program_Name = Visual Basic

    HostName=EG (the machine I'm running this from)

Even though I didn't specify either one. If you look at the cn.connectstring

property immediately after opening, you'll see it's been modified (by ADO or VB,

or both), to look like this:

Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=master;Data Source=EG\ONE;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=EG;Use Encryption for Data=False;Tag with column collation when possible=False

You can see that it's setting the workstation id to my computer name, and

that the string "Visual Basic" is not included anywhere. So far we

know that a generic, minimalist type connection string gets us the hostname

correctly and gives us a bogus (well, less than useful) program_name.

We can fix the program_name by adding ";Application Name=OurApplicationName"

to our connect string. We'll return to this in a bit.

Developers like to save time and one way they do that is by grabbing code out

of other projects they have worked on. Let's say just for worst case they grab

the following connect string from SomeApp and drop it into NewApp. What will you

see when a 100 or some employees run it?

Provider=SQLOLEDB.1;Integrated

Security=SSPI;Persist Security Info=False;Initial Catalog=master;Data

Source=EG\ONE;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=EG;Use Encryption for

Data=False;Tag with column collation when possible=False;Application

Name=SomeApp

Answer: A hundred spids showing hostname=EG and program_name=SomeApp. We've

populated the columns alright, but with the wrong values!

Here's a better way:

cn.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=master;Data Source=EG\ONE;Workstation ID=" & Environ$("COMPUTERNAME") & ";Application Name=" & App.Title & " v" & App.Major & "." & App.Minor & "." & App.Revision

Using the Environ$("COMPUTERNAME") function make sure we accurately

report the hostname. Using App.Title plus the version information gives

us not just an accurate application name, but a more informative one. You can

even make it easy to add by putting this function in a code module that gets

included in all your projects:

Private Function ConnectInfo() As String

ConnectInfo = ";Workstation ID=" & Environ$("COMPUTERNAME") & ";Application Name=" & App.Title & " v" & App.Major & "." & App.Minor & "." & App.Revision

End Function

Does it make a different to SQL? No. Affect performance? No. Cost a lot of

time to implement? No. Make the code more complex? No. How does it make life

better? Ruling out the possibility of wrong hostnames can save you a lot of

energy (or headache) when you're trying to track down the user that is running

the query from hell, or that you need to get disconnected so you can put the

database in single user mode or whatever. Or what if you want to drop some older

objects once everyone has upgraded to v2 of your app? With the version

information included you can see exactly who is running what.

Really boils down to about three options. The vanilla connect string at least

gets the hostname correct. A badly copied connect string can even trash that.

The new and improved version can make sure you get both pieces of data correct

every time.

As with all my articles..and ones about worst practices in particular...I'm

looking forward to your comments!

Rate

Share

Share

Rate