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 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 MSDE 2K running

Hi All
I need to check if MSDE2K service is running on a stand-alone computer using
VB6
I have tried using SQLDMO.ListAvailableServers but find it very
unreliable...
Dim oApp As SQLDMO.Application
Dim oNames As SQLDMO.NameList
Set oApp = CreateObject("SQLDMO.Application")
Set oNames = oApp.ListAvailableSQLServers()
MsgBox oNames.count
If I first run this code it detects my MSDE service oNames.count = 1
(correct)
If I stop MSDE, this code returns oNames.count = 0 (correct)
If I restart MSDE (icon indicates running) oNames.count still returns 0
(incorrect)
Any ideas
Regards
Steve
I know it's not the best way, but how about just writing an ADO application
that executes a test query in one of the databases? If your app fails to
connect to MSDE, then you know you have a problem.
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"steve" <sfrancis@.bigpond.net.au> wrote in message
news:etCwYZcjFHA.320@.TK2MSFTNGP09.phx.gbl...
Hi All
I need to check if MSDE2K service is running on a stand-alone computer using
VB6
I have tried using SQLDMO.ListAvailableServers but find it very
unreliable...
Dim oApp As SQLDMO.Application
Dim oNames As SQLDMO.NameList
Set oApp = CreateObject("SQLDMO.Application")
Set oNames = oApp.ListAvailableSQLServers()
MsgBox oNames.count
If I first run this code it detects my MSDE service oNames.count = 1
(correct)
If I stop MSDE, this code returns oNames.count = 0 (correct)
If I restart MSDE (icon indicates running) oNames.count still returns 0
(incorrect)
Any ideas
Regards
Steve
|||hi Steve,
steve wrote:
> Hi All
> I need to check if MSDE2K service is running on a stand-alone
> computer using VB6
> I have tried using SQLDMO.ListAvailableServers but find it very
> unreliable...
> Dim oApp As SQLDMO.Application
> Dim oNames As SQLDMO.NameList
> Set oApp = CreateObject("SQLDMO.Application")
> Set oNames = oApp.ListAvailableSQLServers()
> MsgBox oNames.count
> If I first run this code it detects my MSDE service oNames.count = 1
> (correct)
> If I stop MSDE, this code returns oNames.count = 0 (correct)
> If I restart MSDE (icon indicates running) oNames.count still returns
> 0 (incorrect)
you could use the SQLDMOSQLServer Status property,
http://msdn.microsoft.com/library/de..._p_s_769l.asp,
but this requires you to be already connected ot the SQL Server instance..
and, as you already saw, the ListAvailableServers is not reliable, because
of the nature of the broadcast call of the ODBC SQLBrowseConnect api used by
the DMO method, where the timeframe window is involved as well...
ListAvailableServer uses ODBC function SQLBrowseConnect() provided by ODBC
libraries installed by MDAC;
this is a mechanism working in broadcast calls, which result never are
conclusive and consistent, becouse results are influenced of various
servers's answer states, answer time, etc.
Until Mdac 2.5, SQLBrowseConnect function works based on a NetBIOS
broadcast, on which SQL Servers respond (Default protocol for SQL Server
7.0), while in SQL Server 2000 the rules changed, because the default client
protocol changed to TCP/IP and now a UDP broadcast is used, beside a NetBIOS
broadcast, listening on port 1434:
which is using a UDP broadcast on port 1434, if instance do not listen or
not respond on time they will not be part of the enumeration.
Some basic rules for 7.0 are:
- SQL Servers have to be running on Windows NT or Windows 2000 and have to
listen on Named Pipes, that is why in 7.0 Windows 9x SQL Servers will never
show up, because they do not listen on Named Pipes.
- The SQL Server has to be running in order to respond on the broadcast.
There is a gray window of 15 minutes after shutdown, where a browse master
in the domain may respond on the broadcast and answer.
- If you have routers in your network, that do not pass on NetBIOS
broadcasts, this might limit your scope of the broadcast.
- Only servers within the same NT domain (or trust) will get enumerated.
In SQL Server 2000 using MDAC 2.6 this changes a little, because now the
default protocol has been changed to be TCP/IP sockets and instead of a
NetBIOS broadcast, they use a TCP UDP to detect the servers. The same logic
still applies roughly.
- SQL Server that are running
- SQL Server that listening on TCP/IP
- Running on Windows NT or Windows 2000 or Windows 9x
- If you use routers and these are configured not to pass UDP broadcasts,
only machines within the same subnet show up.
Upgrading to Service Pack 2 of SQL Server 2000 is required in order to have
..ListAvailableServer method to work properly, becouse precding release of
Sql-DMO Components of Sql Server 2000 present a bug in this area.
Courtesy of Mr. Gert E.R. Drapers
further Information at
http://sqldev.net/misc.htm
to the besto of my knowledge, as you can see from
http://msdn.microsoft.com/library/de...ob_s_7igk.asp,
SQLServer object does not directly exposes a disconnected property to get
it's state, so you have to connect (and eventually use the Status method,,,
but youll''be already connected)..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.14.0 - DbaMgr ver 0.59.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
sql

how to delete push subscription

Hi All:
i did disable publishing and distribution , but still leave a push
subscription in DB , how can i delete it , otherwise i can not modify the
Database which is doing push subscription.
Cheers
nick
Nick,
try using sp_removedbreplication.
Rgds,
Paul Ibison
"Nick" <fsheng@.ebreathe.co.nz> wrote in message
news:Og7BO7wCFHA.3728@.TK2MSFTNGP14.phx.gbl...
> Hi All:
> i did disable publishing and distribution , but still leave a push
> subscription in DB , how can i delete it , otherwise i can not modify the
> Database which is doing push subscription.
>
> Cheers
> nick
>
|||Thanks Paul
it works.
Cheers
nick
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:u7L9TRxCFHA.2072@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> Nick,
> try using sp_removedbreplication.
> Rgds,
> Paul Ibison
> "Nick" <fsheng@.ebreathe.co.nz> wrote in message
> news:Og7BO7wCFHA.3728@.TK2MSFTNGP14.phx.gbl...
the
>