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 connection pool from SQL Server?

I was going thru Enterprise Manager and can't see connection count, I can
see User connected, but that doesn't give me an actual connection count.
Is there a way to determine my connection pool count from Enterprise Manager
and/or some other tool?
Thanks, Rob.
'Connection Pool' is a feature of the client side library, if you are using
the .net provider for sql server then you should be able to look at the
following perfmon object:
".NET data provider for SQL Server"
hth
Vikram Vamshi
Eclipsys Corporation
"Rob R. Ainscough" <robains@.pacbell.net> wrote in message
news:u%230vD7dQFHA.576@.TK2MSFTNGP15.phx.gbl...
>I was going thru Enterprise Manager and can't see connection count, I can
>see User connected, but that doesn't give me an actual connection count.
> Is there a way to determine my connection pool count from Enterprise
> Manager and/or some other tool?
> Thanks, Rob.
>
|||I ended up using the SQL Profiler -- Session -- not perferct (does show
counts), but gave me an idea.
Where does one find the ".NET data provider for SQL Server" performance mon
object?
"Vikram Vamshi" <vikram.vamshi@.nospam.com> wrote in message
news:OQxQ%23WfQFHA.4020@.tk2msftngp13.phx.gbl...
> 'Connection Pool' is a feature of the client side library, if you are
> using the .net provider for sql server then you should be able to look at
> the following perfmon object:
> ".NET data provider for SQL Server"
> hth
> --
> Vikram Vamshi
> Eclipsys Corporation
> "Rob R. Ainscough" <robains@.pacbell.net> wrote in message
> news:u%230vD7dQFHA.576@.TK2MSFTNGP15.phx.gbl...
>
|||It is bundled with the .net runtime /sdk
Vikram Vamshi
Eclipsys Corporation
"Rob R. Ainscough" <robains@.pacbell.net> wrote in message
news:%23a4CINgQFHA.3336@.TK2MSFTNGP10.phx.gbl...
>I ended up using the SQL Profiler -- Session -- not perferct (does show
>counts), but gave me an idea.
> Where does one find the ".NET data provider for SQL Server" performance
> mon object?
> "Vikram Vamshi" <vikram.vamshi@.nospam.com> wrote in message
> news:OQxQ%23WfQFHA.4020@.tk2msftngp13.phx.gbl...
>
sql

How to determine connection pool from SQL Server?

I was going thru Enterprise Manager and can't see connection count, I can
see User connected, but that doesn't give me an actual connection count.
Is there a way to determine my connection pool count from Enterprise Manager
and/or some other tool?
Thanks, Rob.'Connection Pool' is a feature of the client side library, if you are using
the .net provider for sql server then you should be able to look at the
following perfmon object:
".NET data provider for SQL Server"
hth
--
Vikram Vamshi
Eclipsys Corporation
"Rob R. Ainscough" <robains@.pacbell.net> wrote in message
news:u%230vD7dQFHA.576@.TK2MSFTNGP15.phx.gbl...
>I was going thru Enterprise Manager and can't see connection count, I can
>see User connected, but that doesn't give me an actual connection count.
> Is there a way to determine my connection pool count from Enterprise
> Manager and/or some other tool?
> Thanks, Rob.
>|||I ended up using the SQL Profiler -- Session -- not perferct (does show
counts), but gave me an idea.
Where does one find the ".NET data provider for SQL Server" performance mon
object?
"Vikram Vamshi" <vikram.vamshi@.nospam.com> wrote in message
news:OQxQ%23WfQFHA.4020@.tk2msftngp13.phx.gbl...
> 'Connection Pool' is a feature of the client side library, if you are
> using the .net provider for sql server then you should be able to look at
> the following perfmon object:
> ".NET data provider for SQL Server"
> hth
> --
> Vikram Vamshi
> Eclipsys Corporation
> "Rob R. Ainscough" <robains@.pacbell.net> wrote in message
> news:u%230vD7dQFHA.576@.TK2MSFTNGP15.phx.gbl...
>|||It is bundled with the .net runtime /sdk
Vikram Vamshi
Eclipsys Corporation
"Rob R. Ainscough" <robains@.pacbell.net> wrote in message
news:%23a4CINgQFHA.3336@.TK2MSFTNGP10.phx.gbl...
>I ended up using the SQL Profiler -- Session -- not perferct (does show
>counts), but gave me an idea.
> Where does one find the ".NET data provider for SQL Server" performance
> mon object?
> "Vikram Vamshi" <vikram.vamshi@.nospam.com> wrote in message
> news:OQxQ%23WfQFHA.4020@.tk2msftngp13.phx.gbl...
>

How to determinate a SqlConnection is open or not

I use a common connection cnn;
If the conn is opened, if I open it again, it will through a exception
How can I determinate a SqlConnection is open or not before I open it?
conn.State
Highly .State and click F1 in Visual Studio.NET
2004 and 2005 Microsoft MVP C#
Robbe Morris
http://www.masterado.net
Earn $$$ money answering .NET Framework
messageboard posts at EggHeadCafe.com.
http://www.eggheadcafe.com/forums/merit.asp
"ad" <ad@.wfes.tcc.edu.tw> wrote in message
news:%23vHfoN8gFHA.576@.TK2MSFTNGP15.phx.gbl...
>I use a common connection cnn;
> If the conn is opened, if I open it again, it will through a exception
> How can I determinate a SqlConnection is open or not before I open it?
>
>

How to determinate a SqlConnection is open or not

I use a common connection cnn;
If the conn is opened, if I open it again, it will through a exception
How can I determinate a SqlConnection is open or not before I open it?conn.State
Highly .State and click F1 in Visual Studio.NET
2004 and 2005 Microsoft MVP C#
Robbe Morris
http://www.masterado.net
Earn $$$ money answering .NET Framework
messageboard posts at EggHeadCafe.com.
http://www.eggheadcafe.com/forums/merit.asp
"ad" <ad@.wfes.tcc.edu.tw> wrote in message
news:%23vHfoN8gFHA.576@.TK2MSFTNGP15.phx.gbl...
>I use a common connection cnn;
> If the conn is opened, if I open it again, it will through a exception
> How can I determinate a SqlConnection is open or not before I open it?
>
>

How to determinate a SqlConnection is open or not

I use a common connection cnn;
If the conn is opened, if I open it again, it will through a exception
How can I determinate a SqlConnection is open or not before I open it?conn.State
Highly .State and click F1 in Visual Studio.NET
2004 and 2005 Microsoft MVP C#
Robbe Morris
http://www.masterado.net
Earn $$$ money answering .NET Framework
messageboard posts at EggHeadCafe.com.
http://www.eggheadcafe.com/forums/merit.asp
"ad" <ad@.wfes.tcc.edu.tw> wrote in message
news:%23vHfoN8gFHA.576@.TK2MSFTNGP15.phx.gbl...
>I use a common connection cnn;
> If the conn is opened, if I open it again, it will through a exception
> How can I determinate a SqlConnection is open or not before I open it?
>
>

How to detect that the sql server already lost the connection?

Hi,
Sometimes when i use my application but suddenly the
sql server disconnected and i find out that my query
become very slow.
Can i check with you, how to detect the connection
already stop in my application? If i dun want to try and
catch for every sql transaction, how i can catch the sql
server disconnected error once the server already
disconnected? I don't want my application just seems
like 'hang' there.
Thanks for advice.
regards,
florence
"florencelee" <florencelee@.visualsolutions.com.my> wrote in message
news:078501c4ac35$087620a0$a301280a@.phx.gbl...
> Hi,
> Sometimes when i use my application but suddenly the
> sql server disconnected and i find out that my query
> become very slow.
> Can i check with you, how to detect the connection
> already stop in my application? If i dun want to try and
> catch for every sql transaction, how i can catch the sql
> server disconnected error once the server already
> disconnected? I don't want my application just seems
> like 'hang' there.
When I've had an application that performs periodic queries against the
database where the connection could be lost between queries, I've used a
simple query to start the next batch of queries just to see that the
connection was alive. For example, you might do a simple "SELECT 1" just to
see that the connection is alive, then do your complicated query.
This was in Java using the JDBC driver.
Rick
sql

How to detect active connection of a SQL Server?

Hi There,
Who know how to detect active connection to a particular SQL Server?
Eg: I have a SQL Server named MyServer and there are 3 computers link to
MyServer when my application start, but how do I know which of the 3
computers is linked to MyServer when application is running?
Thanks!
Wallace Wong
A2000 Solutions Pte Ltd
Blk 5 Ang Mo Kio Industrial Park 2A #07-17 TECH II Singapore 567760
Tel: (65) 6720 2000 Fax: (65) 6720 2987
Hi,
you can query the following objects:
SQL SERVER 2000:
SELECT * FROM sysprocesses
SQL SERVER 2005:
SELECT * FROM sys.dm_exec_sessions
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||"wallace" <wallace@.a2000.net> wrote in message
news:O7X575BUHHA.496@.TK2MSFTNGP06.phx.gbl...
> Hi There,
> Who know how to detect active connection to a particular SQL Server?
> Eg: I have a SQL Server named MyServer and there are 3 computers link to
> MyServer when my application start, but how do I know which of the 3
> computers is linked to MyServer when application is running?
>
> Thanks!
> --
> Wallace Wong
> A2000 Solutions Pte Ltd
> Blk 5 Ang Mo Kio Industrial Park 2A #07-17 TECH II Singapore 567760
> Tel: (65) 6720 2000 Fax: (65) 6720 2987
>
|||select count(*) from dbo.sysprocesses where status<>'Background'
EggHeadCafe.com - .NET Developer Portal of Choice
http://www.eggheadcafe.com

How to detect active connection of a SQL Server?

Hi There,
Who know how to detect active connection to a particular SQL Server?
Eg: I have a SQL Server named MyServer and there are 3 computers link to
MyServer when my application start, but how do I know which of the 3
computers is linked to MyServer when application is running?
Thanks!
Wallace Wong
A2000 Solutions Pte Ltd
Blk 5 Ang Mo Kio Industrial Park 2A #07-17 TECH II Singapore 567760
Tel: (65) 6720 2000 Fax: (65) 6720 2987Hi,
you can query the following objects:
SQL SERVER 2000:
SELECT * FROM sysprocesses
SQL SERVER 2005:
SELECT * FROM sys.dm_exec_sessions
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
--|||"wallace" <wallace@.a2000.net> wrote in message
news:O7X575BUHHA.496@.TK2MSFTNGP06.phx.gbl...
> Hi There,
> Who know how to detect active connection to a particular SQL Server?
> Eg: I have a SQL Server named MyServer and there are 3 computers link to
> MyServer when my application start, but how do I know which of the 3
> computers is linked to MyServer when application is running?
>
> Thanks!
> --
> Wallace Wong
> A2000 Solutions Pte Ltd
> Blk 5 Ang Mo Kio Industrial Park 2A #07-17 TECH II Singapore 567760
> Tel: (65) 6720 2000 Fax: (65) 6720 2987
>|||Hello,
Execute SP_WHO system stored procedure from SQL Server Management studio --
Query window (SQL 2005) or Query Analyzer in SQL 2000 to get
all the connections in SQL Server. This gives you the Hostname of the each
connection
Thanks
Hari
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message news:...
> "wallace" <wallace@.a2000.net> wrote in message
> news:O7X575BUHHA.496@.TK2MSFTNGP06.phx.gbl...
>

How to detach properly using SQL Server Express?

It’s easy to attach a database file in the connection string using AttachDbFilename. And that file gets closed when all connections to that database are closed (one might have to clear the connection pool, or set the connection to use master). The database file can now be moved or deleted. Sounds good...

...but that database still shows up in the attached databases! (select * from sysdatabases). So over time one could end up with hundreds of attached databases (especially if an application uses the attached databases like document files that users open and close frequently).

Question 1: Is there a performance hit having hundreds of attached databases?

OK, one can try to detach the database when the application is done with a specific file, using sp_detach_db after checking if other connections to that database are open (SELECT count(*) FROM master.dbo.sysprocesses WHERE dbid = DB_ID()). But that still does not detach if the application exists unexpectedly, or if one does not have a connection available anymore (e.g. during finalization when exiting the application). There should be a better way.

Question 2: Does anyone know of a good pattern on how an application should detach (preferably using a managed SqlConnection)?

Question 3: Is there a connection string property that auto-detaches when the connection is closed?

Thanks for any tips

--Ralf

Is there a performance hit having hundreds of attached databases?

Depends on how hard this databases are being used. Perhaps, it would be hard a little to navigate through them in Management Studio or other management tool, but I think it's all.

Does anyone know of a good pattern on how an application should detach (preferably using a managed SqlConnection)?

It looks strange that application attaches database everytime it connected to the server and detaches database everytime it disconneted. Imho, it's unnecessary cycle - just keep databases attached.

especially if an application uses the attached databases like document files that users open and close frequently

Sounds not good. What's the reason to have multiple databases instead of one?

|||

The reason for having multiple databases that are attached and detached frequently is that the user uses our app to open a database just like he would use Word to open a word file. We need to store data acquired from an instrument in easily movable files.

I thought this would be a typical use case for SQL Server Express.

How to deploy an report and connection string from some xml file

Suppose we are two developer.Who have SSRS install at there own system.
Our database server is intalled on our main db server.

1,First thing does it necessay to create a shared datasource.
I have a web application in which I want's to add a folder say My reports in which i will put my reports {that is .rdl file}
Then I want's to hit a url with render format as excel this will bring down my report in excel format.

2,How to configure a connection
I want's to pass the connection string in the rdl via some xml file let say myconnectionn xml file
which look like
<connection-sources>
<source name="Mycon1" default="true" >
Data Source=abc\yukon;User ID=sa;Password=as;Initial Catalog=MyDbName1
</source>
<source name="Mycon2" skip="true" >
Data Source=asdsadabc\yukon;User ID=sa;Password=as;Initial Catalog=MyDbName2
</source>
</connection-sources>

connection string would be where attribute default of source should be true.

1. Shared data sources are used to define a connection once, and share it among multiple reports. That way, if the connection information changes, you only have to update the shared data source, and not each report. You don't have to use shared data sources, though. Data sources can be defined at a report level as well.

2. You can create a connection string from an expression, which would allow you to change the connect string at runtime based on parameters passed to the report. However, as far as I am aware, you cannot use an XML file directly to set the connect string.

|||

Thanks jwelch.

Then any work around ?

|||Use an expression based connection and pass your connection string (or components of it) in as parameters.|||

What is an expression based connection and how can I pass my connection string (or components of it) in as parameters.

Please let it clear a little bit ?

|||

You can create an expression based connection by going to the data tab of your report. Open up the dataset properties (by clicking the ellipsis (...) beside the dataset dropdown), and then click the ellipsis next to the data source. If the Use shared reference option is checked, uncheck it. You should then be able to select the fx button next to the connection string box. You can enter an expression into the resulting dialog, such as:

Dynamic Connection String

="Data Source="+Parameters!Server.Value+";Initial Catalog="+Parameters!Database.Value

You'll need to create the parameters on the report to hold the values you want, and you may need to alter the connection string in my example to match your database provider.

Hope this helps.

How to deploy an report and connection string from some xml file

Suppose we are two developer.Who have SSRS install at there own system.
Our database server is intalled on our main db server.

1,First thing does it necessay to create a shared datasource.
I have a web application in which I want's to add a folder say My reports in which i will put my reports {that is .rdl file}
Then I want's to hit a url with render format as excel this will bring down my report in excel format.

2,How to configure a connection
I want's to pass the connection string in the rdl via some xml file let say myconnectionn xml file
which look like
<connection-sources>
<source name="Mycon1" default="true" >
Data Source=abc\yukon;User ID=sa;Password=as;Initial Catalog=MyDbName1
</source>
<source name="Mycon2" skip="true" >
Data Source=asdsadabc\yukon;User ID=sa;Password=as;Initial Catalog=MyDbName2
</source>
</connection-sources>

connection string would be where attribute default of source should be true.

1. Shared data sources are used to define a connection once, and share it among multiple reports. That way, if the connection information changes, you only have to update the shared data source, and not each report. You don't have to use shared data sources, though. Data sources can be defined at a report level as well.

2. You can create a connection string from an expression, which would allow you to change the connect string at runtime based on parameters passed to the report. However, as far as I am aware, you cannot use an XML file directly to set the connect string.

|||

Thanks jwelch.

Then any work around ?

|||Use an expression based connection and pass your connection string (or components of it) in as parameters.|||

What is an expression based connection and how can I pass my connection string (or components of it) in as parameters.

Please let it clear a little bit ?

|||

You can create an expression based connection by going to the data tab of your report. Open up the dataset properties (by clicking the ellipsis (...) beside the dataset dropdown), and then click the ellipsis next to the data source. If the Use shared reference option is checked, uncheck it. You should then be able to select the fx button next to the connection string box. You can enter an expression into the resulting dialog, such as:

Dynamic Connection String

="Data Source="+Parameters!Server.Value+";Initial Catalog="+Parameters!Database.Value

You'll need to create the parameters on the report to hold the values you want, and you may need to alter the connection string in my example to match your database provider.

Hope this helps.

how to delete unused connection in DTS package

hi guys,
I have a dts package which has 2 unused connections, but I don't know
how to delete them using dts package designer... is that possible to do on U
I
level or
do I have to write a script to drop unused connections?
-kevI got it.. thanks anyway..
http://www.sqldts.com/default.aspx?253
"Kevin" wrote:

> hi guys,
> I have a dts package which has 2 unused connections, but I don't know
> how to delete them using dts package designer... is that possible to do on
UI
> level or
> do I have to write a script to drop unused connections?
> -kev