Friday, December 12, 2008

Run Query on Two Different Database on Two Different Servers

A linked server configuration allows Microsoft SQL Server to execute commands against OLE DB data sources on different servers. Linked servers offer these advantages:
  • Remote server access.
  • The ability to issue distributed queries, updates, commands, and transactions on heterogeneous data sources across the enterprise.
  • The ability to address diverse data sources similarly.
Managing a Linked Server Definition

Register the connection information and data source information with SQL Server.

You can create or delete a linked server definition with stored procedures or through SQL Server Enterprise Manager.

With Stored Procedure:

Create a linked server definition using sp_addlinkedserver.
Syntax
sp_addlinkedserver [ @server = ] 'server'
[ , [ @srvproduct = ] 'product_name' ]
[ , [ @provider = ] 'provider_name' ]
[ , [ @datasrc = ] 'data_source' ]
[ , [ @location = ] 'location' ]
[ , [ @provstr = ] 'provider_string' ]
[ , [ @catalog = ] 'catalog' ]
Example
EXEC sp_addlinkedserver
@server = 'SEATTLE Mktg',
@srvproduct = 'OLE DB Provider for Jet',
@provider = 'Microsoft.Jet.OLEDB.4.0',
@datasrc = 'C:\MSOffice\Access\Samples\Northwind.mdb'

Delete a linked server definition using sp_dropserver.
Syntax
sp_dropserver [ @server = ] 'server'
[ , [ @droplogins = ] { 'droplogins' | NULL} ]
Examples
sp_dropserver ‘SEATTLE Mktg', 'droplogins'

With SQL Server Enterprise Manager:
  • SQL Server Enterprise Manager Console -- Server objects -- Linked Servers
  • Right Click -- New Linked Server
  • Define the name, provider properties, server options, and security options for the linked server.
  • Edit a linked server definition by right-clicking the linked server and clicking Properties.
  • Delete a linked server definition by right-clicking the linked server and clicking Delete.
When executing a distributed query against a linked server, include a fully qualified, four-part table name for each data source to query.

This four-part name should be in the form linked_server_name.catalog.schema.object_name.

Select * From [linkedservername or IP].DatabaseName.sys.Columns

No comments: