I'm trying to switch the current database with a SQL statement. I have tried the following, but all attempts failed:
To add a little more detail.
EDIT: I would like to perform several things on two separate database, where both are configured with a variable. Something like this:
USE Database1
SELECT * FROM Table1
USE Database2
SELECT * FROM Table2
exec sp_execsql @Sql
The DB change only lasts for the time to complete @sql
http://blog.sqlauthority.com/2007/07/02/sql-server-2005-comparison-sp_executesql-vs-executeexec/
The problem with the former is that what you're doing is USE 'myDB' rather than USE myDB.
you're passing a string; but USE is looking for an explicit reference.
The latter example works for me.
declare @sql varchar(20)
select @sql = 'USE myDb'
EXEC sp_sqlexec @Sql
-- also works
select @sql = 'USE [myDb]'
EXEC sp_sqlexec @Sql
use exec sp_execsql @Sql
Example
1. DECLARE @sql as nvarchar(100)
2. DECLARE @paraDOB datetime
3. SET @paraDOB = '1/1/1981'
4. SET @sql=N''
5. SET @sql=N'SELECT * FROM EmpMast'
6. SET @sql=@sql + ' WHERE DOB >= @paraDOB'
7. exec sp_executesql @sql,N'@paraDOB datetime',@paraDOB</code>
try this:
DECLARE @Query varchar(1000)
DECLARE @DatabaseName varchar(500)
SET @DatabaseName='xyz'
SET @Query='SELECT * FROM Server.'+@DatabaseName+'.Owner.Table1'
EXEC (@Query)
SET @DatabaseName='abc'
SET @Query='SELECT * FROM Server.'+@DatabaseName+'.Owner.Table2'
EXEC (@Query)
-- If you are using a variable for the database name.
-- Try something like this.
DECLARE @DBName varchar(50)
Set @DBName = 'Database1'; /* <-- This could be passed in by a parameter. */
IF( @DBName = 'Database1')
Begin
USE [Database1];
SELECT * FROM Table1;
End
IF( @DBName = 'Database2')
Begin
USE [Database2];
SELECT * FROM Table2;
End
IF( @DBName is null)
Begin
USE [Database1];
End
If SQLCMD is an option, it supports scripting variables above and beyond what straight T-SQL can do. For example: http://msdn.microsoft.com/en-us/library/ms188714.aspx