SQL 2012 Linked servers

16/12/2012

תגובה אחת

sp_addlinkedserver (Transact-SQL)

Creates a linked server. A linked server allows for access to distributed, heterogeneous queries against OLE DB data sources. After a linked server is created by usingsp_addlinkedserver, distributed queries can be run against this server. If the linked server is defined as an instance of SQL Server, remote stored procedures can be executed.

Syntax

sp_addlinkedserver [ @server= ] 'server' [ , [ @srvproduct= ] 'product_name' ]      [ , [ @provider= ] 'provider_name' ]      [ , [ @datasrc= ] 'data_source' ]      [ , [ @location= ] 'location' ]      [ , [ @provstr= ] 'provider_string' ]      [ , [ @catalog= ] 'catalog' ] 
        

Arguments


[ @server= ] 'server'

Is the name of the linked server to create. server is sysname, with no default.

[ @srvproduct= ] 'product_name'

Is the product name of the OLE DB data source to add as a linked server. product_name is nvarchar(128), with a default of NULL. If SQL Server, provider_name,data_source, location, provider_string, and catalog do not have to be specified.

[ @provider= ] 'provider_name'

Is the unique programmatic identifier (PROGID) of the OLE DB provider that corresponds to this data source. provider_name must be unique for the specified OLE DB provider installed on the current computer. provider_name is nvarchar(128), with a default of NULL; however, if provider_name is omitted, SQLNCLI is used. (Use SQLNCLI and SQL Server will redirect to the latest version of SQL Server Native Client OLE DB Provider.) The OLE DB provider is expected to be registered with the specified PROGID in the registry.

[ @datasrc= ] 'data_source'

Is the name of the data source as interpreted by the OLE DB provider. data_source is nvarchar(4000). data_source is passed as the DBPROP_INIT_DATASOURCE property to initialize the OLE DB provider.

[ @location= ] 'location'

Is the location of the database as interpreted by the OLE DB provider. location is nvarchar(4000), with a default of NULL. location is passed as the DBPROP_INIT_LOCATION property to initialize the OLE DB provider.

[ @provstr= ] 'provider_string'

Is the OLE DB provider-specific connection string that identifies a unique data source. provider_string is nvarchar(4000), with a default of NULL. provstr is either passed to IDataInitialize or set as the DBPROP_INIT_PROVIDERSTRING property to initialize the OLE DB provider.

When the linked server is created against the SQL Server Native Client OLE DB provider, the instance can be specified by using the SERVER keyword as SERVER=servername\instancename to specify a specific instance of SQL Server. servername is the name of the computer on which SQL Server is running, andinstancename is the name of the specific instance of SQL Server to which the user will be connected.

NoteNote

To access a mirrored database, a connection string must contain the database name. This name is necessary to enable failover attempts by the data access provider. The database can be specified in the @provstr or @catalog parameter. Optionally, the connection string can also supply a failover partner name.

[ @catalog= ] 'catalog'

Is the catalog to be used when a connection is made to the OLE DB provider. catalog is sysname, with a default of NULL. catalog is passed as the DBPROP_INIT_CATALOG property to initialize the OLE DB provider. When the linked server is defined against an instance of SQL Server, catalog refers to the default database to which the linked server is mapped.

הוסף תגובה
facebook linkedin twitter email

כתיבת תגובה

האימייל לא יוצג באתר. שדות החובה מסומנים *

תגובה אחת

  1. GraliaExhaura09/05/2013 ב 03:53

    There are actually definitely a lot of details like that to take into consideration. Which is a great point to bring up. I offer the thoughts above as general inspiration but clearly you will find questions like the one you bring up exactly where the most essential factor will probably be working in honest beneficial faith. I don?t know if top practices have emerged around issues like that, but I'm sure that your job is clearly identified as a fair game. Both boys and girls feel the impact of just a moment's pleasure, for the rest of their lives.

    micheal korrs

    הגב