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 ;ApplicationName=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!
