February 23, 2009 at 11:09 am
Hi,
We upgrading sql 2000 to 2005 using side-by-side method.It is a 3rd party application.We have 2 linked servers in sql 2000.
What is the best way to move these linked servers to 2005?
can we script the linked servers and run in sql 2005?
I have no idea what these linked servers are doing, in this case what information I need to ask the 3rd party vendor?
Thanks
Kotla
February 23, 2009 at 1:00 pm
Hi Kotlas
Open the properties from your linked server in SQL 2000, keep that window open.
Create a new linked server in SQL 2005 and arrange both windows side by side. The options are pretty much the same in SQL 2000 and 2005.
If you absolutely want to script it look into these procedures:
sp_addlinkedserver
sp_addlinkedsrvlogin
sp_serveroption
You could also create a Linked Server in SQL 2005 and generate a script from it. You will see how the procedures are used.
We use them here when an application needs to access another application's database. We want to avoid code like:
SELECT field FROM Database.dbo.Table
and instead use:
SELECT field FROM LinkedServer.Database.dbo.Table
If the database ever needs to be moved to another server, this avoids rewriting a lot of code since we just need to recreate the linked server on the new machine.
They can be used to connect to many different data sources and you can also change the security context so it's hard to guess what the 3rd party uses it for.
You should have at least a good hint in the properties (what provider/server).
February 23, 2009 at 5:00 pm
There's something in the script section of SSC that I've used to script out linked servers and recreate them on other servers: http://www.sqlservercentral.com/scripts/Miscellaneous/30620/.
Greg
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply