Change database dynamically

Last Post 23 Feb 2006 09:54 AM by natasha. 5 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
natasha
New Member
New Member

--
23 Feb 2006 05:20 AM
Here is the code:
declare
@cmd varchar(4000),
@cmd2 varchar(4000)
@dbname sysname
select @cmd = 'use ' + '@Dbname + Char(13)
exec (@cmd)
select * from test1

barfs at me with an eror 'invalid object test1'

If I change above code to this one it works.

Select @cmd = 'use' + @dbname + char(13)
select @cmd2 = 'Select * from ' + @Dbname + '..test1'
select @cmd = @cmd + @Cmd2
exec (@cmd)

Is there any other way I can change Database Name dynamically and then be able to execute series of SQL code??

TIA,
Nat
natasha
New Member
New Member

--
23 Feb 2006 07:42 AM
Thanks for your effort, however, your script is not any different than my second code and also when I tried executing your code it barfed at me with an error incorrect syntax at 'GO'. Somehow 'Go' is not recognized as a batch terminator/seprator in dynamic SQL.
Nat
natasha
New Member
New Member

--
23 Feb 2006 08:09 AM
Rm,
I modified your script slightly and that seem to work. I just substituted it with CHAR(13).
Now the problem is I am maxing out at the varchar(4000), stupid code is a mile long.

declare @Script nvarchar(2500), @Sql nvarchar(4000), @DbName nvarchar(25)

set @DbName = '<DBNAME>'
set @Script = '<SQL SCRIPT>'
set @Sql = @Sql + 'use ' + @DbName + char(13) + @Script

execute sp_executesql @Sql

Thanks for the help though. I might end up splitting the code.
nat
mimadon
New Member
New Member

--
23 Feb 2006 08:30 AM
Why do you need the "USE" at all?

Your original code was already using a three-part identifier to reference the object within the 'other' database. Just get rid of the 'USE' altogether.

select @dbname = 'YourDesiredDatabase'
select @cmd = 'Select * from ' + @dbname + '.dbo.test1'
exec (@cmd)
natasha
New Member
New Member

--
23 Feb 2006 09:54 AM
Reason I am trying to use "Use" is,
I can directly refer to the objects in that database without specifying the full part in my dynamic sql
once the database environment is set.

Inside the script I am joining at least 15 tables(minimum), invoking procedures and also few functions. Also, this script will run against 100 + databases
Nat

natasha
New Member
New Member

--
23 Feb 2006 11:38 AM
My code believe it or not exceeds 8000 characters. I have broken it into smaller chunks and is working as expected.
Thanks to all of you for their suggestions and interest in helping.
Nat
You are not authorized to post a reply.

Acceptable Use Policy