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 package's executing folder?

I should have written this down when I came across it -- but I recently saw an example of how to determine a package's currently-executing folder. Can anyone help?

Thanks!

- Mike

Can you elaborate on what you mean by "the current executing folder"?

-Jamie

|||

Jamie Thomson wrote:

Can you elaborate on what you mean by "the current executing folder"?

-Jamie

Thanks, Jamie -- sure, the folder where the DTSX resides as it executes.

Here's the situation -- the DBAs want to put all the packages I've prepared into a single folder.

The "main" package calls each of 42 "sub-packages", one after another. I'm planning to use the
Execute Package task for each of the 42, and set it to "File system" to reference each of the 42.

The Connection property of the Execute Package task wants the path to the DTSX file the Execute Package
task runs, and I'm not sure how to set this at runtime. Because all the packages will live in the same arbitrary
folder, I hoped I might be able to use an expression or something to set the Connection property properly.

Thanks for asking!

- Mike

|||

mike.groh wrote:

Jamie Thomson wrote:

Can you elaborate on what you mean by "the current executing folder"?

-Jamie

Thanks, Jamie -- sure, the folder where the DTSX resides as it executes.

Here's the situation -- the DBAs want to put all the packages I've prepared into a single folder.

The "main" package calls each of 42 "sub-packages", one after another. I'm planning to use the
Execute Package task for each of the 42, and set it to "File system" to reference each of the 42.

The Connection property of the Execute Package task wants the path to the DTSX file the Execute Package
task runs, and I'm not sure how to set this at runtime. Because all the packages will live in the same arbitrary
folder, I hoped I might be able to use an expression or something to set the Connection property properly.

Thanks for asking!

- Mike

Right. Well as far as I know theres no way for the package to discover where it "lives". The way I solve this is for each package to have a variable called RootFolder which gets set by an indirect configuration. I've talked about it on my blog quite a bit:

