May 19, 2003 at 8:48 am
Hi all, does anyone know hot to dynamically change the database context in sql server 2000 ?
For example
declare @db nvarchar(50)
declare @db2 nvarchar(50)
set @db = 'use pubs'
set @db2 = 'select db_name()'
exec (@db2)
exec (@db)
exec (@db2)
run this procedure in query analyzer ...
and the result is ..
master
master
:-/
why ?!?!?!?!?
May 19, 2003 at 9:17 am
The exec command seems to create a new connection and execute the command that you are specifying, so any changes to the curently specified database are not reflected in the procedure.
What you can do is specify the database as part of your dynamic string:
set @sql = 'select * from pubs.dbo.authors'
exec (@sql)
May 19, 2003 at 12:39 pm
The use is only for the duration of the execution.
You can see that by running this
declare @db nvarchar(50)
declare @db2 nvarchar(50)
set @db = 'use tempdb select db_name()'
set @db2 = 'select db_name()'
exec (@db2)
exec (@db)
exec (@db2)
May 19, 2003 at 1:38 pm
You need to do something like this:
declare @cmd nvarchar(4000)
set @cmd = 'declare @db2 nvarchar(50)' + char(13) +
'use pubs' + char(13)+
'set @db2 = db_name()' + char (13) +
'print @db2'
--print @cmd
exec (@cmd)
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
May 19, 2003 at 1:40 pm
That still does not change the database context except for during the exec statement.
May 20, 2003 at 7:10 am
Thanks to all for the replay....
yes...i've made some script to walkaround the problem..something like your answer...
but the script thqt i've post was only a dummy example....
that's the real question /problem....
i'm trying to make some administering store procedure like chek db size, table size....the usual things...
but i'm trying to made this procedure in a "clear way", outside the master db...
to have an Mantainancedb containing these procedure....
The problem is reading the values in the sysfile, sysidex, etc.etc. of every database
for example....
copy the sp_MStablespace in pubs db.....
now run this
use northwind
exec pubs..sp_mstablespace etc.etc.
the result are from pubs db and not from northwind....
i hope to be clear....
any idea or i've to write a piece of code for every db ?
May 20, 2003 at 8:14 am
I see the problem. I think you will either need to you place your common SP in master, or replicate them in all your user db's.
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
May 20, 2003 at 8:29 am
I noticed you mentioned sysfiles in your earlier post. Usually stored procedures created in master and prefixed with sp_ will run in the context of the database they are called from. However, sysfiles is a special case. The trick here is any proc you create in master that needs to references sysfiles in the database it is run , needs to have the sp_MS_marksystemobject proc run against it.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply