How to determine the last access date of a DB?

(SQL Server 2000, SP3a)
Hello all!
In my company, we have several development servers that, over time, get cluttered with a
*lot* of "scratch" databases. Is there some inherent mechanism that will show the last
time a database was accessed? If not, is there a relatively simple SQL-based solution
that I could craft that could help track that?
Thanks for any help you can provide!
John Peterson
You have to do some server level settings for that.
SQL Server can log event information for logon attempts and you can view it
by reviewing the errorlog. By turning on the
auditing level of SQL Server.
follow these steps to enable auditing of all/successfull connections with
Enterprise Manager in SQL Server:
Expand a server group.
Right-click a server, and then click Properties.
On the Security tab, under Audit Level, click all/success etc(required
option).
You must stop and restart the server for this setting to take effect.
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com
|||Login attempts won't tell you if a database has been used or
not. It will only tell you about server logins, not database
access. You would need to monitor this with profiler
going forward. I don't think there is any direct way to get
historical usage if you haven't been monitoring for such.
I suppose you could always drop the databases and then see
who calls complaining to determine if the databases are
being used. And you could use whatever criteria in
determining if you actually backup the databases before
dropping them. That could make for an interesting work day.
-Sue
On Thu, 8 Jul 2004 07:04:50 +0530, "Vishal Parkar"
<REMOVE_THIS_vgparkar@.yahoo.co.in> wrote:

>You have to do some server level settings for that.
>SQL Server can log event information for logon attempts and you can view it
>by reviewing the errorlog. By turning on the
>auditing level of SQL Server.
>follow these steps to enable auditing of all/successfull connections with
>Enterprise Manager in SQL Server:
>Expand a server group.
>Right-click a server, and then click Properties.
>On the Security tab, under Audit Level, click all/success etc(required
>option).
>You must stop and restart the server for this setting to take effect.
|||Thanks guys! Yeah...if I could put a trigger or something on sysprocesses, I could
potentially log access to specific databases. I suppose I could have a "monitor" wake up
every X seconds and read the distinct databases that were in use -- but that seems kind of
heavy handed...
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:4kdpe010faeumj5c62626q6oo83iegva7m@.4ax.com...
> Login attempts won't tell you if a database has been used or
> not. It will only tell you about server logins, not database
> access. You would need to monitor this with profiler
> going forward. I don't think there is any direct way to get
> historical usage if you haven't been monitoring for such.
> I suppose you could always drop the databases and then see
> who calls complaining to determine if the databases are
> being used. And you could use whatever criteria in
> determining if you actually backup the databases before
> dropping them. That could make for an interesting work day.
> -Sue
> On Thu, 8 Jul 2004 07:04:50 +0530, "Vishal Parkar"
> <REMOVE_THIS_vgparkar@.yahoo.co.in> wrote:
>
|||Triggers on system tables isn't supported though and
sysprocesses is only materialized when used so there
wouldn't even be something to put a trigger on.
-Sue
On Wed, 7 Jul 2004 21:52:56 -0700, "John Peterson"
<j0hnp@.comcast.net> wrote:

>Thanks guys! Yeah...if I could put a trigger or something on sysprocesses, I could
>potentially log access to specific databases. I suppose I could have a "monitor" wake up
>every X seconds and read the distinct databases that were in use -- but that seems kind of
>heavy handed...
>
>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
>news:4kdpe010faeumj5c62626q6oo83iegva7m@.4ax.com.. .
>
|||Understood. How about reading database logs? Would *that* show any activity? How would
I even go about doing that?
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:q4eqe0htf3hbgg1hme377t5ro5gpcvs351@.4ax.com... [vbcol=seagreen]
> Triggers on system tables isn't supported though and
> sysprocesses is only materialized when used so there
> wouldn't even be something to put a trigger on.
> -Sue
> On Wed, 7 Jul 2004 21:52:56 -0700, "John Peterson"
> <j0hnp@.comcast.net> wrote:
up[vbcol=seagreen]
of
>
|||Well...I noodled on using DBCC LOG to try and see if any of that information might be
germane, but I don't think so (everything is "reset" on a restart of SQL Server).
Then I looked at sysobjects.refdate, but that didn't pan out either.
Is there *any* way to determine the date/time that a particular table was last affected by
an INSERT/UPDATE/DELETE?
There seems to be a similar question here:
http://www.experts-exchange.com/Data..._20684794.html
But I don't want to sign up just to find out I *can't* do this... ;-)
Thanks for any help you can provide! :-)
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:%23Cd7QnPZEHA.2944@.TK2MSFTNGP11.phx.gbl...
> Understood. How about reading database logs? Would *that* show any activity? How
would[vbcol=seagreen]
> I even go about doing that?
>
> "Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> news:q4eqe0htf3hbgg1hme377t5ro5gpcvs351@.4ax.com...
wake[vbcol=seagreen]
> up
kind
> of
>
|||The SQL Server logs...not reliably. Unless the databases
have errors all the time that get logged. In terms of the
database log, I don't think you can't get timestamps using
dbcc loginfo or dbcc log. You can check object creation
dates in the database but that won't necessarily mean the
database isn't being used if no one is creating objects. And
even with the logs, if for some reason, they are only
selecting out of the database, that wouldn't do you any good
either.
If you want to read details of log files, you need to use
something like Log Explorer from lumigent -
www.lumigent.com
-Sue
On Thu, 8 Jul 2004 07:42:36 -0700, "John Peterson"
<j0hnp@.comcast.net> wrote:

>Understood. How about reading database logs? Would *that* show any activity? How would
>I even go about doing that?
>
>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
>news:q4eqe0htf3hbgg1hme377t5ro5gpcvs351@.4ax.com.. .
>up
>of
>

How to determine the last access date of a DB?

(SQL Server 2000, SP3a)
Hello all!
In my company, we have several development servers that, over time, get clut
tered with a
*lot* of "scratch" databases. Is there some inherent mechanism that will sh
ow the last
time a database was accessed? If not, is there a relatively simple SQL-base
d solution
that I could craft that could help track that?
Thanks for any help you can provide!
John PetersonYou have to do some server level settings for that.
SQL Server can log event information for logon attempts and you can view it
by reviewing the errorlog. By turning on the
auditing level of SQL Server.
follow these steps to enable auditing of all/successfull connections with
Enterprise Manager in SQL Server:
Expand a server group.
Right-click a server, and then click Properties.
On the Security tab, under Audit Level, click all/success etc(required
option).
You must stop and restart the server for this setting to take effect.
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com|||Login attempts won't tell you if a database has been used or
not. It will only tell you about server logins, not database
access. You would need to monitor this with profiler
going forward. I don't think there is any direct way to get
historical usage if you haven't been monitoring for such.
I suppose you could always drop the databases and then see
who calls complaining to determine if the databases are
being used. And you could use whatever criteria in
determining if you actually backup the databases before
dropping them. That could make for an interesting work day.
-Sue
On Thu, 8 Jul 2004 07:04:50 +0530, "Vishal Parkar"
<REMOVE_THIS_vgparkar@.yahoo.co.in> wrote:

>You have to do some server level settings for that.
>SQL Server can log event information for logon attempts and you can view it
>by reviewing the errorlog. By turning on the
>auditing level of SQL Server.
>follow these steps to enable auditing of all/successfull connections with
>Enterprise Manager in SQL Server:
>Expand a server group.
>Right-click a server, and then click Properties.
>On the Security tab, under Audit Level, click all/success etc(required
>option).
>You must stop and restart the server for this setting to take effect.|||Thanks guys! Yeah...if I could put a trigger or something on sysprocesses,
I could
potentially log access to specific databases. I suppose I could have a "mon
itor" wake up
every X seconds and read the distinct databases that were in use -- but that
seems kind of
heavy handed...
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:4kdpe010faeumj5c62626q6oo83iegva7m@.
4ax.com...
> Login attempts won't tell you if a database has been used or
> not. It will only tell you about server logins, not database
> access. You would need to monitor this with profiler
> going forward. I don't think there is any direct way to get
> historical usage if you haven't been monitoring for such.
> I suppose you could always drop the databases and then see
> who calls complaining to determine if the databases are
> being used. And you could use whatever criteria in
> determining if you actually backup the databases before
> dropping them. That could make for an interesting work day.
> -Sue
> On Thu, 8 Jul 2004 07:04:50 +0530, "Vishal Parkar"
> <REMOVE_THIS_vgparkar@.yahoo.co.in> wrote:
>
>|||Triggers on system tables isn't supported though and
sysprocesses is only materialized when used so there
wouldn't even be something to put a trigger on.
-Sue
On Wed, 7 Jul 2004 21:52:56 -0700, "John Peterson"
<j0hnp@.comcast.net> wrote:

>Thanks guys! Yeah...if I could put a trigger or something on sysprocesses,
I could
>potentially log access to specific databases. I suppose I could have a "mo
nitor" wake up
>every X seconds and read the distinct databases that were in use -- but tha
t seems kind of
>heavy handed...
>
>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> news:4kdpe010faeumj5c62626q6oo83iegva7m@.
4ax.com...
>|||Understood. How about reading database logs? Would *that* show any activit
y? How would
I even go about doing that?
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:q4eqe0htf3hbgg1hme377t5ro5gpcvs351@.
4ax.com...
> Triggers on system tables isn't supported though and
> sysprocesses is only materialized when used so there
> wouldn't even be something to put a trigger on.
> -Sue
> On Wed, 7 Jul 2004 21:52:56 -0700, "John Peterson"
> <j0hnp@.comcast.net> wrote:
>
up[vbcol=seagreen]
of[vbcol=seagreen]
>|||Well...I noodled on using DBCC LOG to try and see if any of that information
might be
germane, but I don't think so (everything is "reset" on a restart of SQL Ser
ver).
Then I looked at sysobjects.refdate, but that didn't pan out either.
Is there *any* way to determine the date/time that a particular table was la
st affected by
an INSERT/UPDATE/DELETE?
There seems to be a similar question here:
[url]http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_20684794.html[/ur
l]
But I don't want to sign up just to find out I *can't* do this... ;-)
Thanks for any help you can provide! :-)
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:%23Cd7QnPZEHA.2944@.TK2MSFTNGP11.phx.gbl...
> Understood. How about reading database logs? Would *that* show any activity? Ho
w
would
> I even go about doing that?
>
> "Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> news:q4eqe0htf3hbgg1hme377t5ro5gpcvs351@.
4ax.com...
wake[vbcol=seagreen]
> up
kind[vbcol=seagreen]
> of
>|||The SQL Server logs...not reliably. Unless the databases
have errors all the time that get logged. In terms of the
database log, I don't think you can't get timestamps using
dbcc loginfo or dbcc log. You can check object creation
dates in the database but that won't necessarily mean the
database isn't being used if no one is creating objects. And
even with the logs, if for some reason, they are only
selecting out of the database, that wouldn't do you any good
either.
If you want to read details of log files, you need to use
something like Log Explorer from lumigent -
www.lumigent.com
-Sue
On Thu, 8 Jul 2004 07:42:36 -0700, "John Peterson"
<j0hnp@.comcast.net> wrote:

>Understood. How about reading database logs? Would *that* show any activi
ty? How would
>I even go about doing that?
>
>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> news:q4eqe0htf3hbgg1hme377t5ro5gpcvs351@.
4ax.com...
>up
>of
>

How to determine the last access date of a DB?

(SQL Server 2000, SP3a)
Hello all!
In my company, we have several development servers that, over time, get cluttered with a
*lot* of "scratch" databases. Is there some inherent mechanism that will show the last
time a database was accessed? If not, is there a relatively simple SQL-based solution
that I could craft that could help track that?
Thanks for any help you can provide!
John PetersonYou have to do some server level settings for that.
SQL Server can log event information for logon attempts and you can view it
by reviewing the errorlog. By turning on the
auditing level of SQL Server.
follow these steps to enable auditing of all/successfull connections with
Enterprise Manager in SQL Server:
Expand a server group.
Right-click a server, and then click Properties.
On the Security tab, under Audit Level, click all/success etc(required
option).
You must stop and restart the server for this setting to take effect.
--
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com|||Login attempts won't tell you if a database has been used or
not. It will only tell you about server logins, not database
access. You would need to monitor this with profiler
going forward. I don't think there is any direct way to get
historical usage if you haven't been monitoring for such.
I suppose you could always drop the databases and then see
who calls complaining to determine if the databases are
being used. And you could use whatever criteria in
determining if you actually backup the databases before
dropping them. That could make for an interesting work day.
-Sue
On Thu, 8 Jul 2004 07:04:50 +0530, "Vishal Parkar"
<REMOVE_THIS_vgparkar@.yahoo.co.in> wrote:
>You have to do some server level settings for that.
>SQL Server can log event information for logon attempts and you can view it
>by reviewing the errorlog. By turning on the
>auditing level of SQL Server.
>follow these steps to enable auditing of all/successfull connections with
>Enterprise Manager in SQL Server:
>Expand a server group.
>Right-click a server, and then click Properties.
>On the Security tab, under Audit Level, click all/success etc(required
>option).
>You must stop and restart the server for this setting to take effect.|||Thanks guys! Yeah...if I could put a trigger or something on sysprocesses, I could
potentially log access to specific databases. I suppose I could have a "monitor" wake up
every X seconds and read the distinct databases that were in use -- but that seems kind of
heavy handed...
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:4kdpe010faeumj5c62626q6oo83iegva7m@.4ax.com...
> Login attempts won't tell you if a database has been used or
> not. It will only tell you about server logins, not database
> access. You would need to monitor this with profiler
> going forward. I don't think there is any direct way to get
> historical usage if you haven't been monitoring for such.
> I suppose you could always drop the databases and then see
> who calls complaining to determine if the databases are
> being used. And you could use whatever criteria in
> determining if you actually backup the databases before
> dropping them. That could make for an interesting work day.
> -Sue
> On Thu, 8 Jul 2004 07:04:50 +0530, "Vishal Parkar"
> <REMOVE_THIS_vgparkar@.yahoo.co.in> wrote:
> >You have to do some server level settings for that.
> >
> >SQL Server can log event information for logon attempts and you can view it
> >by reviewing the errorlog. By turning on the
> >auditing level of SQL Server.
> >
> >follow these steps to enable auditing of all/successfull connections with
> >Enterprise Manager in SQL Server:
> >
> >Expand a server group.
> >Right-click a server, and then click Properties.
> >On the Security tab, under Audit Level, click all/success etc(required
> >option).
> >
> >You must stop and restart the server for this setting to take effect.
>|||Triggers on system tables isn't supported though and
sysprocesses is only materialized when used so there
wouldn't even be something to put a trigger on.
-Sue
On Wed, 7 Jul 2004 21:52:56 -0700, "John Peterson"
<j0hnp@.comcast.net> wrote:
>Thanks guys! Yeah...if I could put a trigger or something on sysprocesses, I could
>potentially log access to specific databases. I suppose I could have a "monitor" wake up
>every X seconds and read the distinct databases that were in use -- but that seems kind of
>heavy handed...
>
>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
>news:4kdpe010faeumj5c62626q6oo83iegva7m@.4ax.com...
>> Login attempts won't tell you if a database has been used or
>> not. It will only tell you about server logins, not database
>> access. You would need to monitor this with profiler
>> going forward. I don't think there is any direct way to get
>> historical usage if you haven't been monitoring for such.
>> I suppose you could always drop the databases and then see
>> who calls complaining to determine if the databases are
>> being used. And you could use whatever criteria in
>> determining if you actually backup the databases before
>> dropping them. That could make for an interesting work day.
>> -Sue
>> On Thu, 8 Jul 2004 07:04:50 +0530, "Vishal Parkar"
>> <REMOVE_THIS_vgparkar@.yahoo.co.in> wrote:
>> >You have to do some server level settings for that.
>> >
>> >SQL Server can log event information for logon attempts and you can view it
>> >by reviewing the errorlog. By turning on the
>> >auditing level of SQL Server.
>> >
>> >follow these steps to enable auditing of all/successfull connections with
>> >Enterprise Manager in SQL Server:
>> >
>> >Expand a server group.
>> >Right-click a server, and then click Properties.
>> >On the Security tab, under Audit Level, click all/success etc(required
>> >option).
>> >
>> >You must stop and restart the server for this setting to take effect.
>|||Understood. How about reading database logs? Would *that* show any activity? How would
I even go about doing that?
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:q4eqe0htf3hbgg1hme377t5ro5gpcvs351@.4ax.com...
> Triggers on system tables isn't supported though and
> sysprocesses is only materialized when used so there
> wouldn't even be something to put a trigger on.
> -Sue
> On Wed, 7 Jul 2004 21:52:56 -0700, "John Peterson"
> <j0hnp@.comcast.net> wrote:
> >Thanks guys! Yeah...if I could put a trigger or something on sysprocesses, I could
> >potentially log access to specific databases. I suppose I could have a "monitor" wake
up
> >every X seconds and read the distinct databases that were in use -- but that seems kind
of
> >heavy handed...
> >
> >
> >"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> >news:4kdpe010faeumj5c62626q6oo83iegva7m@.4ax.com...
> >> Login attempts won't tell you if a database has been used or
> >> not. It will only tell you about server logins, not database
> >> access. You would need to monitor this with profiler
> >> going forward. I don't think there is any direct way to get
> >> historical usage if you haven't been monitoring for such.
> >> I suppose you could always drop the databases and then see
> >> who calls complaining to determine if the databases are
> >> being used. And you could use whatever criteria in
> >> determining if you actually backup the databases before
> >> dropping them. That could make for an interesting work day.
> >>
> >> -Sue
> >>
> >> On Thu, 8 Jul 2004 07:04:50 +0530, "Vishal Parkar"
> >> <REMOVE_THIS_vgparkar@.yahoo.co.in> wrote:
> >>
> >> >You have to do some server level settings for that.
> >> >
> >> >SQL Server can log event information for logon attempts and you can view it
> >> >by reviewing the errorlog. By turning on the
> >> >auditing level of SQL Server.
> >> >
> >> >follow these steps to enable auditing of all/successfull connections with
> >> >Enterprise Manager in SQL Server:
> >> >
> >> >Expand a server group.
> >> >Right-click a server, and then click Properties.
> >> >On the Security tab, under Audit Level, click all/success etc(required
> >> >option).
> >> >
> >> >You must stop and restart the server for this setting to take effect.
> >>
> >
>|||Well...I noodled on using DBCC LOG to try and see if any of that information might be
germane, but I don't think so (everything is "reset" on a restart of SQL Server).
Then I looked at sysobjects.refdate, but that didn't pan out either.
Is there *any* way to determine the date/time that a particular table was last affected by
an INSERT/UPDATE/DELETE?
There seems to be a similar question here:
http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_20684794.html
But I don't want to sign up just to find out I *can't* do this... ;-)
Thanks for any help you can provide! :-)
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:%23Cd7QnPZEHA.2944@.TK2MSFTNGP11.phx.gbl...
> Understood. How about reading database logs? Would *that* show any activity? How
would
> I even go about doing that?
>
> "Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> news:q4eqe0htf3hbgg1hme377t5ro5gpcvs351@.4ax.com...
> > Triggers on system tables isn't supported though and
> > sysprocesses is only materialized when used so there
> > wouldn't even be something to put a trigger on.
> >
> > -Sue
> >
> > On Wed, 7 Jul 2004 21:52:56 -0700, "John Peterson"
> > <j0hnp@.comcast.net> wrote:
> >
> > >Thanks guys! Yeah...if I could put a trigger or something on sysprocesses, I could
> > >potentially log access to specific databases. I suppose I could have a "monitor"
wake
> up
> > >every X seconds and read the distinct databases that were in use -- but that seems
kind
> of
> > >heavy handed...
> > >
> > >
> > >"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> > >news:4kdpe010faeumj5c62626q6oo83iegva7m@.4ax.com...
> > >> Login attempts won't tell you if a database has been used or
> > >> not. It will only tell you about server logins, not database
> > >> access. You would need to monitor this with profiler
> > >> going forward. I don't think there is any direct way to get
> > >> historical usage if you haven't been monitoring for such.
> > >> I suppose you could always drop the databases and then see
> > >> who calls complaining to determine if the databases are
> > >> being used. And you could use whatever criteria in
> > >> determining if you actually backup the databases before
> > >> dropping them. That could make for an interesting work day.
> > >>
> > >> -Sue
> > >>
> > >> On Thu, 8 Jul 2004 07:04:50 +0530, "Vishal Parkar"
> > >> <REMOVE_THIS_vgparkar@.yahoo.co.in> wrote:
> > >>
> > >> >You have to do some server level settings for that.
> > >> >
> > >> >SQL Server can log event information for logon attempts and you can view it
> > >> >by reviewing the errorlog. By turning on the
> > >> >auditing level of SQL Server.
> > >> >
> > >> >follow these steps to enable auditing of all/successfull connections with
> > >> >Enterprise Manager in SQL Server:
> > >> >
> > >> >Expand a server group.
> > >> >Right-click a server, and then click Properties.
> > >> >On the Security tab, under Audit Level, click all/success etc(required
> > >> >option).
> > >> >
> > >> >You must stop and restart the server for this setting to take effect.
> > >>
> > >
> >
>|||The SQL Server logs...not reliably. Unless the databases
have errors all the time that get logged. In terms of the
database log, I don't think you can't get timestamps using
dbcc loginfo or dbcc log. You can check object creation
dates in the database but that won't necessarily mean the
database isn't being used if no one is creating objects. And
even with the logs, if for some reason, they are only
selecting out of the database, that wouldn't do you any good
either.
If you want to read details of log files, you need to use
something like Log Explorer from lumigent -
www.lumigent.com
-Sue
On Thu, 8 Jul 2004 07:42:36 -0700, "John Peterson"
<j0hnp@.comcast.net> wrote:
>Understood. How about reading database logs? Would *that* show any activity? How would
>I even go about doing that?
>
>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
>news:q4eqe0htf3hbgg1hme377t5ro5gpcvs351@.4ax.com...
>> Triggers on system tables isn't supported though and
>> sysprocesses is only materialized when used so there
>> wouldn't even be something to put a trigger on.
>> -Sue
>> On Wed, 7 Jul 2004 21:52:56 -0700, "John Peterson"
>> <j0hnp@.comcast.net> wrote:
>> >Thanks guys! Yeah...if I could put a trigger or something on sysprocesses, I could
>> >potentially log access to specific databases. I suppose I could have a "monitor" wake
>up
>> >every X seconds and read the distinct databases that were in use -- but that seems kind
>of
>> >heavy handed...
>> >
>> >
>> >"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
>> >news:4kdpe010faeumj5c62626q6oo83iegva7m@.4ax.com...
>> >> Login attempts won't tell you if a database has been used or
>> >> not. It will only tell you about server logins, not database
>> >> access. You would need to monitor this with profiler
>> >> going forward. I don't think there is any direct way to get
>> >> historical usage if you haven't been monitoring for such.
>> >> I suppose you could always drop the databases and then see
>> >> who calls complaining to determine if the databases are
>> >> being used. And you could use whatever criteria in
>> >> determining if you actually backup the databases before
>> >> dropping them. That could make for an interesting work day.
>> >>
>> >> -Sue
>> >>
>> >> On Thu, 8 Jul 2004 07:04:50 +0530, "Vishal Parkar"
>> >> <REMOVE_THIS_vgparkar@.yahoo.co.in> wrote:
>> >>
>> >> >You have to do some server level settings for that.
>> >> >
>> >> >SQL Server can log event information for logon attempts and you can view it
>> >> >by reviewing the errorlog. By turning on the
>> >> >auditing level of SQL Server.
>> >> >
>> >> >follow these steps to enable auditing of all/successfull connections with
>> >> >Enterprise Manager in SQL Server:
>> >> >
>> >> >Expand a server group.
>> >> >Right-click a server, and then click Properties.
>> >> >On the Security tab, under Audit Level, click all/success etc(required
>> >> >option).
>> >> >
>> >> >You must stop and restart the server for this setting to take effect.
>> >>
>> >
>sql

How to determine Registry "root" for SQL Server instance?

(SQL Server 2000, SP3a)
Hello all!
I have a need to try and determine the appropriate Registry "root" for the c
urrent
connection. For example, on my local machine, the way I installed SQL Serve
r yields a
Registry entry like:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MS
SQLServer
However, on a machine in a clustered environment, the Registry entry looks l
ike:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Mi
crosoft SQL Server\MyIstance01
Is there an easy way to obtain this Registry value from within the current S
QL Server
connection context? That is, if I'm connected to a specific instance with Q
uery Analyzer,
is there some setting I can query (either through SQL Server itself or xp_re
gread) that
will reveal the correct Registry "root" for the current connection?
Thanks for any help you can provide!
John PetersonJohn,
Can't you just use xp_instance_regread and xp_instance_regwrite?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John Peterson" <j0hnp@.comcast.net> wrote in message news:O9vN225WEHA.1356@.TK2MSFTNGP09.phx.
gbl...
> (SQL Server 2000, SP3a)
> Hello all!
> I have a need to try and determine the appropriate Registry "root" for the
current
> connection. For example, on my local machine, the way I installed SQL Ser
ver yields a
> Registry entry like:
> HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MS
SQLServer
> However, on a machine in a clustered environment, the Registry entry looks
like:
> HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Mi
crosoft SQL Server\MyIstance01
> Is there an easy way to obtain this Registry value from within the current
SQL Server
> connection context? That is, if I'm connected to a specific instance with
Query Analyzer,
> is there some setting I can query (either through SQL Server itself or xp_
regread) that
> will reveal the correct Registry "root" for the current connection?
> Thanks for any help you can provide!
> John Peterson
>|||Tibor
Where is it documented? Did not find it on BOL. It requires a parameter.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eoY8V1NXEHA.1128@.TK2MSFTNGP10.phx.gbl...
> John,
> Can't you just use xp_instance_regread and xp_instance_regwrite?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "John Peterson" <j0hnp@.comcast.net> wrote in message
news:O9vN225WEHA.1356@.TK2MSFTNGP09.phx.gbl...
the current[vbcol=seagreen]
Server yields a[vbcol=seagreen]
looks like:[vbcol=seagreen]
current SQL Server[vbcol=seagreen]
with Query Analyzer,[vbcol=seagreen]
xp_regread) that[vbcol=seagreen]
>|||It is not documented (John, be warned...), so you have to search Google, spy
on EM using Profiler
and all the usual things to find out how to use it...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:usLjmjOXEHA.3012@.tk2msftngp13.phx.gbl.
.
> Tibor
> Where is it documented? Did not find it on BOL. It requires a parameter.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n
> message news:eoY8V1NXEHA.1128@.TK2MSFTNGP10.phx.gbl...
> news:O9vN225WEHA.1356@.TK2MSFTNGP09.phx.gbl...
> the current
> Server yields a
> looks like:
> current SQL Server
> with Query Analyzer,
> xp_regread) that
>|||Thanks Tibor! I wasn't aware of such a thing; it sounds like exactly what I
'm looking
for -- I'll do some "digging"! :-)
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message
news:%237vyGaPXEHA.1888@.TK2MSFTNGP11.phx.gbl...
> It is not documented (John, be warned...), so you have to search Google, spy on EM
using
Profiler
> and all the usual things to find out how to use it...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
news:usLjmjOXEHA.3012@.tk2msftngp13.phx.gbl...
>|||Hmmm...this doesn't seem to be doing the right thing in our clustered enviro
nment, and I'm
not quite sure why. Consider the following:
execute master.dbo.xp_instance_regread
N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\MSSQLSe
rver',
N'DefaultData'
When I run that on any of our servers that have just a default instance, it
appears to
work just fine. However, If I try to run that in our clustered environment
with named
instances, I get:
Msg 22001, Level 1, State 22001
RegQueryValueEx() returned error 2, 'The system cannot find the file specifi
ed.'
(0 row(s) affected)
Which leads me to believe that the specified key does not exist on the syste
m. But, if I
don't know what the appropriate key *is*, how do I find it?
Thanks for any additional help you can provide!
John Peterson
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:u7muiPRXEHA.3112@.tk2msftngp13.phx.gbl...
> Thanks Tibor! I wasn't aware of such a thing; it sounds like exactly what
I'm looking
> for -- I'll do some "digging"! :-)
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:%237vyGaPXEHA.1888@.TK2MSFTNGP11.phx.gbl...
using[vbcol=seagreen]
> Profiler
> news:usLjmjOXEHA.3012@.tk2msftngp13.phx.gbl...
>|||Did you run Profiler while doing the same in EM? I'd not aware of any differ
ence regarding these if
you are on a cluster.

> Which leads me to believe that the specified key does not exist on the sys
tem. But, if I
> don't know what the appropriate key *is*, how do I find it?
I'm afraid that I don't follow you here...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John Peterson" <j0hnp@.comcast.net> wrote in message news:O9m0IiRXEHA.1496@.TK2MSFTNGP10.phx.
gbl...
> Hmmm...this doesn't seem to be doing the right thing in our clustered envi
ronment, and I'm
> not quite sure why. Consider the following:
> execute master.dbo.xp_instance_regread
> N'HKEY_LOCAL_MACHINE',
> N'Software\Microsoft\MSSQLServer\MSSQLSe
rver',
> N'DefaultData'
> When I run that on any of our servers that have just a default instance, i
t appears to
> work just fine. However, If I try to run that in our clustered environmen
t with named
> instances, I get:
> Msg 22001, Level 1, State 22001
> RegQueryValueEx() returned error 2, 'The system cannot find the file speci
fied.'
> (0 row(s) affected)
> Which leads me to believe that the specified key does not exist on the sys
tem. But, if I
> don't know what the appropriate key *is*, how do I find it?
> Thanks for any additional help you can provide!
> John Peterson
>
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:u7muiPRXEHA.3112@.tk2msftngp13.phx.gbl...
> using
>

How to determine Registry "root" for SQL Server instance?

(SQL Server 2000, SP3a)
Hello all!
I have a need to try and determine the appropriate Registry "root" for the current
connection. For example, on my local machine, the way I installed SQL Server yields a
Registry entry like:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer
However, on a machine in a clustered environment, the Registry entry looks like:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MyIstance01
Is there an easy way to obtain this Registry value from within the current SQL Server
connection context? That is, if I'm connected to a specific instance with Query Analyzer,
is there some setting I can query (either through SQL Server itself or xp_regread) that
will reveal the correct Registry "root" for the current connection?
Thanks for any help you can provide!
John PetersonJohn,
Can't you just use xp_instance_regread and xp_instance_regwrite?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John Peterson" <j0hnp@.comcast.net> wrote in message news:O9vN225WEHA.1356@.TK2MSFTNGP09.phx.gbl...
> (SQL Server 2000, SP3a)
> Hello all!
> I have a need to try and determine the appropriate Registry "root" for the current
> connection. For example, on my local machine, the way I installed SQL Server yields a
> Registry entry like:
> HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer
> However, on a machine in a clustered environment, the Registry entry looks like:
> HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MyIstance01
> Is there an easy way to obtain this Registry value from within the current SQL Server
> connection context? That is, if I'm connected to a specific instance with Query Analyzer,
> is there some setting I can query (either through SQL Server itself or xp_regread) that
> will reveal the correct Registry "root" for the current connection?
> Thanks for any help you can provide!
> John Peterson
>|||Tibor
Where is it documented? Did not find it on BOL. It requires a parameter.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eoY8V1NXEHA.1128@.TK2MSFTNGP10.phx.gbl...
> John,
> Can't you just use xp_instance_regread and xp_instance_regwrite?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "John Peterson" <j0hnp@.comcast.net> wrote in message
news:O9vN225WEHA.1356@.TK2MSFTNGP09.phx.gbl...
> > (SQL Server 2000, SP3a)
> >
> > Hello all!
> >
> > I have a need to try and determine the appropriate Registry "root" for
the current
> > connection. For example, on my local machine, the way I installed SQL
Server yields a
> > Registry entry like:
> >
> > HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer
> >
> > However, on a machine in a clustered environment, the Registry entry
looks like:
> >
> > HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MyIstance01
> >
> > Is there an easy way to obtain this Registry value from within the
current SQL Server
> > connection context? That is, if I'm connected to a specific instance
with Query Analyzer,
> > is there some setting I can query (either through SQL Server itself or
xp_regread) that
> > will reveal the correct Registry "root" for the current connection?
> >
> > Thanks for any help you can provide!
> >
> > John Peterson
> >
> >
>|||It is not documented (John, be warned...), so you have to search Google, spy on EM using Profiler
and all the usual things to find out how to use it...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:usLjmjOXEHA.3012@.tk2msftngp13.phx.gbl...
> Tibor
> Where is it documented? Did not find it on BOL. It requires a parameter.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:eoY8V1NXEHA.1128@.TK2MSFTNGP10.phx.gbl...
> > John,
> >
> > Can't you just use xp_instance_regread and xp_instance_regwrite?
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:O9vN225WEHA.1356@.TK2MSFTNGP09.phx.gbl...
> > > (SQL Server 2000, SP3a)
> > >
> > > Hello all!
> > >
> > > I have a need to try and determine the appropriate Registry "root" for
> the current
> > > connection. For example, on my local machine, the way I installed SQL
> Server yields a
> > > Registry entry like:
> > >
> > > HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer
> > >
> > > However, on a machine in a clustered environment, the Registry entry
> looks like:
> > >
> > > HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MyIstance01
> > >
> > > Is there an easy way to obtain this Registry value from within the
> current SQL Server
> > > connection context? That is, if I'm connected to a specific instance
> with Query Analyzer,
> > > is there some setting I can query (either through SQL Server itself or
> xp_regread) that
> > > will reveal the correct Registry "root" for the current connection?
> > >
> > > Thanks for any help you can provide!
> > >
> > > John Peterson
> > >
> > >
> >
> >
>|||Thanks Tibor! I wasn't aware of such a thing; it sounds like exactly what I'm looking
for -- I'll do some "digging"! :-)
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
news:%237vyGaPXEHA.1888@.TK2MSFTNGP11.phx.gbl...
> It is not documented (John, be warned...), so you have to search Google, spy on EM using
Profiler
> and all the usual things to find out how to use it...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
news:usLjmjOXEHA.3012@.tk2msftngp13.phx.gbl...
> > Tibor
> > Where is it documented? Did not find it on BOL. It requires a parameter.
> >
> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> > message news:eoY8V1NXEHA.1128@.TK2MSFTNGP10.phx.gbl...
> > > John,
> > >
> > > Can't you just use xp_instance_regread and xp_instance_regwrite?
> > >
> > > --
> > > Tibor Karaszi, SQL Server MVP
> > > http://www.karaszi.com/sqlserver/default.asp
> > > http://www.solidqualitylearning.com/
> > >
> > >
> > > "John Peterson" <j0hnp@.comcast.net> wrote in message
> > news:O9vN225WEHA.1356@.TK2MSFTNGP09.phx.gbl...
> > > > (SQL Server 2000, SP3a)
> > > >
> > > > Hello all!
> > > >
> > > > I have a need to try and determine the appropriate Registry "root" for
> > the current
> > > > connection. For example, on my local machine, the way I installed SQL
> > Server yields a
> > > > Registry entry like:
> > > >
> > > > HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer
> > > >
> > > > However, on a machine in a clustered environment, the Registry entry
> > looks like:
> > > >
> > > > HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MyIstance01
> > > >
> > > > Is there an easy way to obtain this Registry value from within the
> > current SQL Server
> > > > connection context? That is, if I'm connected to a specific instance
> > with Query Analyzer,
> > > > is there some setting I can query (either through SQL Server itself or
> > xp_regread) that
> > > > will reveal the correct Registry "root" for the current connection?
> > > >
> > > > Thanks for any help you can provide!
> > > >
> > > > John Peterson
> > > >
> > > >
> > >
> > >
> >
> >
>|||Hmmm...this doesn't seem to be doing the right thing in our clustered environment, and I'm
not quite sure why. Consider the following:
execute master.dbo.xp_instance_regread
N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\MSSQLServer',
N'DefaultData'
When I run that on any of our servers that have just a default instance, it appears to
work just fine. However, If I try to run that in our clustered environment with named
instances, I get:
Msg 22001, Level 1, State 22001
RegQueryValueEx() returned error 2, 'The system cannot find the file specified.'
(0 row(s) affected)
Which leads me to believe that the specified key does not exist on the system. But, if I
don't know what the appropriate key *is*, how do I find it?
Thanks for any additional help you can provide!
John Peterson
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:u7muiPRXEHA.3112@.tk2msftngp13.phx.gbl...
> Thanks Tibor! I wasn't aware of such a thing; it sounds like exactly what I'm looking
> for -- I'll do some "digging"! :-)
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:%237vyGaPXEHA.1888@.TK2MSFTNGP11.phx.gbl...
> > It is not documented (John, be warned...), so you have to search Google, spy on EM
using
> Profiler
> > and all the usual things to find out how to use it...
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:usLjmjOXEHA.3012@.tk2msftngp13.phx.gbl...
> > > Tibor
> > > Where is it documented? Did not find it on BOL. It requires a parameter.
> > >
> > > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> > > message news:eoY8V1NXEHA.1128@.TK2MSFTNGP10.phx.gbl...
> > > > John,
> > > >
> > > > Can't you just use xp_instance_regread and xp_instance_regwrite?
> > > >
> > > > --
> > > > Tibor Karaszi, SQL Server MVP
> > > > http://www.karaszi.com/sqlserver/default.asp
> > > > http://www.solidqualitylearning.com/
> > > >
> > > >
> > > > "John Peterson" <j0hnp@.comcast.net> wrote in message
> > > news:O9vN225WEHA.1356@.TK2MSFTNGP09.phx.gbl...
> > > > > (SQL Server 2000, SP3a)
> > > > >
> > > > > Hello all!
> > > > >
> > > > > I have a need to try and determine the appropriate Registry "root" for
> > > the current
> > > > > connection. For example, on my local machine, the way I installed SQL
> > > Server yields a
> > > > > Registry entry like:
> > > > >
> > > > > HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer
> > > > >
> > > > > However, on a machine in a clustered environment, the Registry entry
> > > looks like:
> > > > >
> > > > > HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MyIstance01
> > > > >
> > > > > Is there an easy way to obtain this Registry value from within the
> > > current SQL Server
> > > > > connection context? That is, if I'm connected to a specific instance
> > > with Query Analyzer,
> > > > > is there some setting I can query (either through SQL Server itself or
> > > xp_regread) that
> > > > > will reveal the correct Registry "root" for the current connection?
> > > > >
> > > > > Thanks for any help you can provide!
> > > > >
> > > > > John Peterson
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||Did you run Profiler while doing the same in EM? I'd not aware of any difference regarding these if
you are on a cluster.
> Which leads me to believe that the specified key does not exist on the system. But, if I
> don't know what the appropriate key *is*, how do I find it?
I'm afraid that I don't follow you here...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John Peterson" <j0hnp@.comcast.net> wrote in message news:O9m0IiRXEHA.1496@.TK2MSFTNGP10.phx.gbl...
> Hmmm...this doesn't seem to be doing the right thing in our clustered environment, and I'm
> not quite sure why. Consider the following:
> execute master.dbo.xp_instance_regread
> N'HKEY_LOCAL_MACHINE',
> N'Software\Microsoft\MSSQLServer\MSSQLServer',
> N'DefaultData'
> When I run that on any of our servers that have just a default instance, it appears to
> work just fine. However, If I try to run that in our clustered environment with named
> instances, I get:
> Msg 22001, Level 1, State 22001
> RegQueryValueEx() returned error 2, 'The system cannot find the file specified.'
> (0 row(s) affected)
> Which leads me to believe that the specified key does not exist on the system. But, if I
> don't know what the appropriate key *is*, how do I find it?
> Thanks for any additional help you can provide!
> John Peterson
>
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:u7muiPRXEHA.3112@.tk2msftngp13.phx.gbl...
> > Thanks Tibor! I wasn't aware of such a thing; it sounds like exactly what I'm looking
> > for -- I'll do some "digging"! :-)
> >
> >
> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> > news:%237vyGaPXEHA.1888@.TK2MSFTNGP11.phx.gbl...
> > > It is not documented (John, be warned...), so you have to search Google, spy on EM
> using
> > Profiler
> > > and all the usual things to find out how to use it...
> > >
> > > --
> > > Tibor Karaszi, SQL Server MVP
> > > http://www.karaszi.com/sqlserver/default.asp
> > > http://www.solidqualitylearning.com/
> > >
> > >
> > > "Uri Dimant" <urid@.iscar.co.il> wrote in message
> > news:usLjmjOXEHA.3012@.tk2msftngp13.phx.gbl...
> > > > Tibor
> > > > Where is it documented? Did not find it on BOL. It requires a parameter.
> > > >
> > > > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> > > > message news:eoY8V1NXEHA.1128@.TK2MSFTNGP10.phx.gbl...
> > > > > John,
> > > > >
> > > > > Can't you just use xp_instance_regread and xp_instance_regwrite?
> > > > >
> > > > > --
> > > > > Tibor Karaszi, SQL Server MVP
> > > > > http://www.karaszi.com/sqlserver/default.asp
> > > > > http://www.solidqualitylearning.com/
> > > > >
> > > > >
> > > > > "John Peterson" <j0hnp@.comcast.net> wrote in message
> > > > news:O9vN225WEHA.1356@.TK2MSFTNGP09.phx.gbl...
> > > > > > (SQL Server 2000, SP3a)
> > > > > >
> > > > > > Hello all!
> > > > > >
> > > > > > I have a need to try and determine the appropriate Registry "root" for
> > > > the current
> > > > > > connection. For example, on my local machine, the way I installed SQL
> > > > Server yields a
> > > > > > Registry entry like:
> > > > > >
> > > > > > HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer
> > > > > >
> > > > > > However, on a machine in a clustered environment, the Registry entry
> > > > looks like:
> > > > > >
> > > > > > HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MyIstance01
> > > > > >
> > > > > > Is there an easy way to obtain this Registry value from within the
> > > > current SQL Server
> > > > > > connection context? That is, if I'm connected to a specific instance
> > > > with Query Analyzer,
> > > > > > is there some setting I can query (either through SQL Server itself or
> > > > xp_regread) that
> > > > > > will reveal the correct Registry "root" for the current connection?
> > > > > >
> > > > > > Thanks for any help you can provide!
> > > > > >
> > > > > > John Peterson
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>sql

How to determine Registry "root" for SQL Server instance?

(SQL Server 2000, SP3a)
Hello all!
I have a need to try and determine the appropriate Registry "root" for the current
connection. For example, on my local machine, the way I installed SQL Server yields a
Registry entry like:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer
However, on a machine in a clustered environment, the Registry entry looks like:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MyIstance01
Is there an easy way to obtain this Registry value from within the current SQL Server
connection context? That is, if I'm connected to a specific instance with Query Analyzer,
is there some setting I can query (either through SQL Server itself or xp_regread) that
will reveal the correct Registry "root" for the current connection?
Thanks for any help you can provide!
John Peterson
John,
Can't you just use xp_instance_regread and xp_instance_regwrite?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John Peterson" <j0hnp@.comcast.net> wrote in message news:O9vN225WEHA.1356@.TK2MSFTNGP09.phx.gbl...
> (SQL Server 2000, SP3a)
> Hello all!
> I have a need to try and determine the appropriate Registry "root" for the current
> connection. For example, on my local machine, the way I installed SQL Server yields a
> Registry entry like:
> HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer
> However, on a machine in a clustered environment, the Registry entry looks like:
> HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MyIstance01
> Is there an easy way to obtain this Registry value from within the current SQL Server
> connection context? That is, if I'm connected to a specific instance with Query Analyzer,
> is there some setting I can query (either through SQL Server itself or xp_regread) that
> will reveal the correct Registry "root" for the current connection?
> Thanks for any help you can provide!
> John Peterson
>
|||Tibor
Where is it documented? Did not find it on BOL. It requires a parameter.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eoY8V1NXEHA.1128@.TK2MSFTNGP10.phx.gbl...
> John,
> Can't you just use xp_instance_regread and xp_instance_regwrite?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "John Peterson" <j0hnp@.comcast.net> wrote in message
news:O9vN225WEHA.1356@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
the current[vbcol=seagreen]
Server yields a[vbcol=seagreen]
looks like:[vbcol=seagreen]
current SQL Server[vbcol=seagreen]
with Query Analyzer,[vbcol=seagreen]
xp_regread) that
>
|||It is not documented (John, be warned...), so you have to search Google, spy on EM using Profiler
and all the usual things to find out how to use it...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:usLjmjOXEHA.3012@.tk2msftngp13.phx.gbl...
> Tibor
> Where is it documented? Did not find it on BOL. It requires a parameter.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:eoY8V1NXEHA.1128@.TK2MSFTNGP10.phx.gbl...
> news:O9vN225WEHA.1356@.TK2MSFTNGP09.phx.gbl...
> the current
> Server yields a
> looks like:
> current SQL Server
> with Query Analyzer,
> xp_regread) that
>
|||Thanks Tibor! I wasn't aware of such a thing; it sounds like exactly what I'm looking
for -- I'll do some "digging"! :-)
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
news:%237vyGaPXEHA.1888@.TK2MSFTNGP11.phx.gbl...
> It is not documented (John, be warned...), so you have to search Google, spy on EM using
Profiler
> and all the usual things to find out how to use it...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
news:usLjmjOXEHA.3012@.tk2msftngp13.phx.gbl...
>
|||Hmmm...this doesn't seem to be doing the right thing in our clustered environment, and I'm
not quite sure why. Consider the following:
execute master.dbo.xp_instance_regread
N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\MSSQLServer',
N'DefaultData'
When I run that on any of our servers that have just a default instance, it appears to
work just fine. However, If I try to run that in our clustered environment with named
instances, I get:
Msg 22001, Level 1, State 22001
RegQueryValueEx() returned error 2, 'The system cannot find the file specified.'
(0 row(s) affected)
Which leads me to believe that the specified key does not exist on the system. But, if I
don't know what the appropriate key *is*, how do I find it?
Thanks for any additional help you can provide!
John Peterson
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:u7muiPRXEHA.3112@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> Thanks Tibor! I wasn't aware of such a thing; it sounds like exactly what I'm looking
> for -- I'll do some "digging"! :-)
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:%237vyGaPXEHA.1888@.TK2MSFTNGP11.phx.gbl...
using
> Profiler
> news:usLjmjOXEHA.3012@.tk2msftngp13.phx.gbl...
>
|||Did you run Profiler while doing the same in EM? I'd not aware of any difference regarding these if
you are on a cluster.

> Which leads me to believe that the specified key does not exist on the system. But, if I
> don't know what the appropriate key *is*, how do I find it?
I'm afraid that I don't follow you here...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John Peterson" <j0hnp@.comcast.net> wrote in message news:O9m0IiRXEHA.1496@.TK2MSFTNGP10.phx.gbl...
> Hmmm...this doesn't seem to be doing the right thing in our clustered environment, and I'm
> not quite sure why. Consider the following:
> execute master.dbo.xp_instance_regread
> N'HKEY_LOCAL_MACHINE',
> N'Software\Microsoft\MSSQLServer\MSSQLServer',
> N'DefaultData'
> When I run that on any of our servers that have just a default instance, it appears to
> work just fine. However, If I try to run that in our clustered environment with named
> instances, I get:
> Msg 22001, Level 1, State 22001
> RegQueryValueEx() returned error 2, 'The system cannot find the file specified.'
> (0 row(s) affected)
> Which leads me to believe that the specified key does not exist on the system. But, if I
> don't know what the appropriate key *is*, how do I find it?
> Thanks for any additional help you can provide!
> John Peterson
>
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:u7muiPRXEHA.3112@.tk2msftngp13.phx.gbl...
> using
>

How To Determine if SP3a installed on client PC?

How can I tell (preferably by a version attribute on some DLL or EXE) that
Service Pack 3a has been installed on a client PC? KB articles such as
321185 do a great job of showing how to use SERVERPROPERTY to determine what
version the SERVER is running, but that's not my question. I need to know if
Service Pack 3a has been installed on a CLIENT, not the SERVER.
Fred wrote:
> How can I tell (preferably by a version attribute on some DLL or EXE)
> that Service Pack 3a has been installed on a client PC? KB articles
> such as 321185 do a great job of showing how to use SERVERPROPERTY to
> determine what version the SERVER is running, but that's not my
> question. I need to know if Service Pack 3a has been installed on a
> CLIENT, not the SERVER.
Run Query Analyzer and go to Help | About. If it's version 760 it's
SP3a.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||Is there a programmatic way to determing this? We use CA's SDO tool to push
client software and we need to figure out a way to programmatically test (a
DLL or EXE or registry key entry I assume) to see if SQL Server 2000 Service
Pack 3a is already deployed on a particular client PC.
"David Gugick" wrote:

> Fred wrote:
> Run Query Analyzer and go to Help | About. If it's version 760 it's
> SP3a.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>
|||Fred wrote:
> Is there a programmatic way to determing this? We use CA's SDO tool
> to push client software and we need to figure out a way to
> programmatically test (a DLL or EXE or registry key entry I assume)
> to see if SQL Server 2000 Service Pack 3a is already deployed on a
> particular client PC.
>
Can your CA software check the file version of a file? If so, just check
the ISQLW.EXE file against the version you want (2000.80.760.0 for
SP3/SP3a)
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||SELECT @.@.VERSION
Star Fleet Admiral Q @. your Service!
http://www.google.com
Google is your "Friend"
"Fred" <Fred@.discussions.microsoft.com> wrote in message
news:B7E829FB-A6EF-4408-8628-61EFFF8F51D3@.microsoft.com...
> How can I tell (preferably by a version attribute on some DLL or EXE) that
> Service Pack 3a has been installed on a client PC? KB articles such as
> 321185 do a great job of showing how to use SERVERPROPERTY to determine
> what
> version the SERVER is running, but that's not my question. I need to know
> if
> Service Pack 3a has been installed on a CLIENT, not the SERVER.

How to determine ASC/DESC of a column in an index?

(SQL Server 2000, SP3a)
Hello all!
Is there any way to programmatically determine the ASC/DESC nature of a column in an
index?
For example, if I declare an index to be:
create unique clustered index [Test] on [MyTable](Field1 desc, Field2)
I'd like to determine, somehow, that Field1 is declared with the DESC keyword.
Thanks for any help you can provide!
John PetersonAfter a bit of fiddling around, the data *appears* to be in the [dbo].[sysindexes].[keys]
column. However, I'm not sure how to interpret this data. :-(
From this link:
http://sybooks.sybase.com/onlinebooks/group-asarc/asg1192e/asefun92/@.Generic__BookTextView/22111
I wish that SQL Server had a INDEX_COLORDER() built-in function. ;-)
Any additional thoughts?
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:ei2YX6LAEHA.1036@.TK2MSFTNGP10.phx.gbl...
> (SQL Server 2000, SP3a)
> Hello all!
> Is there any way to programmatically determine the ASC/DESC nature of a column in an
> index?
> For example, if I declare an index to be:
> create unique clustered index [Test] on [MyTable](Field1 desc, Field2)
> I'd like to determine, somehow, that Field1 is declared with the DESC keyword.
> Thanks for any help you can provide!
> John Peterson
>|||sp_helpindex will return this information. The index_keys
column returns the index keys - descending has a minus sign
following the column name.
The INDEXKEY_PROPERTY function can be used to determine if
an index key is descending or ascending. Sysindexkeys system
table can help you in determining the key_id.
In SQL-DMO, you can use the GetIndexedColumnDESC method of
the Index2 object to determine if a column in an index is
descending.
-Sue
On Tue, 2 Mar 2004 18:26:37 -0700, "John Peterson"
<j0hnp@.comcast.net> wrote:
>(SQL Server 2000, SP3a)
>Hello all!
>Is there any way to programmatically determine the ASC/DESC nature of a column in an
>index?
>For example, if I declare an index to be:
>create unique clustered index [Test] on [MyTable](Field1 desc, Field2)
>I'd like to determine, somehow, that Field1 is declared with the DESC keyword.
>Thanks for any help you can provide!
>John Peterson
>|||Thanks, Sue! That's *exactly* what I was looking for! I cobbled something based on the
31st byte of the 32 byte chunks in the sysindexes.keys, but it was rife with disaster.
(But seemed to work!)
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:r3ja40lp2nde0h8pbf1fjc92oqfkc252de@.4ax.com...
> sp_helpindex will return this information. The index_keys
> column returns the index keys - descending has a minus sign
> following the column name.
> The INDEXKEY_PROPERTY function can be used to determine if
> an index key is descending or ascending. Sysindexkeys system
> table can help you in determining the key_id.
> In SQL-DMO, you can use the GetIndexedColumnDESC method of
> the Index2 object to determine if a column in an index is
> descending.
> -Sue
> On Tue, 2 Mar 2004 18:26:37 -0700, "John Peterson"
> <j0hnp@.comcast.net> wrote:
> >(SQL Server 2000, SP3a)
> >
> >Hello all!
> >
> >Is there any way to programmatically determine the ASC/DESC nature of a column in an
> >index?
> >
> >For example, if I declare an index to be:
> >
> >create unique clustered index [Test] on [MyTable](Field1 desc, Field2)
> >
> >I'd like to determine, somehow, that Field1 is declared with the DESC keyword.
> >
> >Thanks for any help you can provide!
> >
> >John Peterson
> >
>

How to determine ASC/DESC of a column in an index?

(SQL Server 2000, SP3a)
Hello all!
Is there any way to programmatically determine the ASC/DESC nature of a colu
mn in an
index?
For example, if I declare an index to be:
create unique clustered index [Test] on [MyTable](Field1 desc, Field
2)
I'd like to determine, somehow, that Field1 is declared with the DESC keywor
d.
Thanks for any help you can provide!
John PetersonAfter a bit of fiddling around, the data *appears* to be in the [dbo].&#
91;sysindexes].[keys]
column. However, I'm not sure how to interpret this data. :-(
From this link:
http://sybooks.sybase.com/onlineboo...
TextView/22111
I wish that SQL Server had a INDEX_COLORDER() built-in function. ;-)
Any additional thoughts?
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:ei2YX6LAEHA.1036@.TK2MSFTNGP10.phx.gbl...
> (SQL Server 2000, SP3a)
> Hello all!
> Is there any way to programmatically determine the ASC/DESC nature of a co
lumn in an
> index?
> For example, if I declare an index to be:
> create unique clustered index [Test] on [MyTable](Field1 desc, Fie
ld2)
> I'd like to determine, somehow, that Field1 is declared with the DESC keyw
ord.
> Thanks for any help you can provide!
> John Peterson
>|||sp_helpindex will return this information. The index_keys
column returns the index keys - descending has a minus sign
following the column name.
The INDEXKEY_PROPERTY function can be used to determine if
an index key is descending or ascending. Sysindexkeys system
table can help you in determining the key_id.
In SQL-DMO, you can use the GetIndexedColumnDESC method of
the Index2 object to determine if a column in an index is
descending.
-Sue
On Tue, 2 Mar 2004 18:26:37 -0700, "John Peterson"
<j0hnp@.comcast.net> wrote:

>(SQL Server 2000, SP3a)
>Hello all!
>Is there any way to programmatically determine the ASC/DESC nature of a col
umn in an
>index?
>For example, if I declare an index to be:
>create unique clustered index [Test] on [MyTable](Field1 desc, Fiel
d2)
>I'd like to determine, somehow, that Field1 is declared with the DESC keywo
rd.
>Thanks for any help you can provide!
>John Peterson
>|||Thanks, Sue! That's *exactly* what I was looking for! I cobbled something
based on the
31st byte of the 32 byte chunks in the sysindexes.keys, but it was rife with
disaster.
(But seemed to work!)
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:r3ja40lp2nde0h8pbf1fjc92oqfkc252de@.
4ax.com...
> sp_helpindex will return this information. The index_keys
> column returns the index keys - descending has a minus sign
> following the column name.
> The INDEXKEY_PROPERTY function can be used to determine if
> an index key is descending or ascending. Sysindexkeys system
> table can help you in determining the key_id.
> In SQL-DMO, you can use the GetIndexedColumnDESC method of
> the Index2 object to determine if a column in an index is
> descending.
> -Sue
> On Tue, 2 Mar 2004 18:26:37 -0700, "John Peterson"
> <j0hnp@.comcast.net> wrote:
>
>sql