Change the in use database - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Change the in use database

Author

Topic

CanadaDBA

583 Posts

Posted - 2007-03-26 : 13:32:03

In SMSS or QA why the following code does not change the database in use to My_db? The value for @DynSQL is USE [My_db].


USE [master]
GO

DECLARE @db_name varchar(100), @DynSQL varchar(512)

SET @db_name = 'My_db'

SET @DynSQL = 'USE [' + @db_name + ']'

Print @DynSQL
EXEC (@DynSQL)
GO

It seems the change occurs inside the dynamic portion because if I SELECT a table within that database, it works. For example the following works but it does not change the default database to My_db.

SET @DynSQL = 'USE [' + @db_name + ']'+' SELECT * FROM Mytable'

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-03-26 : 14:33:37

of course it doesn't.
exec runs in a different batch and USE works per batch.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

CanadaDBA

583 Posts

Posted - 2007-03-26 : 15:26:26

It makes sense. Thanks

Canada DBA

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-03-26 : 18:04:56

one way you can do something like this is to execute the script with sqlcmd.exe and pass the database in the -d flag.

www.elsasoft.org

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources