In SQL Server 2005, how can I use database_b, do something, then use the old db database_a in TSQL?

Question

Hi guys,

In SQL Server 2005, how can I use database_b, do something, then use the old db database_a in TSQL? The following is my code but there is some problem with it. Who can help me to identity the problem? Great thanks.

DECLARE @old_database_name VARCHAR(200)
SET @old_database_name = db_name()

use mydatabase

create table t1(id int identity(1,1))

use @old_database_name

Answer 1

You'll need to use dynamic sql to do this.

e.g.

-- Do stuff in current DB here

EXECUTE ('USE mydatabase; create table t1(id int identity(1,1));')

-- Do more stuff in current DB here. This context will not have changed since before the EXECUTE statement

Answer 2

Why are you even using USE statements for this?

create table mydatabase.dbo.t1(id int identity(1,1)) 

Answer 3

This is similar to AdaTheDev's "EXECUTE" example, but uses sp_executesql :

-- Do stuff in current DB here

EXECUTE mydatabase..sp_executesql N'create table t1(id int identity(1,1));'

-- Do more stuff in current DB here. This context will not have changed since before the EXECUTE statement