September 15, 2002 at 12:00 am
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/awarren/worstpracticebadconnectionstringsandbadinfoinsyspr.asp>http://www.sqlservercentral.com/columnists/awarren/worstpracticebadconnectionstringsandbadinfoinsyspr.asp
September 22, 2002 at 3:28 pm
How does that play with connection pooling? I always understood that the connect strings had to be identical, and if you've got computer name in there I'd think that'd mess with the "identicallness" of the connect strings ;).
--woody
C. Woody Butler, MCP
C. Woody Butler, MCP
cwbutler@cwoodybutler.com
September 23, 2002 at 12:53 am
I've always tended to use a "minimalist" connection string. Usually, this will consist of the provider, the Data Source (or Server), and the Initial Catalog (or Database). Latterly, I've added in the application name.
I've always had Visual Studio create the connection string and then trimmed it manually.
I've been using .NET for about 6 months now and have just got to the point of releasing my application. I set it up so that by default, it would look at the final Production version of the database and by setting command line options, it would point to alternative development databases. Last week, I added a new test database so that we could perform final testing prior to release, and I couldn't make it connect, even though all I had to do was change the server name. I just got a message back saying "Network Error". Using the .NET Studio, I could see the database and query tables etc without any problem.
In the end, I had to expand the connection string out to what the Studio set it to originally to make it work.
I had:
"data source=SERVER;initial catalog=DATABASE;integrated security=SSPI;"
which works on all my other machines. To get it to work on the test machine, I had to use:
"data source=TESTSERVER;initial catalog=database;integrated security=SSPI;persist security info=False;workstation id=E08116-2K1;packet size=4096"
I asked our DBAs and they couldn't come up with an answer.
Why would this be so?
September 23, 2002 at 6:43 am
I 100% agree with setting the application name and having it based off of something so that when copy and pasted it changes. I don't necessarily agree if putting the workstation in, for the reason that if you put it in, you can make it misleading, ie change it to something that it's not. As for connection pooling, it's a non issue becuase if you are talking about a web server, it will make all the connections the same because it will have the same params. Also, for db monitoring, we have sometimes added the page name to the application so we can see what page is causing the most amount of sql work. Good article/tip.
September 23, 2002 at 7:31 am
Good point on the connection string. I plan to do some research into that in general for an upcoming article so I'll try to test that part as well. Not sure in the case of VB it would make a difference from what usually happens - it sets the workstation id for you.
Butler, I agree you "could" spoof it, but not setting it doesnt make that any more/less likely to happen. At least if you use the standard code each time it's consistent. Adding page name to the app name is a great idea (assuming you open/close the connection on the page and dont store at the session level!). Each page is really a mini app.
Andy
September 23, 2002 at 9:54 am
Andy: Good article. It's focused, clear, and you make some good points. Besides, you agree with my point of view.
Student of SQL and Golf, Master of Neither
Student of SQL and Golf, Master of Neither
September 23, 2002 at 3:22 pm
I enjoyed the article and agree with the idea of keeping things clean! Under the hostname field of the sysprocesses table I sometimes see my instance (compname/instance) and other times my server name minus the instance (compname). Any idea why?
September 23, 2002 at 5:48 pm
No idea. Might want to repost in the general forums, see if we can find an answer!
Andy
September 24, 2002 at 8:22 am
Good article. Any ideas on doing something similar with Access 2000? I have a mdb that is shared by many people.
Matt Dolan
September 24, 2002 at 9:13 am
I used to use Access 97 a lot, didnt ever use Access2k much (ADP's are cool though!). As I recall Access tends to be DSN centric for linked tables, but you can override that by providing a connection string. Same concept should work. Having problems doing it or just thinking about whether it would work?
Andy
September 24, 2002 at 9:28 am
I am using a System DSN for attached tables and SQL PassThru Queries. There is not an option when creating or modifying the WSID or AppID from the Access 2000 application. I can modify the ODBC connection with VBA at application startup, but this is a shared MDB file and I don't know what will happen when the second and third person starts the application and changes the settings. I have thought about putting the MDB on each user's cpu, but that is a lot of extra work do deal with when I need to make a program or schema change.
Matt
Matt Dolan
Matt Dolan
September 24, 2002 at 5:50 pm
Good question and one I dont know enough to answer. One way would be to try it and see:-) Probably worth finding an Access newsgroup, see if they can offer some help. If your solution is working dont know that I'd do much changing, though if you get to the point where scaling is an issue putting mdb on each machine should help a lot.
Andy
September 26, 2002 at 12:48 am
Good common sense advice.
I shall probably be stoned to death as a criminal heretic, but I do have quite a few Access 97 applications which use SQL Server through attached tables (why is everyone reaching down to pick up a rock?).
The problem there is that in the attached tables "remember" my connection string (ODBC, of course).
I suppose I could write a routine that reconnects the tables on startup changing the attributes for the workstation name.
Anyway, for all ADO based applications and for ODBC connections made through code, I think this article contains very sound and simple good advice.
September 26, 2002 at 1:25 am
It was mentioned above that specifying the workstation id in the connection string could affect connection pooling due to non-identical connection strings.
Please correct me if I'm wrong but the way I understand it is that a connection pool exists on a per-workstation basis. So each workstation maintains its own connection pool and so including the workstation id would not lead to un-reusable connections. As far as I know an SQL Server would not maintain a connection pool as this is something provided by data access technologies (eg. ADO, ADO.NET). Since connections invoked from different workstations would have different network addresses I would expect that these would never be reusable between workstations.
September 26, 2002 at 4:54 am
Confession is good for the soul! Actually Access 97 was what first got me thinking about the problem. We segment client data into different db's, each db has same schema. At some point I noticed the workstation id problem because we use a "shell" mdb that has the tables linked, when they start a new project they copy it to their machine (not end users, our IS team that does data cleaning, Access is great for this), which leads to the same workstation id. We also use some code that relinks the tables, either just a refresh or to point a different db. I sent them some code to determine the computername to make the relink code better, first few times it was used all the workstations showed up as "computername"! Ah well....
I think you're right on connection pooling though I have more exploring to do on the matter. One exception which really isnt would be if you're creating all the connections on an app server. Will be interesting to see.
Andy
Viewing 15 posts - 1 through 15 (of 36 total)
You must be logged in to reply to this topic. Login to reply