Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

Worst Practice - Bad Connection Strings and Bad Info in Sysprocesses Expand / Collapse
Author
Message
Posted Sunday, September 15, 2002 12:00 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: 2 days ago @ 2:51 PM
Points: 6,790, Visits: 1,902
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

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Post #6800
Posted Sunday, September 22, 2002 3:28 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, August 30, 2005 1:20 AM
Points: 305, Visits: 1
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
cwbutler@cebec.com



C. Woody Butler, MCP
cwbutler@cwoodybutler.com
Post #41587
Posted Monday, September 23, 2002 12:53 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, July 12, 2013 5:09 AM
Points: 243, Visits: 178
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?




Post #41588
Posted Monday, September 23, 2002 6:43 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, November 5, 2008 12:45 PM
Points: 3, Visits: 12
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.




Post #41589
Posted Monday, September 23, 2002 7:31 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: 2 days ago @ 2:51 PM
Points: 6,790, Visits: 1,902
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
http://www.sqlservercentral.com/columnists/awarren/




Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Post #41590
Posted Monday, September 23, 2002 9:54 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, May 21, 2014 8:17 AM
Points: 297, Visits: 335
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
Post #41591
Posted Monday, September 23, 2002 3:22 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 1:23 PM
Points: 112, Visits: 79
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?




Post #41592
Posted Monday, September 23, 2002 5:48 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: 2 days ago @ 2:51 PM
Points: 6,790, Visits: 1,902
No idea. Might want to repost in the general forums, see if we can find an answer!

Andy
http://www.sqlservercentral.com/columnists/awarren/




Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Post #41593
Posted Tuesday, September 24, 2002 8:22 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, March 12, 2010 12:16 PM
Points: 187, Visits: 15
Good article. Any ideas on doing something similar with Access 2000? I have a mdb that is shared by many people.




Matt Dolan
Post #41594
Posted Tuesday, September 24, 2002 9:13 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: 2 days ago @ 2:51 PM
Points: 6,790, Visits: 1,902
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
http://www.sqlservercentral.com/columnists/awarren/




Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Post #41595
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse