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 Tuesday, September 24, 2002 9:28 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
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
Post #41596
Posted Tuesday, September 24, 2002 5:50 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Yesterday @ 12:31 PM
Points: 6,808, Visits: 1,940
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
http://www.sqlservercentral.com/columnists/awarren/




Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Post #41597
Posted Thursday, September 26, 2002 12:48 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, November 16, 2004 2:54 AM
Points: 6, Visits: 1
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.




Post #41598
Posted Thursday, September 26, 2002 1:25 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Thursday, May 12, 2011 10:17 AM
Points: 128, Visits: 5
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.




Post #41599
Posted Thursday, September 26, 2002 4:54 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Yesterday @ 12:31 PM
Points: 6,808, Visits: 1,940
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
http://www.sqlservercentral.com/columnists/awarren/




Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Post #41600
Posted Thursday, January 2, 2003 9:43 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 4:19 AM
Points: 105, Visits: 75
Can you do this with VBScript in asp pages as it seems to reflect good practice!


Post #41601
Posted Thursday, January 2, 2003 11:17 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Yesterday @ 12:31 PM
Points: 6,808, Visits: 1,940
Sure. Pretty common to include the requesting IP address to make it more helpful.

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




Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Post #41602
Posted Friday, August 1, 2003 1:28 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, June 6, 2005 12:47 AM
Points: 28, Visits: 1
Good idea! I am on the start point of the big databse project now and this will for sure save my time and will save me from a headache.
Thank you.
P.S. Where is possible to read about what parameters can we put into connection string? Because before I've read this article I had no idea about application name and some other parameters.




To change the world find its source code...
Post #41603
Posted Friday, August 1, 2003 2:12 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, December 1, 2014 4:49 AM
Points: 589, Visits: 404
* BLUSH * Big Time!
This is ripe coming from a DBA who still develops, but I'd often looked at the sysprocesses and wondered whether I could get the additional information to appear there - a severe case of RTFM!! Guilty as charged m'lud!
Isn't is amazing how quickly I can now respond right down to the desk when there are problems by identifying the precise user causing locks and slow processes within seconds. Eveyone will soon think I'm spying on them (more than I do now anyway).
Great artice, and an example of how a small change can make a huge impact.




---------------------------------------
It is by caffeine alone I set my mind in motion.
It is by the Beans of Java that thoughts acquire speed,
the hands acquire shaking, the shaking becomes a warning.
It is by caffeine alone I set my mind in motion.
Post #41604
Posted Friday, August 1, 2003 2:25 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Monday, December 22, 2014 6:50 AM
Points: 7,057, Visits: 7,288
Good article Andy. My apps use common code for connection strings so adding this would be easy for me. As I do not have a large user base for my apps I track usage by workstation ID/database and a quick phone call usally identifies what the user is doing. Must admit though I have been using the app/page name addition in asp for some years (not to steal your thunder though!) and makes diagnosing easy but as already stated it will increase your connections from web server even with connection pooling.

BTW I did read somewhere (can't remmeber where) that there were problems with switching on connection pooling with the later versions of MDAC which cause connection failure and network errors.



Edited by - davidburrows on 08/01/2003 02:25:48 AM



Far away is close at hand in the images of elsewhere.

Anon.

Post #41605
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse