- 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.
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.
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:
Post a Comment