Changing database context with dynamic SQL

Question

Changing database context with dynamic SQL

Stuart Davies

SSCoach

Points: 19505
More actions
May 10, 2004 at 6:49 am

#84409

I have a problem with changing database context with dynamic SQL.
I have table
Objects
[OdjectId] [int] IDENTITY (1, 1) NOT NULL ,
[ObjectText] [varchar] (500) COLLATE Latin1_General_BIN NULL ,
[DbName] [varchar] (20) COLLATE Latin1_General_BIN NULL ,
[ObjectName] [varchar] (50) COLLATE Latin1_General_BIN NULL
and
sp as
declare @text varchar (500),
@db varchar (20),
@Object varchar(50),
@rows int
DECLARE spCursor CURSOR FOR
  select
  ObjectText,
  DbName,
  ObjectName
  from Objects
open spCursor
FETCH NEXT FROM spCursor into @text,@db,@Object
while @@fetch_status = 0
begin
  set @rows = (select count(*) from Cornerstone.dbo.sysobjects where name = @Object)
   if @rows = 0
   begin
    select @db
    exec ('use ' + @db )
    select * from sysobjects
    select @text
    exec (@text )
   end
  fetch next from spCursor into @text,@db,@Object
end
close spCursor
deallocate spCursor
The problem is that when the objects are created, they are created in the context of the current db not in the context of the the db from the objects table. - Is there any way I can get the objects created in the correct db ?
Thanks in advance
Stuart
-------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Stuart Davies SSCoach Points: 19505 More actions · Answer 1

Sorry just re-read this and it makes no sense unless I state that Field ObjectText on Objects Table contains "create table (view) name as ......." which is the definition of an object to be created in the target db - as specified in the dbName field

-------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx

Guy Haskell Ten Centuries Points: 1212 More actions · Answer 2

As far as I am aware, any context changes (current DB, SET options etc) only apply for the duration of the dynamically executed batch.

So:

use db1

exec ('use db2')

select db_name()

will return "db1", but

use db1

exec ('use db2 select db_name()')

will return "db2"

Stuart Davies SSCoach Points: 19505 More actions · Answer 3

Thanks for your help - I've now sorted it.

-------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx