| Author |
How to use USE and database as a variable
|
|
|
| Hello!
I am trying to change to another database in the middle of transaction
execution. The problem is that the database is a variable (below it is @DB).
I have tried with:
use @DB
exec ('use ' + @DB)
exec ("use [" + @DB + "]")
but I can't figure out how to do. Anyone, some ideas?
Regards Magnus
| |
| Cristian 2002-08-02, 7:25 am |
| Hi !
Can't use something like
declare @sqlString nvarchar(1000)
set @sqlString = 'select MyColumn from ' + @DB + '..MyTable'
execute sp_executesql @sqlString
- Cristian
"MB" <magnus.blomberg@skanska.se> wrote in message
news:#Xvyx8hOCHA.2308@tkmsftngp08...
> Hello!
>
> I am trying to change to another database in the middle of transaction
> execution. The problem is that the database is a variable (below it is
@DB).
> I have tried with:
>
> use @DB
> exec ('use ' + @DB)
> exec ("use [" + @DB + "]")
>
> but I can't figure out how to do. Anyone, some ideas?
>
> Regards Magnus
>
>
| |
| Dan Guzman 2002-08-02, 7:25 am |
| The EXEC statements you posted will change the database context but only
within the scope of the EXEC; the context will revert to the current
database once the EXEC completes. You'll need to also include the SQL
statements referencing the database in the same EXEC:
DECLARE @DB sysname
SET @db = 'pubs'
EXEC ('USE ' + @DB + ' SELECT * FROM authors')
GO
Alternatively, you can qualify the individual object names within the
EXEC:
DECLARE @DB sysname
SET @db = 'pubs'
EXEC ('SELECT * FROM ' + @db + '..authors')
GO
See Erland's article for a more thorough discussion on dynamic SQL:
http://www.algonet.se/~sommar/dynamic_sql.html
Hope this helps.
Dan Guzman
SQL Server MVP
-----------------------
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index...epartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
-----------------------
"MB" <magnus.blomberg@skanska.se> wrote in message
news:#Xvyx8hOCHA.2308@tkmsftngp08...
> Hello!
>
> I am trying to change to another database in the middle of transaction
> execution. The problem is that the database is a variable (below it is
@DB).
> I have tried with:
>
> use @DB
> exec ('use ' + @DB)
> exec ("use [" + @DB + "]")
>
> but I can't figure out how to do. Anyone, some ideas?
>
> Regards Magnus
>
>
|
|
|
|