EXEC for using the database
I wrote a script that generates a database backup file name, creates a database backup, and then restores it as a copy with a new database name. The name is based on date / time data.
Then I need USE
this database in a script and then disable all triggers.
However, this doesn't work:
DECLARE @Use VARCHAR(50)
SET @Use = 'USE ' + @NewDatabaseName
EXEC(@Use)
Manual Start - Database does not receive "USED".
How can I execute a statement USE
on a variable?
I tried sp_executesql
proc too , with the same result. The database has not changed.
DECLARE @sqlCommand nvarchar(1000)
SET @sqlCommand = 'USE ' + @NewDatabaseName
EXECUTE sp_executesql @sqlCommand
It looks like I might have to go into sqlcmd mode? Not really hoping for that.
source to share
Both exec
and execute_sql
are working in their field. And the change in the database will only affect their own scope. Thus, you could:
set @sql = 'use ' + quotename(@new_db_name) + '; disable trigger t1;'
exec (@sql)
As far as I know, there is no way to change the database context of the current scope to the name of a database variable.
source to share