DYNAMICALLY SET DATABASE?

[sagn]

hI

I was wondering if it were possible to dynamically set the database with the use statement?

I have tried

exec ('use '+@db)

and have tried to use

exec sp_executesql N'use '+@db,N'@db varchar(50)',@db=@dbtobeused

neither seem to work. I am thinking this is because it is out of scope. Is there any way to be able to do this?

thanks!

 

[ptheriault]

I think your first method would will work if you put a space between use and @db
like this.

EXEC ('USE'+' '+@db)

- Paul
- Database performance looks fine, it must be the Network!

 

[sagn]

Hmm I tried it (it actually results in adding an extra space as I did have one after USE (as 'USE ')

Anyway, still no dice.

Any other suggestions?

thanks!

 

[ptheriault]

I was wrong, it doesn't work.

- Paul
- Database performance looks fine, it must be the Network!

 

[ptheriault]

This works


DECLARE @db varchar(100)
,@sql varchar(100)

SELECT @db = 'ConfigTest'


SELECT @sql = 'USE'+' '+@db+' '+ 'SELECT * FROM FAQ'

EXEC (@sql)

- Paul
- Database performance looks fine, it must be the Network!

 

[sagn]

Yeah.. I know. I did that too...

I was just hoping to be able to use ONE dynamic statement
in the very beginning but it seems I have to do it for every
command I want to execute. Again, I think its a scope thing.

thanks anyway!

 

[SQLDenis]

Just prefix your object with the db name

DECLARE @db varchar(100),@db2 varchar(100)
,@sql varchar(100)

SELECT @db = 'pubs'
SELECT @db2 = 'master'


SELECT @sql = 'SELECT top 10 * FROM '+ @db + '..authors cross join ' + @db2 + '..sysobjects'

print (@sql)
exec (@sql)

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[gmmastros]

I agree with Denis. Just make sure you accomodate data bases with a space in the name by wrapping it in square brackets, like so...

SELECT @sql = 'SELECT top 10 * FROM [!][[/!]'+ @db + '[!]][/!]..authors cross join [!][[/!]' + @db2 + '[!]][/!]..sysobjects'


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[sagn]

Yeah, I know it can be done that way. Being able to
get the job done was not really the problem. I am just lazy and as such wanted to be able to use only one EXEC(@STR) and not have to worry about constructing a string for each operation I want to perform.

Thanks anyway though!

 

[SQLDenis]

or like this

DECLARE @db varchar(100),@db2 varchar(100)
        ,@sql varchar(100)

SELECT @db = '[pubs]'
SELECT @db2 = '[master]'


SELECT @sql = 'SELECT top 10 * FROM '+  @db + '..authors cross join ' +  @db2 + '..sysobjects'

print (@sql) 
exec (@sql)

or by using the handy dandy QUOTENAME

DECLARE @db varchar(100),@db2 varchar(100)
        ,@sql varchar(100)

SELECT @db = quotename('pubs')
SELECT @db2 = quotename('master')


SELECT @sql = 'SELECT top 10 * FROM '+  @db + '..authors cross join ' +  @db2 + '..sysobjects'

print (@sql) 
exec (@sql)

Don't you know that ISO 11179-5 standards forbid databases with spaces in them ;-)

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[gmmastros]

True.

I hate db names with spaces. Even though there is an ISO standard, it doesn't mean people follow them.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[sagn]

While we are on the topic.. I hate all names with spaces!
drives me crazy.

 

[ptheriault]

you should try working with a database that starts with a number.

- Paul
- Database performance looks fine, it must be the Network!