SQL Server Performance Forum – Threads Archive

Change the in use database

Why the following code does not change the database in use to _New_db_name? The value for @DynSQL is USE [_New_db_name].
USE [master]
GO DECLARE @db_name varchar(100), @DynSQL varchar(512) SET @db_name = ‘_New_db_name’ SET @DynSQL = ‘USE [‘ + @db_name + ‘]’ Print @DynSQL
EXEC (@DynSQL)
GO
CanadaDBA

I tested in Express and work fine.
Luis Martin
Moderator
SQL-Server-Performance.com All in Love is Fair
Stevie Wonder
All postings are provided â€œAS ISâ€ with no warranties for accuracy.

What you are trying to do with this code…. Check the following link for dynamic sql… http://www.sommarskog.se/dynamic_sql.html
MohammedU.
Moderator
SQL-Server-Performance.com

I don’t know how it worked for you Luis. Probably you ran it on QA in 2000. I am trying on SMSS 2005. I was going to USE another database dynamically and then run some queries. It seems it does work if I write the dynamic query as below.
USE [master]
GO DECLARE @db_name varchar(100), @DynSQL varchar(512)SET @db_name = ‘_New_db_name’ SET @DynSQL = ‘USE [‘ + @db_name + ‘]
IF NOT Exists(select name from sysusers WHERE name = ”TestUser” )
begin
CREATE USER [TestUser] FOR LOGIN [TestUser]
end
EXEC sp_addrolemember N”db_datareader”, N”TestUser”’ EXEC (@DynSQL) But still the database in USE is "master". CanadaDBA

The new script should work… it is woking…
But you don’t see your database chane in Query window becuase you not change your connection database…. where as you are asking sql to execute the statements after USE <DB NAME>…. Check the db that the users TESTUSER created or not… and make sure Login exists…
MohammedU.
Moderator
SQL-Server-Performance.com

It makes sense. The dynamic query is being run in a different batch and USE works per batch. Thanks everybody! CanadaDBA

]]>