September 8, 2004 at 3:07 pm
I've got a bit of a problem that I can't quite figure out, and it's driving me a bit nuts. I'm trying to create a procedure that generates multiple insert statements and executes them based off of a server string passed in through a variable. Something along the lines of this:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
create procedure psPopulate
@sourcedb varchar(200)
as
declare @sqlstate varchar(8000)
set @sqlstate =
'
insert into group1 (group_code, [description], sort_order)
select group_code, [description], sort_order
from ' + @sourcedb + 'group1
insert into type1 (type_code, [description], group_fk, sort_order)
select type_code, [description], group_fk, sort_order
from ' + @sourcedb + 'type1
'
exec (@sqlstate)
SET QUOTED_IDENTIFIER OFF
go
SET QUOTED_IDENTIFIER OFF
go
SET ANSI_NULLS ON
go
exec psPopulate 'myserver.oldDB.dbo.'
gives me the error:
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'type1'.
I've got about 200 tables that I've got to do this for in order to reconcile different versions of our databases, so any suggestions would be appreciated.
September 8, 2004 at 10:09 pm
Is the type1 table in the same server.database from which you're executing the stored proc? It sounds like the table doesn't exist in that database.
September 9, 2004 at 7:42 am
dammit, I just realized that I missed the fact that the insert is attempting to execute in a different database even though the procedure is being run from the correct one. Thanks for asking that question, it helped just enough for me to realize what I was doing wrong.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply