June 6, 2008 at 2:36 pm
Can linked server be used as a variable in a stored procedure?
I have a stored procedure writing to an Oracle database via linked server and now need it to write to identical structure to another Oracle database via new linked server.
(two locations, two db names)
Tried passing the linked server name as a variable and received a syntax error.
create procedure dbo.sp_migrate_data @dbname NVARCHAR(8)
execute sp_migrate_data DBONE
INSERT INTO @dbname..schema.table
execute sp_migrate_data DBTWO
INSERT INTO @dbname..schema.table
No bueno?
June 6, 2008 at 2:42 pm
You would need to use dynamic sql and even then I am not sure is would work. Something like:
Declare @sql varchar(max)
Set @sql = 'insert into ' + @dbname + '.database.schema.table ...'
Exec (@sql)
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
June 6, 2008 at 4:28 pm
June 9, 2008 at 7:59 am
Thanks guys. Working through the code now.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply