How to Query Linked Servers

Linked servers in SQL Server allow programmers to query tables on a separate database. The other database can be another SQL server, or it can be an Access database or Oracle server. As long as drivers are available and the main database has permissions to access the linked server, programmers can query the data using the "OpenQuery()" function.

Difficulty:

Moderate

Instructions

1. Click the Windows "Start" button and select "All Programs." From the menu, select "Microsoft SQL Server Enterprise Manager". This opens the console where you can create queries on the databases.

2. Use "sp_addlinkedserver" to link the server in the code. If the administrator has not set up a linked server, you can link it using this SQL command. The following code links an Oracle server named "OracleDB."
   EXEC sp_addlinkedserver 'OracleDB',
   'Oracle 7',
   'myUser',
   'myPass'
   GO

3. Run a select query. Here is an example of how to select data from the linked server.
   select *
   from openquery(OracleDB, 'select last_name, first_name from myDatabase.Customer')

4. Run an update query on the linked server. The "Update" command replaces data. For instance, the following code replaces a customer's last name with the data indicated.
   update openquery (OracleDB, 'select last_name from myDatabase.Customer where id = 22')
   SET last_name = 'MyNewLastName'

5. Run a delete query. Delete queries should be made with caution. Ensure that deleting data does not damage data integrity.
   delete openquery (OracleDB, 'select last_name from myDatabase.Customer where name = ''DeleteThisRecord''');

6. Run an insert statement. The following code inserts a new record into the linked database server.
   insert openquery (OracleDB, 'select last_name from myDatabase.Customer')
   values ('MyNewRecord')

References

• Microsoft