Select from DB on a different server for a view

[BSman]

I need to select data from a table that is in a database on a different server. What is the syntax for the select statement used to create the view? This is initially for a database originally on SQL2000 that, in a view, selects data from a different database (I'll call it "Reference Data") on the same server. The database was moved to SQL2005 (on a different server), but "Reference Data" must remain in SQL2000 on its current server. I need to modify the view so the proper identification of the SQL2000 server is included in its SQL statement.

Bob

 

[mrdenny]

You need to setup a linked server to the remote server. Then use the four part name.

SELECT *
FROM RemoteServer.DatabaseName.Owner.Table

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog