client spid

Question

client spid

timingskey

SSCommitted

Points: 1708
More actions
December 5, 2002 at 3:07 pm

#159302

We have an application server that connects to one of our SQL Servers. Several different clients connect to teh application server and then access the database. So the hostname is always the name of the application server. Is there a way that I can identify which queries are coming from specific client machines?
Thank you,
Ben Reeder
Deere & Company
reederben@johndeere.com

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Antares686 SSC Guru Points: 125444 More actions · Answer 1

No, but you could build a mechanis into your app to capture the user, timeofstart and spid each time someone starts the app.

don1941 SSCarpal Tunnel Points: 4183 More actions · Answer 2

Depending on the traffic volume, you could try matching up the IP addresses from your web server with the time stamps of when rows are updated/inserted on your data base. it would be messy but it would be an rough approximation.

David Burrows SSC Guru Points: 65142 More actions · Answer 3

If you add APP=text to your SQL connection then the info appears under Application in EM (process info). I use this so I can associate spids with users.

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

timingskey SSCommitted Points: 1708 More actions · Answer 4

Thanks David. But I'm not sure what you mean by add "APP=text to your SQL connection." I'm a system DBA and not an application DBA. Does this pertain to the application connection, or the connection that I have to the SQL Server when I'm checking the connections?

Thanks again

David Burrows SSC Guru Points: 65142 More actions · Answer 5

Sorry about that but yes it pertains to the application connection to SQL.

e.g. using ADO might use either of these

Provider=SQLOLEDB;OLE DB Services=-1;Data Source=server;Initial Catalog=dbname;User ID=username;Password=password;APP=text

DSN=dsnname;UID=username;PWD=password;APP=text

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

Antares686 SSC Guru Points: 125444 More actions · Answer 6

My concern under doing the APP=text is you will want to fill the APP with the value of the clients id, ip or other identifying piece. But when you create a unique connection string like so you will upset the abilit of the app to use connection pooling and thus slowing overall connection speeds and times down.

Simon Sabin SSCrazy Eights Points: 8142 More actions · Answer 7

Classic 3 tier problem, and maintaining connection pooling.

To allow connection pooling the connection string has to be identical.

Therefore the best way of doing this is to use the context_info. Every time a query is made in the app you have to run a piece of SQL that sets the context info. I have wrapped this in an SP

CREATE PROCEDURE usp_SetContext @username varchar(128)

AS

DECLARE @Context varbinary(128)

SET @Context = Cast(@username as varbinary(128))

SET CONTEXT_INFO @Context

GO

You can then use select spid, cast(context_info as varchar(128)) from sysprocesses to find out who is doing what.

Unfortunately you have to change the app

Simon Sabin

Co-author of SQL Server 2000 XML Distilled

http://www.amazon.co.uk/exec/obidos/ASIN/1904347088

Simon Sabin
SQL Server MVP

http://sqlblogcasts.com/blogs/simons

timingskey SSCommitted Points: 1708 More actions · Answer 8

Thanks Simon. That's helpful. Unfortunately it is going to be difficult to get the 100s of application developers to change the apps which access our databases.

But maybe we can use your SP on a case-by-case basis.