I am trying to script some inserts from a source database to a target database, using linked servers. For the first time we are using a second named instance of SQL, and it is giving me an "Internal SQL Server Error" when I run the following code. If I remove the brackets around the named instance, I no longer get the Internal SQL Server Error, but I do get a "Incorrect syntax near '\'." error. Any help is much appreciated.
declare @listname as varchar(80)
declare @sourcedb as sysname
declare @targetdb as sysname
set @listname = 'List(r) Stuff - Planning'
set @sourcedb = '[server1.domain.com\server1sql2005].database.dbo.'
set @targetdb = 'server2.database.dbo.'
print @listname
exec ('if not exists (select * from ' + @targetdb + 'listmatchingheader where listname = ''' + @listname + ''')
begin
insert ' + @targetdb + 'listmatchingheader (listname, userdefinedname1, userdefinedname2, userdefinedname3, siterecnum, dolplistnum, listdate, regdate, customlist, footer)
select
mlmh.listname,
mlmh.userdefinedname1,
mlmh.userdefinedname2,
mlmh.userdefinedname3,
mlmh.siterecnum,
mlmh.dolplistnum,
mlmh.listdate,
mlmh.regdate,
mlmh.customlist,
mlmh.footer
from ' + @sourcedb + 'listmatchingheader mlmh
where mlmh.listname = ''' + @listname + '''
end
else
begin
update ' + @targetdb + 'listmatchingheader set
listname = mlmh.listname,
userdefinedname1 = mlmh.userdefinedname1,
userdefinedname2 = mlmh.userdefinedname2,
userdefinedname3 = mlmh.userdefinedname3,
siterecnum = mlmh.siterecnum,
dolplistnum = mlmh.dolplistnum,
listdate = mlmh.listdate,
regdate = mlmh.regdate,
customlist = mlmh.customlist,
footer = mlmh.footer
from ' + @targetdb + 'listmatchingheader lmh
inner join ' + @sourcedb + 'listmatchingheader mlmh on lmh.listname = mlmh.listname
where mlmh.listname = ''' + @listname + '''
end
')
exec ('
delete ' + @targetdb + 'listmatching
from ' + @targetdb + 'listmatching lm
inner join ' + @targetdb + 'listmatchingheader lmh on lm.listrecnum = lmh.listrecnum
where lmh.listname = ''' + @listname + '''
')
exec ('
delete ' + @targetdb + 'listitemcass
from ' + @targetdb + 'listitemcass lic
where not exists (select * from ' + @targetdb + 'listmatching where listitemrecnum = lic.listitemrecnum)
')
exec ('
delete ' + @targetdb + 'listitemingreds
from ' + @targetdb + 'listitemingreds lii
where not exists (select * from ' + @targetdb + 'listmatching where listitemrecnum = lii.listitemrecnum)
')
exec ('
insert ' + @targetdb + 'listmatching (listrecnum, cas, ingredient, userdefineddata1, userdefineddata2, userdefineddata3)
select
lmh.listrecnum,
mlm.cas,
mlm.ingredient,
mlm.userdefineddata1,
mlm.userdefineddata2,
mlm.userdefineddata3
from ' + @sourcedb + 'listmatching mlm
inner join ' + @sourcedb + 'listmatchingheader mlmh on mlm.listrecnum = mlmh.listrecnum and mlmh.listname = ''' + @listname + '''
inner join ' + @targetdb + 'listmatchingheader lmh on mlmh.listname = lmh.listname
')
exec ('
insert ' + @targetdb + 'listitemcass (listitemrecnum, cas, abstrue)
select
lm.listitemrecnum,
mlic.cas,
mlic.abstrue
from ' + @sourcedb + 'listitemcass mlic
inner join ' + @sourcedb + 'listmatching mlm on mlic.listitemrecnum = mlm.listitemrecnum
inner join ' + @sourcedb + 'listmatchingheader mlmh on mlm.listrecnum = mlmh.listrecnum and mlmh.listname = ''' + @listname + '''
inner join ' + @targetdb + 'listmatchingheader lmh on mlmh.listname = lmh.listname
inner join ' + @targetdb + 'listmatching lm on lmh.listrecnum = lm.listrecnum and mlm.cas = lm.cas and mlm.ingredient = lm.ingredient and mlm.userdefineddata1 = lm.userdefineddata1 and mlm.userdefineddata2 = lm.userdefineddata2 and mlm.userdefineddata3 = lm.userdefineddata3
')
exec ('
insert ' + @targetdb + 'listitemingreds (listitemrecnum, ingredienttext, abstrue)
select
lm.listitemrecnum,
mlii.ingredienttext,
mlii.abstrue
from ' + @sourcedb + 'listitemingreds mlii
inner join ' + @sourcedb + 'listmatching mlm on mlii.listitemrecnum = mlm.listitemrecnum
inner join ' + @sourcedb + 'listmatchingheader mlmh on mlm.listrecnum = mlmh.listrecnum and mlmh.listname = ''' + @listname + '''
inner join ' + @targetdb + 'listmatchingheader lmh on mlmh.listname = lmh.listname
inner join ' + @targetdb + 'listmatching lm on lmh.listrecnum = lm.listrecnum and mlm.cas = lm.cas and mlm.ingredient = lm.ingredient and mlm.userdefineddata1 = lm.userdefineddata1 and mlm.userdefineddata2 = lm.userdefineddata2 and mlm.userdefineddata3 = lm.userdefineddata3')