How to determine sp caller current database?

When executing a stored procedure that is defined in another database, as:

USE db1;

EXEC db2.dbo.sproc;

Is there a way in the stored procedure "sproc" to determine that the caller made the call from db1?

If nothing else, you can use CONTEXT_INFO to retain that information.

To call the stored procedure you can do something like:

Code Snippet

USE db1;

declare @.binVar varbinary(128)
set @.binVar = convert(varbinary(128), 'db1')
set context_info @.binVar

EXEC db2.dbo.sproc;

and to fetch the information from within the stored procedure you can use something like:

Code Snippet

convert(varchar(128), context_info())

( This is assuming that db_name() is not working for you. )

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 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 detect the current settings of ANSI_NULLS?

Hello All.
How to detect the current settings of ANSI_NULLS at run-time of script?
For example:
SET ANSI_NULLS OFF - sets ANSI_NULLS to OFF
GO
GET ANSI_NULLS - gets the settings of ANSI_NULLS.Hi
dbcc useroptions
<burlaka@.yandex.ru> wrote in message
news:1143625595.136842.163590@.t31g2000cwb.googlegroups.com...
> Hello All.
> How to detect the current settings of ANSI_NULLS at run-time of script?
> For example:
> SET ANSI_NULLS OFF - sets ANSI_NULLS to OFF
> GO
> GET ANSI_NULLS - gets the settings of ANSI_NULLS.
>|||Thank you very much.

How to delete tmp file which created by Crystal Report automatically

Hi,
Each time I run the vb application, the crystal report will create tmp file in the C:\ and VB*.tmp in the current working dirctory. How can I delete it automatically? Now, I need to delete it manually, otherwise, the huge tmp file will remind in both directories.
ThanksKill FileName

How to Delete article in mearge replication

Dear Friends
I want to delete one article from the current Merge
replication i have use the sp_dropmergearticle but it is
comming up with the error that the ARTICLE CAN NOT BE
DROPED AS THE SNAPSHOT OF THE SAME PUBLICATION IS ALREADY
APPLIED.
Please suggest how I can do the same.
Thanks and best regards
Sharad
Kick everyone off your subscribers and publisher. drop the article. Recreate
your subscriptions using the no-sync option.
Run all merge agents.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"sharad" <niitmalad@.yahoo.co.in> wrote in message
news:0cea01c4f3f0$9e1da4e0$a601280a@.phx.gbl...
> Dear Friends
> I want to delete one article from the current Merge
> replication i have use the sp_dropmergearticle but it is
> comming up with the error that the ARTICLE CAN NOT BE
> DROPED AS THE SNAPSHOT OF THE SAME PUBLICATION IS ALREADY
> APPLIED.
> Please suggest how I can do the same.
> Thanks and best regards
> Sharad
|||Sharad try with this SP
sp_repldropcolumn
David Barquero
"sharad" <niitmalad@.yahoo.co.in> wrote in message
news:0cea01c4f3f0$9e1da4e0$a601280a@.phx.gbl...
> Dear Friends
> I want to delete one article from the current Merge
> replication i have use the sp_dropmergearticle but it is
> comming up with the error that the ARTICLE CAN NOT BE
> DROPED AS THE SNAPSHOT OF THE SAME PUBLICATION IS ALREADY
> APPLIED.
> Please suggest how I can do the same.
> Thanks and best regards
> Sharad

How to delete (drop) a database with OSQL

Hi,
I imported DBF files into a new SQL server database and I've been
developing a VB.Net application.
Now I would like to delete the current database and re-import a more
current set of DBF files into SQL.
Only problem, I can't seem to find a way to delete the database from
MSDE. I tried the OSQL 'drop database <name>' but it complains the
'database is in currently in use'.
I must be missing something simple.
Thanks
Richard
Hi ,
You might be in the same database while you are trying to execute the
command.
connect to the server and change the context to the master database and
also make sure that you dint have any sessions currently accessing the
database either remotely or from the same machine. This time the command
should go ahead fine
girish sundaram
This posting is provided "AS IS" with no warranties, and confers no rights.
|||It is possible that there are still connections to the database. If you are
on MSDE 2000 the following is a sure way to drop a database:
-- Kick everyone out of the database
ALTER DATABASE <database name> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
-- Go somewhere else yourself as well
USE master
-- Now drop it
DROP DATABASE <database name>
Jacco Schalkwijk
SQL Server MVP
"Richard Fagen" <no_spam@.my_isp.com> wrote in message
news:eBGaP5K3EHA.2676@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I imported DBF files into a new SQL server database and I've been
> developing a VB.Net application.
> Now I would like to delete the current database and re-import a more
> current set of DBF files into SQL.
> Only problem, I can't seem to find a way to delete the database from MSDE.
> I tried the OSQL 'drop database <name>' but it complains the 'database is
> in currently in use'.
> I must be missing something simple.
> Thanks
> Richard
|||Thanks guys, you were right, there was a connection to the database.
At first I tried a 'use master' in qsql but got the same results. Then
it dawned on me that Visual Studio itself had a connection to the
database. Once I closed it, I could delete the database.
To be sure I understood it, I restored the database, checked in VS.Net
to see it, then I deleted it in osql while VS.Net was open (but no
connection), this also worked.
Thanks for you help!
Richard
Jacco Schalkwijk wrote:
> It is possible that there are still connections to the database. If you are
> on MSDE 2000 the following is a sure way to drop a database:
> -- Kick everyone out of the database
> ALTER DATABASE <database name> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
> -- Go somewhere else yourself as well
> USE master
> -- Now drop it
> DROP DATABASE <database name>
>