Querying a linked server
To query a linked server, use the Transact-SQL OPENQUERY function. For example, to return all the data on the VDATA table from a linked server named MYLINKEDSERVER, use the following query:
SELECT * FROM OPENQUERY ( MYLINKEDSERVER, 'SELECT * FROM vdata' )
You can execute this query using the New Query feature in SQL Server Management Studio or from a third party application that can connect to a SQL Server database, such as Microsoft Excel or Microsoft Access.
Example query
The following SQL Server query demonstrates how to use a linked server to join the following two tables:
▪The VDATA table for a UNICOM Intelligence Interviewer (RDB) data source that containing the response data for a survey.
▪The table that contains the sample data for the same survey. (In this example, the sample table is in the Participants database.)
The two are joined by the respondent.serial column on the VDATA table and the serial column on the sample table. The linked server for the UNICOM Intelligence Interviewer data source is called MYFIRSTINTERVIEWLINKEDSERVER.
SELECT Responses.*, Sample.*
FROM OPENQUERY ( MYFIRSTINTERVIEWLINKEDSERVER, 'SELECT * FROM vdata' ) AS Responses,
Participants..MyFirstInterview AS Sample
WHERE Responses.[respondent.serial] = Sample.serial
Notes
For Microsoft SQL Server 2008, the allow in process option needs to be selected when creating a linked server to the UNICOM Intelligence DM-2 OLE DB Provider. To enable the option:
1 Open SQL Server Management Studio, navigate to the Object Explorer pane, and select Server Objects > Linked servers > Providers.
2 Right-click mrOledb.Provider and select Properties.
3 Select allow in process, and then click OK.
Requirements
Microsoft SQL Server
See also