views:

132

answers:

3

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')
A: 

Check to see that the name is correctly written in the @SourceDB and @TargetDB variables.

To get a list of the names registered as linked servers, use the following code

sp_linkedservers

Read more on the procedure

Raj More
Both my @SourceDB and @TargetDB variables are correct, I had already checked my linked servers to make sure they were mapped correctly. Thanks for the thought though.
A: 

Code moved up to original question

A: 

So it turns out I was doing something stupid, in that I was actually running the query from a SQL2000 instance, and my source db was on a SQL2005 instance, and apparently that is not good. When I ran my query from the 2005 instance, it ran just fine. Thanks for all the input, and sorry for the run around...