<TAG>Simple HTML& Javascripting Tricks & Tips


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.

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
Select * from OpenQuery(My_DB2, 'Select name, title, release_date, Genre from MYTABLE')
where release_date between '02/10/2001' and '11/15/2003'

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.