I have a client who needs to be able to run a query that shows a list of all
workstations (IP address, logon etc) who are currently accessing a specified
SQL database with logon / last activity time (if possible).
Most of the users are using a web application to access the database, so how
would we identify which users are accessing the database through a web
server. There are some however who are using the fat client application to
access the same database.
Is this possible?see if this is what you want?
select * from sysprocesses
--
"Mark - HIS" wrote:
> I have a client who needs to be able to run a query that shows a list of a
ll
> workstations (IP address, logon etc) who are currently accessing a specifi
ed
> SQL database with logon / last activity time (if possible).
> Most of the users are using a web application to access the database, so h
ow
> would we identify which users are accessing the database through a web
> server. There are some however who are using the fat client application to
> access the same database.
> Is this possible?
>|||If using SQL Server 2000 (not sure about the 2005 equivalent) you can
view this info through the GUI: Under the "Management" node there
should be a node called "Current Activity" and under that a node called
"Process Info" & this is the one you want. You should click on this
node to get a display that contains, among other things, the following
info:
* The SQL Server login that is associated with a process
* The host that the process connected from (if access is via a web
server the entry for the host should be the name of the web server)
* Time logged in
* Time of last batch/command execution
* Command that was last executed (double click on a process for this
information)
In SQL Server 2000 you have the sp_who (you also have the undocumented
sp_who2 stored proc in SQL Server 2000, not sure if it's in SQL Server
2005) which will materialize a result set. In this result set, the
following may be of interest to you:
* loginname - The SQL Server login associated with a process
* hostname - The host the process connected from
In addition, sp_who2 provides a column (lastbatch) that gives the date
and time when the last command/batch is executed for a process.
Quick example calls:
EXEC sp_who
GO
EXEC sp_who2
GO
Hope that helps a bit|||This table is in the master database.
So use
select * from master.dbo.sysprocesses.|||Anyone connecting to the web server will show up as the network user that
the web page is running under. If you disable anonymous access and have the
users login with their network credentials, I think these MAY show up as the
network account in SQL Server, but I have not tried this myself, so I could
be wrong.
The IP address will always be the web server itself, since that is where the
connection is made.
The SQL Server user will show up as whatever login the web server is using
to connect to the database. If you are using the same connection string for
all users, then that is the ID that will show up.
"Mark - HIS" <MarkHIS@.discussions.microsoft.com> wrote in message
news:48AAC7C6-FEA9-4090-9A85-DC502F55EE86@.microsoft.com...
> I have a client who needs to be able to run a query that shows a list of
all
> workstations (IP address, logon etc) who are currently accessing a
specified
> SQL database with logon / last activity time (if possible).
> Most of the users are using a web application to access the database, so
how
> would we identify which users are accessing the database through a web
> server. There are some however who are using the fat client application to
> access the same database.
> Is this possible?
>
Showing posts with label accessing. Show all posts
Showing posts with label accessing. Show all posts
Friday, March 23, 2012
Wednesday, March 7, 2012
Query times out when executed from 1 server
I have a weird problem.
It all started when one of our clients reported having an error
accessing one of our pages. We found that the cause of the error
resulted in a timed-out query.
When we execute a certain query using Query analyser from our Web
Server, the query times out.
When executing exactly the same query using any other server/machine
with Query Analyser, the query runs within 1 second.
Same SQL Server, same database, same query.
We've pinpointed the problem to a nvarchar column containing the
substring "CO." in one of its returned value.
When we remove the point from the "CO.", the query runs without
problems from our web server.
Anybody has an idea of why this could happen and how we can prevent
this from happening.
Many Thanx.Hi,
Probably your statistics might be outdated. Could you execute a update
statistics on that table and try executing the query.
See the execution plan for the index usage as well.
Thanks
Hari
SQL Server MVP
"Alex V" <alex.vezeau@.gmail.com> wrote in message
news:1128706938.421519.122200@.g49g2000cwa.googlegroups.com...
>I have a weird problem.
> It all started when one of our clients reported having an error
> accessing one of our pages. We found that the cause of the error
> resulted in a timed-out query.
> When we execute a certain query using Query analyser from our Web
> Server, the query times out.
> When executing exactly the same query using any other server/machine
> with Query Analyser, the query runs within 1 second.
> Same SQL Server, same database, same query.
> We've pinpointed the problem to a nvarchar column containing the
> substring "CO." in one of its returned value.
> When we remove the point from the "CO.", the query runs without
> problems from our web server.
> Anybody has an idea of why this could happen and how we can prevent
> this from happening.
> Many Thanx.
>|||Updated the statistics and problem still there.|||Other example of timeout:
Select name as NAME_USER FROM USERTABLE WHERE USERID = 4
This query times out on our webserver but not on any other server.
If I change the NAME_USER for NAMEUSER, it works on all servers.
If I change the 4 for a 6, Times out on the web server but works on all
the other servers.
If I change the 4 for a 6 and, NAMEUSER for NUSER, it works on all
servers ...
And it goes on and on ...
It all started when one of our clients reported having an error
accessing one of our pages. We found that the cause of the error
resulted in a timed-out query.
When we execute a certain query using Query analyser from our Web
Server, the query times out.
When executing exactly the same query using any other server/machine
with Query Analyser, the query runs within 1 second.
Same SQL Server, same database, same query.
We've pinpointed the problem to a nvarchar column containing the
substring "CO." in one of its returned value.
When we remove the point from the "CO.", the query runs without
problems from our web server.
Anybody has an idea of why this could happen and how we can prevent
this from happening.
Many Thanx.Hi,
Probably your statistics might be outdated. Could you execute a update
statistics on that table and try executing the query.
See the execution plan for the index usage as well.
Thanks
Hari
SQL Server MVP
"Alex V" <alex.vezeau@.gmail.com> wrote in message
news:1128706938.421519.122200@.g49g2000cwa.googlegroups.com...
>I have a weird problem.
> It all started when one of our clients reported having an error
> accessing one of our pages. We found that the cause of the error
> resulted in a timed-out query.
> When we execute a certain query using Query analyser from our Web
> Server, the query times out.
> When executing exactly the same query using any other server/machine
> with Query Analyser, the query runs within 1 second.
> Same SQL Server, same database, same query.
> We've pinpointed the problem to a nvarchar column containing the
> substring "CO." in one of its returned value.
> When we remove the point from the "CO.", the query runs without
> problems from our web server.
> Anybody has an idea of why this could happen and how we can prevent
> this from happening.
> Many Thanx.
>|||Updated the statistics and problem still there.|||Other example of timeout:
Select name as NAME_USER FROM USERTABLE WHERE USERID = 4
This query times out on our webserver but not on any other server.
If I change the NAME_USER for NAMEUSER, it works on all servers.
If I change the 4 for a 6, Times out on the web server but works on all
the other servers.
If I change the 4 for a 6 and, NAMEUSER for NUSER, it works on all
servers ...
And it goes on and on ...
Subscribe to:
Posts (Atom)