T-SQL: USING Linked Servers Example
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.