Common folder structure
(http://blogs.conchango.com/jamiethomson/archive/2006/01/05/2559.aspx)

Indirect configurations ROCK!
(http://blogs.conchango.com/jamiethomson/archive/2005/11/02/2342.aspx)

Indirect configurations gotcha
http://blogs.conchango.com/jamiethomson/archive/2005/10/31/2336.aspx

-Jamie

|||

Thanks! I'll check it out in your blog.

- Mike

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

How to detect INSTALLED SQL version?

Hello,
I need to detect whether the computer has SQL Server 2000 SP3 installed
before
attempting to connect to it, for example during my custom setup program. Wha
t
is the recommended way of doing this?
TIA,
A.SELECT TOP 0 NULL AS Property , NULL AS Variable
UNION ALL SELECT 'Version' , SERVERPROPERTY( 'ProductVersion' )
UNION ALL SELECT 'Level' , SERVERPROPERTY( 'ProductLevel' )
UNION ALL SELECT 'Engine Edition' , SERVERPROPERTY( 'Edition' )
"Abbey" <Abbey@.discussions.microsoft.com> wrote in message
news:1D2E126E-B6FE-411C-B157-FD3BBB49CE82@.microsoft.com...
> Hello,
> I need to detect whether the computer has SQL Server 2000 SP3 installed
> before
> attempting to connect to it, for example during my custom setup program.
What
> is the recommended way of doing this?
> TIA,
> A.
>|||HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Mi
crosoft SQL
Server\MSSQLServer\MSSQLServer\CurrentVe
rsion\
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Mi
crosoft SQL Server\<instance
name>\MSSQLServer\CurrentVersion\
Key name = CSDVersion (not CurrentVersion).
"Abbey" <Abbey@.discussions.microsoft.com> wrote in message
news:1D2E126E-B6FE-411C-B157-FD3BBB49CE82@.microsoft.com...
> Hello,
> I need to detect whether the computer has SQL Server 2000 SP3 installed
> before
> attempting to connect to it, for example during my custom setup program.
> What
> is the recommended way of doing this?
> TIA,
> A.
>|||This won't work "before attempting to connect to it"... you have to be
connected to the server to run your query...

> SELECT TOP 0 NULL AS Property , NULL AS Variable
> UNION ALL SELECT 'Version' , SERVERPROPERTY( 'ProductVersion' )
> UNION ALL SELECT 'Level' , SERVERPROPERTY( 'ProductLevel' )
> UNION ALL SELECT 'Engine Edition' , SERVERPROPERTY( 'Edition' )|||Hello Aaron,
Thanks for that suggestion - I'll try it and let you know...
Regards,
Abbey
"Aaron Bertrand [SQL Server MVP]" wrote:

> HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Mi
crosoft SQL
> Server\MSSQLServer\MSSQLServer\CurrentVe
rsion\
> HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Mi
crosoft SQL Server\<instance
> name>\MSSQLServer\CurrentVersion\
> Key name = CSDVersion (not CurrentVersion).
>
> "Abbey" <Abbey@.discussions.microsoft.com> wrote in message
> news:1D2E126E-B6FE-411C-B157-FD3BBB49CE82@.microsoft.com...
>
>|||Is there any harm in opening a connection to check the database version?
If it's the wrong version you can always run "wrong version" code and
disconnect the connection.
"Rebecca York" <rebecca.york {at} 2ndbyte.com> wrote in message
news:4362481f$0$134$7b0f0fd3@.mistral.news.newnet.co.uk...
> SELECT TOP 0 NULL AS Property , NULL AS Variable
> UNION ALL SELECT 'Version' , SERVERPROPERTY( 'ProductVersion' )
> UNION ALL SELECT 'Level' , SERVERPROPERTY( 'ProductLevel' )
> UNION ALL SELECT 'Engine Edition' , SERVERPROPERTY( 'Edition' )
>
> "Abbey" <Abbey@.discussions.microsoft.com> wrote in message
> news:1D2E126E-B6FE-411C-B157-FD3BBB49CE82@.microsoft.com...
> What
>sql

How to deploy large sets of data.

How can a large set of data be deployed and implemented by a local DBA.
Large set of data for example over 1 Gigabyte.
For example consisting of 1000 rows of data.
For example for text data ?
Or for binairy data ?
Large set of data, for example over 3 Gigabyte and consisting of
many rows ? (Milions of rows).
The implementation has to be as simple as possible for the local DBA,
thanks for your attention,
ben brugman
1 way would be :-
Copy the data out of SQL Server to a text file via (DTS, BCP)
The local DBA then runs a BULK INSERT script to import
HTH. Ryan
"ben brugman" <ben@.niethier.nl> wrote in message
news:%23Y4af8qGGHA.740@.TK2MSFTNGP12.phx.gbl...
> How can a large set of data be deployed and implemented by a local DBA.
> Large set of data for example over 1 Gigabyte.
> For example consisting of 1000 rows of data.
> For example for text data ?
> Or for binairy data ?
> Large set of data, for example over 3 Gigabyte and consisting of
> many rows ? (Milions of rows).
> The implementation has to be as simple as possible for the local DBA,
> thanks for your attention,
> ben brugman
>
|||"ben brugman" <ben@.niethier.nl> wrote in message
news:%23Y4af8qGGHA.740@.TK2MSFTNGP12.phx.gbl...
> How can a large set of data be deployed and implemented by a local DBA.
> Large set of data for example over 1 Gigabyte.
> For example consisting of 1000 rows of data.
> For example for text data ?
> Or for binairy data ?
> Large set of data, for example over 3 Gigabyte and consisting of
> many rows ? (Milions of rows).
> The implementation has to be as simple as possible for the local DBA,
> thanks for your attention,
> ben brugman
>
There are several approaces that you can take...
Use multiple files for your data and use sp_detach_db and sp_attach_db.
(Script any SQL authenticated logins if moving to a new machine in a new
environment)
Simply use Backup and Restore.
(Script any SQL authenticated logins if moving to a new machine in a new
environment)
If there are size issues. For example a 3GB backup file is too big to FTP,
or put on a CD etc. You could do a multi-file backup, Zip each file and
then move them. Unzip and restore.
Those are probably the easiest for a dba to perform.
Rick Sawtell
MCT, MCSD, MCDBA

How to deploy large sets of data.

How can a large set of data be deployed and implemented by a local DBA.
Large set of data for example over 1 Gigabyte.
For example consisting of 1000 rows of data.
For example for text data ?
Or for binairy data ?
Large set of data, for example over 3 Gigabyte and consisting of
many rows ? (Milions of rows).
The implementation has to be as simple as possible for the local DBA,
thanks for your attention,
ben brugman1 way would be :-
Copy the data out of SQL Server to a text file via (DTS, BCP)
The local DBA then runs a BULK INSERT script to import
HTH. Ryan
"ben brugman" <ben@.niethier.nl> wrote in message
news:%23Y4af8qGGHA.740@.TK2MSFTNGP12.phx.gbl...
> How can a large set of data be deployed and implemented by a local DBA.
> Large set of data for example over 1 Gigabyte.
> For example consisting of 1000 rows of data.
> For example for text data ?
> Or for binairy data ?
> Large set of data, for example over 3 Gigabyte and consisting of
> many rows ? (Milions of rows).
> The implementation has to be as simple as possible for the local DBA,
> thanks for your attention,
> ben brugman
>|||"ben brugman" <ben@.niethier.nl> wrote in message
news:%23Y4af8qGGHA.740@.TK2MSFTNGP12.phx.gbl...
> How can a large set of data be deployed and implemented by a local DBA.
> Large set of data for example over 1 Gigabyte.
> For example consisting of 1000 rows of data.
> For example for text data ?
> Or for binairy data ?
> Large set of data, for example over 3 Gigabyte and consisting of
> many rows ? (Milions of rows).
> The implementation has to be as simple as possible for the local DBA,
> thanks for your attention,
> ben brugman
>
There are several approaces that you can take...
Use multiple files for your data and use sp_detach_db and sp_attach_db.
(Script any SQL authenticated logins if moving to a new machine in a new
environment)
Simply use Backup and Restore.
(Script any SQL authenticated logins if moving to a new machine in a new
environment)
If there are size issues. For example a 3GB backup file is too big to FTP,
or put on a CD etc. You could do a multi-file backup, Zip each file and
then move them. Unzip and restore.
Those are probably the easiest for a dba to perform.
Rick Sawtell
MCT, MCSD, MCDBA

How to deploy large sets of data.

How can a large set of data be deployed and implemented by a local DBA.
Large set of data for example over 1 Gigabyte.
For example consisting of 1000 rows of data.
For example for text data ?
Or for binairy data ?
Large set of data, for example over 3 Gigabyte and consisting of
many rows ? (Milions of rows).
The implementation has to be as simple as possible for the local DBA,
thanks for your attention,
ben brugman1 way would be :-
Copy the data out of SQL Server to a text file via (DTS, BCP)
The local DBA then runs a BULK INSERT script to import
--
HTH. Ryan
"ben brugman" <ben@.niethier.nl> wrote in message
news:%23Y4af8qGGHA.740@.TK2MSFTNGP12.phx.gbl...
> How can a large set of data be deployed and implemented by a local DBA.
> Large set of data for example over 1 Gigabyte.
> For example consisting of 1000 rows of data.
> For example for text data ?
> Or for binairy data ?
> Large set of data, for example over 3 Gigabyte and consisting of
> many rows ? (Milions of rows).
> The implementation has to be as simple as possible for the local DBA,
> thanks for your attention,
> ben brugman
>|||"ben brugman" <ben@.niethier.nl> wrote in message
news:%23Y4af8qGGHA.740@.TK2MSFTNGP12.phx.gbl...
> How can a large set of data be deployed and implemented by a local DBA.
> Large set of data for example over 1 Gigabyte.
> For example consisting of 1000 rows of data.
> For example for text data ?
> Or for binairy data ?
> Large set of data, for example over 3 Gigabyte and consisting of
> many rows ? (Milions of rows).
> The implementation has to be as simple as possible for the local DBA,
> thanks for your attention,
> ben brugman
>
There are several approaces that you can take...
Use multiple files for your data and use sp_detach_db and sp_attach_db.
(Script any SQL authenticated logins if moving to a new machine in a new
environment)
Simply use Backup and Restore.
(Script any SQL authenticated logins if moving to a new machine in a new
environment)
If there are size issues. For example a 3GB backup file is too big to FTP,
or put on a CD etc. You could do a multi-file backup, Zip each file and
then move them. Unzip and restore.
Those are probably the easiest for a dba to perform.
Rick Sawtell
MCT, MCSD, MCDBA

How to deplory reports using visual studio and Forms Authentication?

Hi,
i successfully installed the forms authentication example on my report
server.
The problem i am facing now is that i am no longer able to deploy my
reports using visual studio to the report server as i did before. Is
there a possibility to still use visual studio for deploying?
If not, is there an alternative for deploying? How can i transfer my
reports, data-sources and models to the server?Have you removed Windows Authentication as an option in IIS? My VS prompts
me when LogonUser fails.
"Patrick Dinger" <paxos2k@.gmail.com> wrote in message
news:1142000029.874965.138750@.u72g2000cwu.googlegroups.com...
> Hi,
> i successfully installed the forms authentication example on my report
> server.
> The problem i am facing now is that i am no longer able to deploy my
> reports using visual studio to the report server as i did before. Is
> there a possibility to still use visual studio for deploying?
> If not, is there an alternative for deploying? How can i transfer my
> reports, data-sources and models to the server?
>

How to delay queue processing?

I am looking for some suggestions on how to implement a delay in processing
queue elements. For example, I am using a queue to process requests to call
a web service. If the web service is unable to process a particular request,
I would like that request to be retried 5 minutes from now while continuing
to process other requests that may be in the queue. I suspect I may need
additional queues, such as a delay queue. Any ideas would be appreciated.

TIA -- Keith.Conversation Timers can be used to do something periodically or to do something after a fixed delay. You can either keep the same dialog you received the message came in on, set a timer and then send the message again when the timer message is received or else put the messages on a different queue and then process that queue periodically. The first option will require keeping the message body around somewhere - either by using retension on the queue or by keeping it in a seperate table (maybe indexed by dialogID).

Here's a little sample that uses timere to do something once a minute:

-- Set up a queue for the timer sample

CREATE QUEUE [TimerQueue]

CREATE SERVICE [TimerService] ON QUEUE [TimerQueue]([DEFAULT]) ;

CREATE SERVICE [TimerResponseService] ON QUEUE [TimerQueue];

GO

-- Create the stored procedure to handle the timer messages

CREATE PROCEDURE [dbo].[HandleTimer] AS

DECLARE @.conversationHandle uniqueidentifier

DECLARE @.message_type_name sysname

DECLARE @.dialog uniqueidentifier

BEGIN TRANSACTION

-- Timer messages only happen once a minute so there's no

-- need to receive in a loop.

WAITFOR (

RECEIVE top(1)

@.message_type_name=message_type_name,

@.dialog = conversation_handle

FROM [TimerQueue]

), TIMEOUT 500

IF (@.message_type_name = 'http://schemas.microsoft.com/SQL/ServiceBroker/DialogTimer')

BEGIN

-- Start the next cycle

BEGIN CONVERSATION TIMER ( @.dialog ) TIMEOUT = 60;

-- Do Whatever you want to do here

END

COMMIT TRANSACTION

go

ALTER QUEUE [TimerQueue] WITH ACTIVATION (

STATUS = ON, PROCEDURE_NAME = HandleTimer ,

MAX_QUEUE_READERS = 1,EXECUTE AS SELF)

go

DECLARE @.conversationHandle uniqueidentifier

BEGIN DIALOG CONVERSATION @.conversationHandle

FROM SERVICE [TimerResponseService]

TO SERVICE 'TimerService';

BEGIN CONVERSATION TIMER ( @.ConversationHandle ) TIMEOUT = 60;

How to define global var?

How to I create a global variable for several SPs to share? For example, I
might have two status vars, such as statusred = 3 and statusgreen = 1.
Thanks,
BrettInsert the value(s) in a table and have each of your stored procs select the
value from the table?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Brett" <no@.spam.com> wrote in message
news:%23YLgthPIFHA.1176@.TK2MSFTNGP12.phx.gbl...
> How to I create a global variable for several SPs to share? For example,
I
> might have two status vars, such as statusred = 3 and statusgreen = 1.
> Thanks,
> Brett
>|||That's one way but isn't that inefficient?
How does SQL Server use the @.@.ERROR global var for example?
Thanks,
Brett
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:uj8mZsPIFHA.3076@.TK2MSFTNGP10.phx.gbl...
> Insert the value(s) in a table and have each of your stored procs select
> the
> value from the table?
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Brett" <no@.spam.com> wrote in message
> news:%23YLgthPIFHA.1176@.TK2MSFTNGP12.phx.gbl...
> I
>|||Do the two SPs have anything in common? i.e., do they run in support of one
another? or in rsponse to the same trigger? or are they totally indodependan
t
except for their use of this same value?
If they're functionally related, consider creating a single SP That aclls
both of them, and have that SP pass the value to both SPs that need it.
If they're not, it sounds like what you have is (one of potentially many)
application configuration settings. These can be stored and propogated to
wherever they are needed in a variety of ways, including externally in XML
files, or the Registry, or internally in a separate Database Table that has
name value pairs (Setting, value).
Don;t worry about efficiency ( I Think you meant performance) because SQL is
optimized for this. If the value is used often, it will be cached and kept
in memory anyway.
"Brett" wrote:

> How to I create a global variable for several SPs to share? For example,
I
> might have two status vars, such as statusred = 3 and statusgreen = 1.
> Thanks,
> Brett
>
>|||"Brett" <no@.spam.com> wrote in message
news:O9k4M4PIFHA.3628@.TK2MSFTNGP10.phx.gbl...
> That's one way but isn't that inefficient?
> How does SQL Server use the @.@.ERROR global var for example?
No; SQL Server will keep the value in memory if it's accessed often --
the in-memory cache frees data based on usage, so keep using it and it
stays.
As for @.@.ERROR, that's a function, not a global variable. It's just
named similarly to a variable.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||From BOL ...@.@.ERROR is cleared and reset on each statement executed...
Where did you get the idea that this is a global variable?
Why don't you just pass the 2 status's as parameters from 1 SP to the other?
"Brett" <no@.spam.com> wrote in message
news:O9k4M4PIFHA.3628@.TK2MSFTNGP10.phx.gbl...
> That's one way but isn't that inefficient?
> How does SQL Server use the @.@.ERROR global var for example?
> Thanks,
> Brett
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:uj8mZsPIFHA.3076@.TK2MSFTNGP10.phx.gbl...
>|||Think about this for a moment. It is a relational database with the primary
goal of storing data in a table. The whole purpose is optimal data
handling. For a few values that you will be dealing with they will likely
be stored in memory throughout the process anyhow.
Just create a permanent table that your procs use and they can share data on
multiple connections. You will have to figure out how to handle garbage
collection when the programs stop and/or when the start however.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Brett" <no@.spam.com> wrote in message
news:O9k4M4PIFHA.3628@.TK2MSFTNGP10.phx.gbl...
> That's one way but isn't that inefficient?
> How does SQL Server use the @.@.ERROR global var for example?
> Thanks,
> Brett
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:uj8mZsPIFHA.3076@.TK2MSFTNGP10.phx.gbl...
>