April 3, 2008 at 6:06 pm
Anyone know why is not working?
declare @sql varchar(4000), @db sysname
set @db = 'master'
set @sql = 'USE ' + @db
exec (@sql)
April 3, 2008 at 6:23 pm
Because you can't change context like that. Can't do it in a procedure either. Basically, "USE xyz db" only works in a query editor.
April 3, 2008 at 10:26 pm
How do i fix my code?
April 3, 2008 at 11:42 pm
The [font="System"]USE db[/font] command only changes the session it was executed in. [font="System"]EXEC(@sql)[/font] does not execute the [font="System"]@sql [/font]text in your current session, but creates a new session and executes it there, then it exits back to your original session.
So you cannot use dynamic SQL to change your current session, only that Temporary session that goes away after the string execute.
So, how can you fix it? Easy (but messy). After you make your [font="System"]@sql[/font] string with the [font="System"]USE [/font]command, then add to the [font="System"]@sql[/font] string, all of the other SQL commands that you were going to execute. Now when you execute that temporary session, it will also do all of the other work there also.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 4, 2008 at 9:57 am
Beautiful.
Thanks.
April 6, 2008 at 12:09 am
glad I could help.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply