Worst Practice - Bad Connection Strings and Bad Info in Sysprocesses

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Andy Warren » Worst Practice - Bad Connection Strings and...

37 posts, Page 1 of 4

1 2 3 4 »»»

Worst Practice - Bad Connection Strings and Bad Info in Sysprocesses

Rate Topic

Display Mode

Topic Options

Author

Message

Andy Warren

Posted Sunday, September 15, 2002 12:00 AM

SSCertifiable

Group: Moderators
Last Login: Thursday, May 09, 2013 12:38 PM
Points: 6,462, Visits: 1,384

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
SQLShare - Learn One New Thing Each Day
SQLAndy - My Professional Blog
Connect with me on LinkedIn
Follow me on Twitter

Post #6800

cwbutler

Posted Sunday, September 22, 2002 3:28 PM

Old 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

Graham Cottle

Posted Monday, September 23, 2002 12:53 AM

SSC Veteran

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 7:27 AM
Points: 243, Visits: 177

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

butlerjd

Posted Monday, September 23, 2002 6:43 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, November 05, 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

Andy Warren

Posted Monday, September 23, 2002 7:31 AM

SSCertifiable

Group: Moderators
Last Login: Thursday, May 09, 2013 12:38 PM
Points: 6,462, Visits: 1,384

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
SQLShare - Learn One New Thing Each Day
SQLAndy - My Professional Blog
Connect with me on LinkedIn
Follow me on Twitter

Post #41590

BobAtDBS

Posted Monday, September 23, 2002 9:54 AM

SSC Veteran

Group: General Forum Members
Last Login: Monday, October 10, 2011 2:00 PM
Points: 297, Visits: 329

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

Terry Grignon

Posted Monday, September 23, 2002 3:22 PM

SSC Journeyman

Group: General Forum Members
Last Login: Friday, March 22, 2013 6:25 AM
Points: 91, Visits: 45

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

Andy Warren

Posted Monday, September 23, 2002 5:48 PM

SSCertifiable

Group: Moderators
Last Login: Thursday, May 09, 2013 12:38 PM
Points: 6,462, Visits: 1,384

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
SQLShare - Learn One New Thing Each Day
SQLAndy - My Professional Blog
Connect with me on LinkedIn
Follow me on Twitter

Post #41593

mdolan1959

Posted Tuesday, September 24, 2002 8:22 AM

SSC-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

Andy Warren

Posted Tuesday, September 24, 2002 9:13 AM

SSCertifiable

Group: Moderators
Last Login: Thursday, May 09, 2013 12:38 PM
Points: 6,462, Visits: 1,384

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
SQLShare - Learn One New Thing Each Day
SQLAndy - My Professional Blog
Connect with me on LinkedIn
Follow me on Twitter

Post #41595

« Prev Topic | Next Topic »

37 posts, Page 1 of 4

1 2 3 4 »»»

Permissions