SQL Server Performance Forum – Threads Archive
Change the in use databaseWhy the following code does not change the database in use to _New_db_name? The value for @DynSQL is USE [_New_db_name].
GO DECLARE @db_name varchar(100), @DynSQL varchar(512) SET @db_name = ‘_New_db_name’ SET @DynSQL = ‘USE [‘ + @db_name + ‘]’ Print @DynSQL
I tested in Express and work fine.
SQL-Server-Performance.com All in Love is Fair
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
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.
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” )
CREATE USER [TestUser] FOR LOGIN [TestUser]
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…
It makes sense. The dynamic query is being run in a different batch and USE works per batch. Thanks everybody! CanadaDBA