T-SQL: USING Linked Servers Example

Jack Donnell,
[email protected]

Using Linked Servers allows you to extend your local database to 
access a wide range of data sources. I use these to do look-ups 
and compare data from multiple systems. Extremely useful if you are
upgrading or trying to maintain multiple systems or provide warehousing 
views into multiple systems.

WARNING: USE A GOOD SECURITY MODEL!!!
The Linked Servers use specific rights granted to either the logged in user and/or 
a mapped local user. So watch out who you give the 'Keys to the Kingdom'.


Some Simple Examples:

Look-up for missing objects
Select * from OpenQuery (My_DBRemote, 'Select * from sysobjects')
where name not in (select  from LocalDB..sysobjects where type in ('u','v','p') )

Fire a Stored Procedure
 Select * from OpenQuery(My_DB, 'sp_Data_Return')

Use Extended Stored Proceedures
 Select * from OpenQuery(My_DB, 'sp_helptext dtsp_MYProc')

Create a Local View

Create View Peek_Into_Remote_DB_VW
AS
Select * from OpenQuery(My_DB2, 'Select name, title, release_date, Genre from MYTABLE')
where release_date between '02/10/2001' and '11/15/2003'
go

Load a Table

INSERT INTO localDB..OBJECT_TABLE(Id, ObjectId, ObjectType, RootObjectId, DateCreated)
Select * from openquery(Remote_OBJ_DB,'SELECT Id, ObjectId, ObjectType, RootObjectId, DateCreated from OBJECT_TABLE  NOLOCK')

Here is a bit more complex cursor and linked server EXAMPLE.