How to change DB context using a variable

Question

Post reply

How to change DB context using a variable

Ryan-209402

Hall of Fame

Points: 3707
More actions
May 1, 2006 at 12:00 pm

#113084

I wish to change db context in a script by using a variable. The script below illustrates what I wish to do:
DECLARE @dbname varchar(30)
SET @dbname = 'pubs'
PRINT @dbname
USE @dbname
However, I am getting the error:
Server: Msg 170, Level 15, State 1, Line 6
Line 6: Incorrect syntax near '@dbname'.
Printing the variable using "PRINT" works but trying to switch to another db using "USE" does not. Not sure what I'm missing here.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Pam Brisjar SSChampion Points: 12094 More actions · Answer 1

Try this:

declare @sqlt varchar (50)

set @sqlt = 'USE ' + @dbname

exec (@sqlt)

Ryan-209402 Hall of Fame Points: 3707 More actions · Answer 2

Thanks for your reply. This runs without error but it does not change the db context.

DECLARE @dbname varchar(30)

SET @dbname = 'pubs'

declare @sqlt varchar (50)

set @sqlt = 'USE ' + @dbname

exec (@sqlt)

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

It does change it. The exec creates a new execution context. The db is use is changed for that new context but not for the current context.

What do you need to do exactly (many possible solutions from here).

Ryan-209402 Hall of Fame Points: 3707 More actions · Answer 4

One of the things that I'm trying to do is add users in a db. The script starts out running in Master but then, for testing purposes, I would like to switch to Pubs to run something like the following:

DECLARE @dbname varchar(30)

SET @dbname = 'pubs'

declare @sqlt varchar (50)

set @sqlt = 'USE ' + @dbname

exec (@sqlt)

EXEC sp_grantdbaccess 'machinename\IUSR_machinename', 'machinename\IUSR_machinename'

However, when I run this I don't see the db context change at the top of the Query Analyzer window (like it does when I just type "USE pubs") and the new user gets created in Master instead of Pubs, indicating that the context was not switched. Thanks.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

As I said it changes in the exec itself, but that context is completely independant than the calling context.

Try something like this

DECLARE @dbname varchar(30)

SET @dbname = 'pubs'

declare @sqlt varchar (500)

set @sqlt = 'USE ' + @dbname + '

EXEC sp_grantdbaccess ''machinename\IUSR_machinename'', ''machinename\IUSR_machinename'''

print @sqlt

--exec (@sqlt)

Ryan-209402 Hall of Fame Points: 3707 More actions · Answer 6

This provides the expected result after uncommenting "exec (@sqlt)". Thanks for your help.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

That's just a good habit.... First print the command, see if it will exec what you think it must exec, then make it run. If you don't do that you can wake up with a few nasty surprises.