Background: I'm going to soon be moving a database from SQL 2000 to 2008. The complication is this database contains many references to purchased piece of software that is unfortunately not compatible with SQL 2008. So that database has to stay put. My solution is to use a linked server so that those queries(many in Stored Procedures) can still access the 2000 database once they get moved to the new server. In order to update the code in place, so I can just move the database later, I want to create linked server on the existing SQL 2000 server that points to itself.
Problem: When trying to add this in Management Studio, I get an error that says
You cannot create a local SQL Server as a linked server.
Is there a way around this?
Edit: I hate when I think of stuff right after I post the question. I don't need to create a linked server because I can reference the current server by its name (probably why I can't link to self), and that same reference will work on the new server that has the linked server defined. Example select * from dwdata2.m2mdata01.dbo.inmast will work on the current server and on the new one with dwdata2 defined as a linked server.
I don't need to create a linked server because I can reference the current server by its name (probably why I can't link to self), and that same reference will work on the new server that has the linked server defined. Example select * from dwdata2.m2mdata01.dbo.inmast will work on the current server and on the new one with dwdata2 defined as a linked server.