Using @@SPID in IIS apps??

[Kjell Brandes]

Is there a way to get unique @@SPID to database when programming solutions
for IIS in ASP.
When trying I realize that the actual client/user against MSSQL database is
IIS, not the currently logged on user.
So my question is, can I get a unique @@SPID for every "real" client using
my webapp. when creating connection against database?

reg.
Kjell Brandes

 

[Bob Barrows [MVP]]

Is there a way to get unique @@SPID to database when programming
solutions for IIS in ASP.
When trying I realize that the actual client/user against MSSQL
database is IIS, not the currently logged on user.
So my question is, can I get a unique @@SPID for every "real" client
using my webapp. when creating connection against database?

If I understand you correctly, then you are using anonymous access, so
the answer is probably "no", unless you kill performance and scalability
by serializing all database access and disabling connection pooling
(definitely not recommended).

You may need to resort to using SessionID to identify users, or else
force them to login with unique login names.

 

[Kjell Brandes]

ok, thanks for the answer..

Another question.
I have tried to append sessionID to application Name "APP" in my
connectionstring. When trying to connect with 2 "real" clients. I get 2
different SPIDs. Is this because of that the name differs from each other?

Kjell

 

[Bob Barrows [MVP]]

Yes. Any differences in connection string will prevent connection
pooling. This will have an adverse affect on performance and scalability
since you will prevent connections from being shared, except by those
processes using the same connection string. Whether this adverse affect
is unacceptable can only be answered by you.

 

[Kjell Brandes]

Thanks for your time!!

Do you know if there is any "global variable" where you can ask for
ApplicationName?
like "SELECT @@SPID" for active connection.

Kjell

 

[Bob Barrows [MVP]]

I've never heard of one.
Did you find one in Books Online? That's where I would look. Just to be
sure, you should ask on a sql server group, making sure you let them know
what version of sql server you are using.

Why are you trying to tie a spid to a user? To me, this is a dangerous thing
to be trying to do and can lead to problems for your application. And it
seems to display an ignorance of how http and asp work (don't get all huffy
and defensive - I can only go by what you've written in these posts - I'm
not trying to insult you, just advise you that you may have more to learn).

Maybe this will be of interest:
http://www.aspfaq.com/show.asp?id=2491

 

[Kjell Brandes]

I can see why you asking...

The thing is that I maintains an application written in swedish with a lot
of businessrules for swedish laws. Such as calendar(worktime) and so on. This
application have to support other countries laws from now.
So I'm testing a method where I connects a usersession to a SPID in
database. Renaming all TABLES and making views with the same name as the
previous tablename. Adding a column to sourcetable telling the row for wich
county it applies to(CountryCode). By doing this I'm hoping that I wont need
to do that much coding in the application, adding countrycode to all WHERE in
SELECTs.

In the views I use SPID in combination with logged on user (usertable
contains countrycode) to resolve wich countrylaws I should apply on the
logged on user.

But to do this I have to get the value for the session saved on SQL-server.
Thats my actual problem, do you have any other ideas??

The database has 2 clienttypes, one win32 and one HTTPclient. The
win32client was no problem using SPIDs, but it seems to be a problem in
HTTPclient.

Kjell
PS. This is a bit of topic I know... Sorry!

 

[Alphi]

Or pass back the IP Address of the client and use that with every request to the database.

either that or create a GUID as soon as the web app starts and just keep passing that back with every request