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 client. Show all posts
Showing posts with label client. Show all posts
Friday, March 23, 2012
Friday, March 9, 2012
Query to display a field based on a parameter
I have a client table, with 8 fields, all of which contain a phone number.
Bad design, I know, but let's not get into that here. My problem now is
that in addition to those fields, we have a "Preferred" field, which simply
names the field of the preferred phone number. So we have fields like
HomePhone, BusinessPhone, Fax, and the Preferred field says "HomePhone"
How can I create a query so that the record displays only that field that is
preferred? I hope that makes sense.
For instance: John Doe, Home Phone 123-4567, Business Phone 765-4321,
Preferred "Home Phone", I want to the query to only display the name, and
home phone.
For Jane Doe, Home Phone 123-4567, Business Phone 765-4321, Preferred
"Business Phone", I want to the query to only display the name, and business
phone.
All of these records are displayed in a datagrid.
Thanks for your help.SELECT CASE Preferred
WHEN 'Home Phone' THEN HomePhone
WHEN 'Work Phone' THEN WorkPhone
..
WHEN 'Yet Another Phone' THEN YetAnotherPhone
End as PreferredPhone
FROM LotsOfPhones
Roy Harvey
Beacon Falls, CT
On Fri, 7 Apr 2006 16:29:47 -0600, "KatMagic" <SSKatMagic@.yahoo.com>
wrote:
>I have a client table, with 8 fields, all of which contain a phone number.
>Bad design, I know, but let's not get into that here. My problem now is
>that in addition to those fields, we have a "Preferred" field, which simply
>names the field of the preferred phone number. So we have fields like
>HomePhone, BusinessPhone, Fax, and the Preferred field says "HomePhone"
>How can I create a query so that the record displays only that field that i
s
>preferred? I hope that makes sense.
>For instance: John Doe, Home Phone 123-4567, Business Phone 765-4321,
>Preferred "Home Phone", I want to the query to only display the name, and
>home phone.
>For Jane Doe, Home Phone 123-4567, Business Phone 765-4321, Preferred
>"Business Phone", I want to the query to only display the name, and busines
s
>phone.
>All of these records are displayed in a datagrid.
>Thanks for your help.
>
Bad design, I know, but let's not get into that here. My problem now is
that in addition to those fields, we have a "Preferred" field, which simply
names the field of the preferred phone number. So we have fields like
HomePhone, BusinessPhone, Fax, and the Preferred field says "HomePhone"
How can I create a query so that the record displays only that field that is
preferred? I hope that makes sense.
For instance: John Doe, Home Phone 123-4567, Business Phone 765-4321,
Preferred "Home Phone", I want to the query to only display the name, and
home phone.
For Jane Doe, Home Phone 123-4567, Business Phone 765-4321, Preferred
"Business Phone", I want to the query to only display the name, and business
phone.
All of these records are displayed in a datagrid.
Thanks for your help.SELECT CASE Preferred
WHEN 'Home Phone' THEN HomePhone
WHEN 'Work Phone' THEN WorkPhone
..
WHEN 'Yet Another Phone' THEN YetAnotherPhone
End as PreferredPhone
FROM LotsOfPhones
Roy Harvey
Beacon Falls, CT
On Fri, 7 Apr 2006 16:29:47 -0600, "KatMagic" <SSKatMagic@.yahoo.com>
wrote:
>I have a client table, with 8 fields, all of which contain a phone number.
>Bad design, I know, but let's not get into that here. My problem now is
>that in addition to those fields, we have a "Preferred" field, which simply
>names the field of the preferred phone number. So we have fields like
>HomePhone, BusinessPhone, Fax, and the Preferred field says "HomePhone"
>How can I create a query so that the record displays only that field that i
s
>preferred? I hope that makes sense.
>For instance: John Doe, Home Phone 123-4567, Business Phone 765-4321,
>Preferred "Home Phone", I want to the query to only display the name, and
>home phone.
>For Jane Doe, Home Phone 123-4567, Business Phone 765-4321, Preferred
>"Business Phone", I want to the query to only display the name, and busines
s
>phone.
>All of these records are displayed in a datagrid.
>Thanks for your help.
>
Wednesday, March 7, 2012
Query Timeouts--Indexing Questions
I am helping a client with query timeout issues. Their tables have an
identity field used as a primary key, with a clustered index on the primary
key. This key really has no functional significance; it is just a number.
One example is that they search for customers by phone number, with an
additional index on the phone number. When I check the execution plan, it
shows a clustered index scan on the clustered index. Isn't a clustered index
s
the most efficient?
My question is whether I should drop the primary key and clustered index on
the identity column and create a clustered index on something of physical
significance like a phone number to get better performance?
Any ideas?
Larry Menzin
American Techsystems Corp.Place all the non-cluster index in a separate filegroup.
"Larry Menzin" wrote:
> I am helping a client with query timeout issues. Their tables have an
> identity field used as a primary key, with a clustered index on the primar
y
> key. This key really has no functional significance; it is just a number.
> One example is that they search for customers by phone number, with an
> additional index on the phone number. When I check the execution plan, it
> shows a clustered index scan on the clustered index. Isn't a clustered ind
ex
> s the most efficient?
> My question is whether I should drop the primary key and clustered index o
n
> the identity column and create a clustered index on something of physical
> significance like a phone number to get better performance?
> Any ideas?
> --
> Larry Menzin
> American Techsystems Corp.|||What indexes are used, and how they are used, depend on the columns returned
by the select statement and the nature of the WHERE condition.
SELECT * FROM [YourTable] WHERE [PhoneNumber] = '916-867-5309'
should result in a s of the nonclustered index on [PhoneNumber] with a
bookmark lookup on the clustered index. But, if you changed it to
SELECT * FROM [YourTable] WHERE [PhoneNumber] LIKE '%916-867-5309%'
you will most likely get a clustered index scan. Why? Because the SELECT is
looking for columns not covered by the non-clustered index.
If your application performs queries like
SELECT [CustomerName] FROM [YourTable]
WHERE [PhoneNumber] LIKE '%916-867-5309%'
consider using a composite non-clustered index using both [CustomerName] and
[PhoneNumber] columns.
"Larry Menzin" wrote:
> I am helping a client with query timeout issues. Their tables have an
> identity field used as a primary key, with a clustered index on the primar
y
> key. This key really has no functional significance; it is just a number.
> One example is that they search for customers by phone number, with an
> additional index on the phone number. When I check the execution plan, it
> shows a clustered index scan on the clustered index. Isn't a clustered ind
ex
> s the most efficient?
> My question is whether I should drop the primary key and clustered index o
n
> the identity column and create a clustered index on something of physical
> significance like a phone number to get better performance?
> Any ideas?
> --
> Larry Menzin
> American Techsystems Corp.|||My client is using some wildcards so that indexes are not used properly.
Since there can only be one clustered index per table and my client is using
it on the identity column, I'm stuck using non-clustered indexes.
What strategy is there for sealing with wildcards?
Larry Menzin
American Techsystems Corp.
"Mark Williams" wrote:
> What indexes are used, and how they are used, depend on the columns return
ed
> by the select statement and the nature of the WHERE condition.
> SELECT * FROM [YourTable] WHERE [PhoneNumber] = '916-867-5309'
> should result in a s of the nonclustered index on [PhoneNumber] with a
> bookmark lookup on the clustered index. But, if you changed it to
> SELECT * FROM [YourTable] WHERE [PhoneNumber] LIKE '%916-867-5309%'
> you will most likely get a clustered index scan. Why? Because the SELECT i
s
> looking for columns not covered by the non-clustered index.
> If your application performs queries like
> SELECT [CustomerName] FROM [YourTable]
> WHERE [PhoneNumber] LIKE '%916-867-5309%'
> consider using a composite non-clustered index using both [CustomerName] and
> [PhoneNumber] columns.
> --
>
> "Larry Menzin" wrote:
>|||You won't be able to avoid a scan of an index when using wildcards in the
WHERE condition.
Ideally, you would want an index *s* on a non-clusted index, with a
bookmark lookup to the clustered index. (There are some conditions, believe
it or not, where a scan of the clustered index will be faster, but that's
another story).
Again, your best option would be to create a composite non-clustered index
using the two columns involved in the customer phone - number lookup query.
"Larry Menzin" wrote:
> My client is using some wildcards so that indexes are not used properly.
> Since there can only be one clustered index per table and my client is usi
ng
> it on the identity column, I'm stuck using non-clustered indexes.
> What strategy is there for sealing with wildcards?
> --
> Larry Menzin
> American Techsystems Corp.
>
> "Mark Williams" wrote:
>
identity field used as a primary key, with a clustered index on the primary
key. This key really has no functional significance; it is just a number.
One example is that they search for customers by phone number, with an
additional index on the phone number. When I check the execution plan, it
shows a clustered index scan on the clustered index. Isn't a clustered index
s
the most efficient?My question is whether I should drop the primary key and clustered index on
the identity column and create a clustered index on something of physical
significance like a phone number to get better performance?
Any ideas?
Larry Menzin
American Techsystems Corp.Place all the non-cluster index in a separate filegroup.
"Larry Menzin" wrote:
> I am helping a client with query timeout issues. Their tables have an
> identity field used as a primary key, with a clustered index on the primar
y
> key. This key really has no functional significance; it is just a number.
> One example is that they search for customers by phone number, with an
> additional index on the phone number. When I check the execution plan, it
> shows a clustered index scan on the clustered index. Isn't a clustered ind
ex
> s
the most efficient?> My question is whether I should drop the primary key and clustered index o
n
> the identity column and create a clustered index on something of physical
> significance like a phone number to get better performance?
> Any ideas?
> --
> Larry Menzin
> American Techsystems Corp.|||What indexes are used, and how they are used, depend on the columns returned
by the select statement and the nature of the WHERE condition.
SELECT * FROM [YourTable] WHERE [PhoneNumber] = '916-867-5309'
should result in a s
of the nonclustered index on [PhoneNumber] with abookmark lookup on the clustered index. But, if you changed it to
SELECT * FROM [YourTable] WHERE [PhoneNumber] LIKE '%916-867-5309%'
you will most likely get a clustered index scan. Why? Because the SELECT is
looking for columns not covered by the non-clustered index.
If your application performs queries like
SELECT [CustomerName] FROM [YourTable]
WHERE [PhoneNumber] LIKE '%916-867-5309%'
consider using a composite non-clustered index using both [CustomerName] and
[PhoneNumber] columns.
"Larry Menzin" wrote:
> I am helping a client with query timeout issues. Their tables have an
> identity field used as a primary key, with a clustered index on the primar
y
> key. This key really has no functional significance; it is just a number.
> One example is that they search for customers by phone number, with an
> additional index on the phone number. When I check the execution plan, it
> shows a clustered index scan on the clustered index. Isn't a clustered ind
ex
> s
the most efficient?> My question is whether I should drop the primary key and clustered index o
n
> the identity column and create a clustered index on something of physical
> significance like a phone number to get better performance?
> Any ideas?
> --
> Larry Menzin
> American Techsystems Corp.|||My client is using some wildcards so that indexes are not used properly.
Since there can only be one clustered index per table and my client is using
it on the identity column, I'm stuck using non-clustered indexes.
What strategy is there for sealing with wildcards?
Larry Menzin
American Techsystems Corp.
"Mark Williams" wrote:
> What indexes are used, and how they are used, depend on the columns return
ed
> by the select statement and the nature of the WHERE condition.
> SELECT * FROM [YourTable] WHERE [PhoneNumber] = '916-867-5309'
> should result in a s
of the nonclustered index on [PhoneNumber] with a> bookmark lookup on the clustered index. But, if you changed it to
> SELECT * FROM [YourTable] WHERE [PhoneNumber] LIKE '%916-867-5309%'
> you will most likely get a clustered index scan. Why? Because the SELECT i
s
> looking for columns not covered by the non-clustered index.
> If your application performs queries like
> SELECT [CustomerName] FROM [YourTable]
> WHERE [PhoneNumber] LIKE '%916-867-5309%'
> consider using a composite non-clustered index using both [CustomerName] and
> [PhoneNumber] columns.
> --
>
> "Larry Menzin" wrote:
>|||You won't be able to avoid a scan of an index when using wildcards in the
WHERE condition.
Ideally, you would want an index *s
* on a non-clusted index, with abookmark lookup to the clustered index. (There are some conditions, believe
it or not, where a scan of the clustered index will be faster, but that's
another story).
Again, your best option would be to create a composite non-clustered index
using the two columns involved in the customer phone - number lookup query.
"Larry Menzin" wrote:
> My client is using some wildcards so that indexes are not used properly.
> Since there can only be one clustered index per table and my client is usi
ng
> it on the identity column, I'm stuck using non-clustered indexes.
> What strategy is there for sealing with wildcards?
> --
> Larry Menzin
> American Techsystems Corp.
>
> "Mark Williams" wrote:
>
Monday, February 20, 2012
Query syntax for count comparison
I have 2 tables, with 2 columns each: Seq# and Client.
I need the syntax of a query to compare count(Client) by Seq#, between Table1 and Table2.
Something like this (although this doesn't work):
Select Table1.Seq#
from Table1 inner join Table2 on Table1.Seq# = Table2.Seq#
where count (table1.Client) = count (table2.Client)
Is this possible? If so, please provide correct syntax.
Thank you!
It would be easier for us to understand your requirement, if you posted some
sample data to work with, and the expected resultset.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Ysandre" <ysandre@.hotmail.com> wrote in message
news:2DFCF9B8-D4C6-4330-932A-678B0687FC86@.microsoft.com...
I have 2 tables, with 2 columns each: Seq# and Client.
I need the syntax of a query to compare count(Client) by Seq#, between
Table1 and Table2.
Something like this (although this doesn't work):
Select Table1.Seq#
from Table1 inner join Table2 on Table1.Seq# = Table2.Seq#
where count (table1.Client) = count (table2.Client)
Is this possible? If so, please provide correct syntax.
Thank you!
|||Take a look at FULL OUTER JOIN in SQL Server Books Online. In simple cases
similar to yours, one can use it to compare data across different tables.
Anith
I need the syntax of a query to compare count(Client) by Seq#, between Table1 and Table2.
Something like this (although this doesn't work):
Select Table1.Seq#
from Table1 inner join Table2 on Table1.Seq# = Table2.Seq#
where count (table1.Client) = count (table2.Client)
Is this possible? If so, please provide correct syntax.
Thank you!
It would be easier for us to understand your requirement, if you posted some
sample data to work with, and the expected resultset.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Ysandre" <ysandre@.hotmail.com> wrote in message
news:2DFCF9B8-D4C6-4330-932A-678B0687FC86@.microsoft.com...
I have 2 tables, with 2 columns each: Seq# and Client.
I need the syntax of a query to compare count(Client) by Seq#, between
Table1 and Table2.
Something like this (although this doesn't work):
Select Table1.Seq#
from Table1 inner join Table2 on Table1.Seq# = Table2.Seq#
where count (table1.Client) = count (table2.Client)
Is this possible? If so, please provide correct syntax.
Thank you!
|||Take a look at FULL OUTER JOIN in SQL Server Books Online. In simple cases
similar to yours, one can use it to compare data across different tables.
Anith
Subscribe to:
Posts (Atom)