Using sp_addlinkedserver

3 out of 16 rated this helpful - Rate this topic

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 using sp_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.
Transact-SQL Syntax Conventions

sp_addlinkedserver [ @server= ] 'server' [ , [ @srvproduct= ] 'product_name' ]

     [ , [ @provider= ] 'provider_name' ]

     [ , [ @datasrc= ] 'data_source' ]

     [ , [ @location= ] 'location' ]

     [ , [ @provstr= ] 'provider_string' ]

     [ , [ @catalog= ] 'catalog' ]

[ @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, and instancename is the name of the specific instance of SQL Server to which the user will be connected.

Note

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.

0 (success) or 1 (failure)

None.

The following table shows the ways that a linked server can be set up for data sources that can be accessed through OLE DB. A linked server can be set up more than one way for a particular data source; there can be more than one row for a data source type. This table also shows the sp_addlinkedserver parameter values to be used for setting up the linked server.

Remote OLE DB data source	OLE DB provider	product_name	provider_name	data_source	location	provider_string	catalog
SQL Server	Microsoft SQL Server Native Client OLE DB Provider	SQL Server1 (default)
SQL Server	Microsoft SQL Server Native Client OLE DB Provider		SQLNCLI	Network name of SQL Server (for default instance)			Database name (optional)
SQL Server	Microsoft SQL Server Native Client OLE DB Provider		SQLNCLI	servername\instancename (for specific instance)			Database name (optional)
Oracle, version 8 and later	Oracle Provider for OLE DB	Any	OraOLEDB.Oracle	Alias for the Oracle database
Access/Jet	Microsoft OLE DB Provider for Jet	Any	Microsoft.Jet.OLEDB.4.0	Full path of Jet database file
ODBC data source	Microsoft OLE DB Provider for ODBC	Any	MSDASQL	System DSN of ODBC data source
ODBC data source	Microsoft OLE DB Provider for ODBC	Any	MSDASQL			ODBC connection string
File system	Microsoft OLE DB Provider for Indexing Service	Any	MSIDXS	Indexing Service catalog name
Microsoft Excel Spreadsheet	Microsoft OLE DB Provider for Jet	Any	Microsoft.Jet.OLEDB.4.0	Full path of Excel file		Excel 5.0
IBM DB2 Database	Microsoft OLE DB Provider for DB2	Any	DB2OLEDB			See Microsoft OLE DB Provider for DB2 documentation.	Catalog name of DB2 database

1 This way of setting up a linked server forces the name of the linked server to be the same as the network name of the remote instance of SQL Server. Use data_source to specify the server.
2 "Any" indicates that the product name can be anything.
The Microsoft SQL Server Native Client OLE DB provider is the provider that is used with SQL Server if no provider name is specified or if SQL Server is specified as the product name. Even if you specify the older provider name, SQLOLEDB, it will be changed to SQLNCLI when persisted to the catalog.
The data_source, location, provider_string, and catalog parameters identify the database or databases the linked server points to. If any one of these parameters is NULL, the corresponding OLE DB initialization property is not set.
In a clustered environment, when you specify file names to point to OLE DB data sources, use the universal naming convention name (UNC) or a shared drive to specify the location.
sp_addlinkedserver cannot be executed within a user-defined transaction.

Security Note

When a linked server is created by using sp_addlinkedserver, a default self-mapping is added for all local logins. For non-SQL Server providers, SQL Server Authenticated logins may be able to gain access to the provider under the SQL Server service account. Administrators should consider using sp_droplinkedsrvlogin <linkedserver_name>, NULL to remove the global mapping.

Requires ALTER ANY LINKED SERVER permission.

A. Using the Microsoft SQL Server Native Client OLE DB Provider

The following example creates a linked server named SEATTLESales. The product name is SQL Server, and no provider name is used.

USE master;

GO

EXEC sp_addlinkedserver

   N'SEATTLESales',

   N'SQL Server';

GO

The following example creates a linked server S1_instance1 on an instance of SQL Server by using the SQL Server Native Client OLE DB provider.

EXEC sp_addlinkedserver

   @server=N'S1_instance1',

   @srvproduct=N'',

   @provider=N'SQLNCLI',

   @datasrc=N'S1\instance1';

B. Using the Microsoft OLE DB Provider for Microsoft Access

The Microsoft.Jet.OLEDB.4.0 provider connects to Microsoft Access databases that use the 2002-2003 format. The following example creates a linked server named SEATTLE Mktg.

Note

This example assumes that both Microsoft Access and the sample Northwind database are installed and that the Northwind database resides in C:\Msoffice\Access\Samples.

EXEC sp_addlinkedserver

   @server = N'SEATTLE Mktg',

   @provider = N'Microsoft.Jet.OLEDB.4.0',

   @srvproduct = N'OLE DB Provider for Jet',

   @datasrc = N'C:\MSOffice\Access\Samples\Northwind.mdb';

GO

The Microsoft.ACE.OLEDB.12.0 provider connects to Microsoft Access databases that use the 2007 format. The following example creates a linked server named SEATTLE Mktg.

Note

This example assumes that both Microsoft Access and the sample Northwind database are installed and that the Northwind database resides in C:\Msoffice\Access\Samples.

EXEC sp_addlinkedserver

   @server = N'SEATTLE Mktg',

   @provider = N'Microsoft.ACE.OLEDB.12.0',

   @srvproduct = N'OLE DB Provider for ACE',

   @datasrc = N'C:\MSOffice\Access\Samples\Northwind.accdb';

GO

C. Using the Microsoft OLE DB Provider for ODBC with the data_source parameter

The following example creates a linked server named SEATTLE Payroll that uses the Microsoft OLE DB Provider for ODBC (MSDASQL) and the data_source parameter.

Note

The specified ODBC data source name must be defined as System DSN in the server before you use the linked server.

EXEC sp_addlinkedserver

   @server = N'SEATTLE Payroll',

   @srvproduct = N'',

   @provider = N'MSDASQL',

   @datasrc = N'LocalServer';

GO

D. Using the Microsoft OLE DB Provider for Excel spreadsheet

To create a linked server definition using the Microsoft OLE DB Provider for Jet to access an Excel spreadsheet in the 1997 - 2003 format, first create a named range in Excel by specifying the columns and rows of the Excel worksheet to select. The name of the range can then be referenced as a table name in a distributed query.

EXEC sp_addlinkedserver 'ExcelSource',

   'Jet 4.0',

   'Microsoft.Jet.OLEDB.4.0',

   'c:\MyData\DistExcl.xls',

   NULL,

   'Excel 5.0';

GO

To access data from an Excel spreadsheet, associate a range of cells with a name. The following query can be used to access the specified named range SalesData as a table by using the linked server set up previously.

SELECT *

   FROM ExcelSource...SalesData;

GO

If SQL Server is running under a domain account that has access to a remote share, a UNC path can be used instead of a mapped drive.

EXEC sp_addlinkedserver 'ExcelShare',

   'Jet 4.0',

   'Microsoft.Jet.OLEDB.4.0',

   '\\MyServer\MyShare\Spreadsheets\DistExcl.xls',

   NULL,

   'Excel 5.0';

To connect to an Excel spreadsheet in the Excel 2007 format use the ACE provider.

EXEC sp_addlinkedserver @server = N'ExcelDataSource',

@srvproduct=N'ExcelData', @provider=N'Microsoft.ACE.OLEDB.12.0',

@datasrc=N'C:\DataFolder\People.xlsx',

@provstr=N'EXCEL 12.0' ;

E. Using the Microsoft OLE DB Provider for Jet to access a text file

The following example creates a linked server for directly accessing text files, without linking the files as tables in an Access .mdb file. The provider is Microsoft.Jet.OLEDB.4.0 and the provider string is Text.
The data source is the full path of the directory that contains the text files. A schema.ini file, which describes the structure of the text files, must exist in the same directory as the text files. For more information about how to create a Schema.ini file, see the Jet Database Engine documentation.

--Create a linked server.

EXEC sp_addlinkedserver txtsrv, N'Jet 4.0',

   N'Microsoft.Jet.OLEDB.4.0',

   N'c:\data\distqry',

   NULL,

   N'Text';

GO

--Set up login mappings.

EXEC sp_addlinkedsrvlogin txtsrv, FALSE, Admin, NULL;

GO

--List the tables in the linked server.

EXEC sp_tables_ex txtsrv;

GO

--Query one of the tables: file1#txt

--using a four-part name.

SELECT *

FROM txtsrv...[file1#txt];

F. Using the Microsoft OLE DB Provider for DB2

The following example creates a linked server named DB2 that uses the Microsoft OLE DB Provider for DB2.

EXEC sp_addlinkedserver

   @server=N'DB2',

   @srvproduct=N'Microsoft OLE DB Provider for DB2',

   @catalog=N'DB2',

   @provider=N'DB2OLEDB',

   @provstr=N'Initial Catalog=PUBS;

       Data Source=DB2;

       HostCCSID=1252;

       Network Address=XYZ;

       Network Port=50000;

       Package Collection=admin;

       Default Schema=admin;';

sp_addlinkedsrvlogin (Transact-SQL)

SQL Server 2012

8 out of 10 rated this helpful - Rate this topic

Creates or updates a mapping between a login on the local instance of SQL Server and a security account on a remote server.
Transact-SQL Syntax Conventions

sp_addlinkedsrvlogin [ @rmtsrvname = ] 'rmtsrvname'

     [ , [ @useself = ] 'TRUE' | 'FALSE' | NULL ]

     [ , [ @locallogin = ] 'locallogin' ]

     [ , [ @rmtuser = ] 'rmtuser' ]

     [ , [ @rmtpassword = ] 'rmtpassword' ]

[ @rmtsrvname = ] 'rmtsrvname'

Is the name of a linked server that the login mapping applies to. rmtsrvname is sysname, with no default.

[ @useself = ] 'TRUE' | 'FALSE' | 'NULL'

Determines whether to connect to rmtsrvname by impersonating local logins or explicitly submitting a login and password. The data type is varchar(8), with a default of TRUE.

A value of TRUE specifies that logins use their own credentials to connect to rmtsrvname, with the rmtuser and rmtpassword arguments being ignored. FALSE specifies that the rmtuser and rmtpassword arguments are used to connect to rmtsrvname for the specified locallogin. If rmtuser and rmtpassword are also set to NULL, no login or password is used to connect to the linked server.

[ @locallogin = ] 'locallogin'

Is a login on the local server. locallogin is sysname, with a default of NULL. NULL specifies that this entry applies to all local logins that connect to rmtsrvname. If not NULL, locallogin can be a SQL Server login or a Windows login. The Windows login must have been granted access to SQL Server either directly, or through its membership in a Windows group granted access.

[ @rmtuser = ] 'rmtuser'

Is the remote login used to connect to rmtsrvname when @useself is FALSE. When the remote server is an instance of SQL Server that does not use Windows Authentication, rmtuser is a SQL Server login. rmtuser is sysname, with a default of NULL.

[ @rmtpassword = ] 'rmtpassword'

Is the password associated with rmtuser. rmtpassword is sysname, with a default of NULL.

0 (success) or 1 (failure)

When a user logs on to the local server and executes a distributed query that accesses a table on the linked server, the local server must log on to the linked server on behalf of the user to access that table. Use sp_addlinkedsrvlogin to specify the login credentials that the local server uses to log on to the linked server.

Note

To create the best query plans when you are using a table on a linked server, the query processor must have data distribution statistics from the linked server. Users that have limited permissions on any columns of the table might not have sufficient permissions to obtain all the useful statistics, and might receive a less efficient query plan and experience poor performance. If the linked server is an instance of SQL Server, to obtain all available statistics, the user must own the table or be a member of the sysadmin fixed server role, the db_owner fixed database role, or the db_ddladmin fixed database role on the linked server. SQL Server 2012 SP1 modifies the permission restrictions for obtaining statistics and allows users with SELECT permission to access statistics available through DBCC SHOW_STATISTICS. For more information, see the Permissions section of DBCC SHOW_STATISTICS (Transact-SQL).

A default mapping between all logins on the local server and remote logins on the linked server is automatically created by executing sp_addlinkedserver. The default mapping states that SQL Server uses the user credentials of the local login when connecting to the linked server on behalf of the login. This is equivalent to executing sp_addlinkedsrvlogin with @useself set to true for the linked server, without specifying a local user name. Use sp_addlinkedsrvlogin only to change the default mapping or to add new mappings for specific local logins. To delete the default mapping or any other mapping, use sp_droplinkedsrvlogin.
Instead of having to use sp_addlinkedsrvlogin to create a predetermined login mapping, SQL Server can automatically use the Windows security credentials (Windows login name and password) of a user issuing the query to connect to a linked server when all the following conditions exist:

· A user is connected to SQL Server by using Windows Authentication Mode.

· Security account delegation is available on the client and sending server.

· The provider supports Windows Authentication Mode; for example, SQL Server running on Windows.

Note

Delegation does not have to be enabled for single-hop scenarios, but it is required for multiple-hop scenarios.

After the authentication has been performed by the linked server by using the mappings that are defined by executing sp_addlinkedsrvlogin on the local instance of SQL Server, the permissions on individual objects in the remote database are determined by the linked server, not the local server.
sp_addlinkedsrvlogin cannot be executed from within a user-defined transaction.

Requires ALTER ANY LOGIN permission on the server.

A. Connecting all local logins to the linked server by using their own user credentials

The following example creates a mapping to make sure that all logins to the local server connect through to the linked server Accounts by using their own user credentials.

EXEC sp_addlinkedsrvlogin 'Accounts';

EXEC sp_addlinkedsrvlogin 'Accounts', 'true';

Note

If there are explicit mappings created for individual logins, they take precedence over any global mappings that may exist for that linked server.

B. Connecting a specific login to the linked server by using different user credentials

The following example creates a mapping to make sure that the Windows user Domain\Mary connects through to the linked server Accounts by using the login MaryP and password d89q3w4u.

EXEC sp_addlinkedsrvlogin 'Accounts', 'false', 'Domain\Mary', 'MaryP', 'd89q3w4u';

Security Note

This example does not use Windows Authentication. Passwords will be transmitted unencrypted. Passwords may be visible in data source definitions and scripts that are saved to disk, in backups, and in log files. Never use an administrator password in this kind of connection. Consult your network administrator for security guidance specific to your environment.

sp_catalogs (Transact-SQL)

SQL Server 2012

This topic has not yet been rated - Rate this topic

Returns the list of catalogs in the specified linked server. This is equivalent to databases in SQL Server.

sp_catalogs [ @server_name = ] 'linked_svr'

[ @server_name =] 'linked_svr'

Is the name of a linked server. linked_svr is sysname, with no default.

Column name	Data type	Description
Catalog_name	nvarchar( 128)	Name of the catalog
Description	nvarchar( 4000)	Description of the catalog

Requires SELECT permission on the schema.

The following example returns catalog information for the linked server named OLE DB ODBC Linked Server #3.

Note

For sp_catalogs to provide useful information, the OLE DB ODBC Linked Server #3 must already exist.

USE master

EXEC sp_catalogs 'OLE DB ODBC Linked Server #3'

sp_column_privileges_ex (Transact-SQL)

SQL Server 2012

This topic has not yet been rated - Rate this topic

Returns column privileges for the specified table on the specified linked server.

sp_column_privileges_ex [ @table_server = ] 'table_server'

[ , [ @table_name = ] 'table_name' ]

[ , [ @table_schema = ] 'table_schema' ]

[ , [ @table_catalog = ] 'table_catalog' ]

[ , [ @column_name = ] 'column_name' ]

[ @table_server = ] 'table_server'

Is the name of the linked server for which to return information. table_server is sysname, with no default.

[ @table_name = ] 'table_name'

Is the name of the table that contains the specified column. table_name is sysname, with a default of NULL.

[ @table_schema = ] 'table_schema'

Is the table schema. table_schema is sysname, with a default of NULL.

[ @table_catalog = ] 'table_catalog'

Is the name of the database in which the specified table_name resides. table_catalog is sysname, with a default of NULL.

[ @column_name = ] 'column_name'

Is the name of the column for which to provide privilege information. column_name is sysname, with a default of NULL (all common).

The following table shows the result set columns. The results returned are ordered by TABLE_QUALIFIER, TABLE_OWNER, TABLE_NAME, COLUMN_NAME, and PRIVILEGE.

Column name	Data type	Description
TABLE_CAT	sysname	Table qualifier name. Various DBMS products support three-part naming for tables (qualifier.owner.name). In SQL Server, this column represents the database name. In some products, it represents the server name of the table's database environment. This field can be NULL.
TABLE_SCHEM	sysname	Table owner name. In SQL Server, this column represents the name of the database user who created the table. This field always returns a value.
TABLE_NAME	sysname	Table name. This field always returns a value.
COLUMN_NAME	sysname	Column name, for each column of the TABLE_NAME returned. This field always returns a value.
GRANTOR	sysname	Database user name that has granted permissions on this COLUMN_NAME to the listed GRANTEE. In SQL Server, this column is always the same as the TABLE_OWNER. This field always returns a value. The GRANTOR column can be either the database owner (TABLE_OWNER) or someone to whom the database owner granted permissions by using the WITH GRANT OPTION clause in the GRANT statement.
GRANTEE	sysname	Database user name that has been granted permissions on this COLUMN_NAME by the listed GRANTOR. This field always returns a value.
PRIVILEGE	varchar( 32)	One of the available column permissions. Column permissions can be one of the following values (or other values supported by the data source when implementation is defined): SELECT = GRANTEE can retrieve data for the columns. INSERT = GRANTEE can provide data for this column when new rows are inserted (by the GRANTEE) into the table. UPDATE = GRANTEE can modify existing data in the column. REFERENCES = GRANTEE can reference a column in a foreign table in a primary key/foreign key relationship. Primary key/foreign key relationships are defined with table constraints.
IS_GRANTABLE	varchar( 3)	Indicates whether the GRANTEE is permitted to grant permissions to other users (often referred to as "grant with grant" permission). Can be YES, NO, or NULL. An unknown, or NULL, value refers to a data source where "grant with grant" is not applicable.

Requires SELECT permission on the schema.

The following example returns column privilege information for the HumanResources.Department table in the AdventureWorks2012 database on the Seattle1 linked server.

EXEC sp_column_privileges_ex @table_server = 'Seattle1',

@table_name = 'Department',

@table_schema = 'HumanResources',

@table_catalog ='AdventureWorks2012';

sp_columns_ex (Transact-SQL)

SQL Server 2012

This topic has not yet been rated - Rate this topic

Returns the column information, one row per column, for the specified linked server tables. sp_columns_ex returns column information for only the specific column if column is specified.

sp_columns_ex [ @table_server = ] 'table_server'

[ , [ @table_name = ] 'table_name' ]

[ , [ @table_schema = ] 'table_schema' ]

[ , [ @table_catalog = ] 'table_catalog' ]

[ , [ @column_name = ] 'column' ]

[ , [ @ODBCVer = ] 'ODBCVer' ]

[ @table_server = ] 'table_server'

Is the name of the linked server for which to return column information. table_server is sysname, with no default.

[ @table_name = ] 'table_name'

Is the name of the table for which to return column information. table_name is sysname, with a default of NULL.

[ @table_schema = ] 'table_schema'

Is the schema name of the table for which to return column information. table_schema is sysname, with a default of NULL.

[ @table_catalog = ] 'table_catalog'

Is the catalog name of the table for which to return column information. table_catalog is sysname, with a default of NULL.

[ @column_name = ] 'column'

Is the name of the database column for which to provide information. column is sysname, with a default of NULL.

[ @ODBCVer = ] 'ODBCVer'

Is the version of ODBC that is being used. ODBCVer is int, with a default of 2. This indicates ODBC Version 2. Valid values are 2 or 3. For information about the behavior differences between versions 2 and 3, see the ODBC SQLColumns specification.

None

Column name	Data type	Description
TABLE_CAT	sysname	Table or view qualifier name. Various DBMS products support three-part naming for tables (qualifier.owner.name). In SQL Server this column represents the database name. In some products, it represents the server name of the table's database environment. This field can be NULL.
TABLE_SCHEM	sysname	Table or view owner name. In SQL Server, this column represents the name of the database user that created the table. This field always returns a value.
TABLE_NAME	sysname	Table or view name. This field always returns a value.
COLUMN_NAME	sysname	Column name, for each column of the TABLE_NAME returned. This field always returns a value.
DATA_TYPE	smallint	Integer value that correspond to ODBC type indicators. If this is a data type that cannot be mapped to an ODBC type, this value is NULL. The native data type name is returned in the TYPE_NAME column.
TYPE_NAME	varchar( 13)	String representing a data type. The underlying DBMS presents this data type name.
COLUMN_SIZE	int	Number of significant digits. The return value for the PRECISION column is in base 10.
BUFFER_LENGTH	int	Transfer size of the data.1
DECIMAL_DIGITS	smallint	Number of digits to the right of the decimal point.
NUM_PREC_RADIX	smallint	Is the base for numeric data types.
NULLABLE	smallint	Specifies nullability. 1 = NULL is possible. 0 = NOT NULL.
REMARKS	varchar( 254)	This field always returns NULL.
COLUMN_DEF	varchar( 254)	Default value of the column.
SQL_DATA_TYPE	smallint	Value of the SQL data type as it appears in the TYPE field of the descriptor. This column is the same as the DATA_TYPE column, except for the datetime and SQL-92 interval data types. This column always returns a value.
SQL_DATETIME_SUB	smallint	Subtype code for datetime and SQL-92 interval data types. For other data types, this column returns NULL.
CHAR_OCTET_LENGTH	int	Maximum length in bytes of a character or integer data type column. For all other data types, this column returns NULL.
ORDINAL_POSITION	int	Ordinal position of the column in the table. The first column in the table is 1. This column always returns a value.
IS_NULLABLE	varchar( 254)	Nullability of the column in the table. ISO rules are followed to determine nullability. An ISO SQL-compliant DBMS cannot return an empty string. YES = Column can include NULLS. NO = Column cannot include NULLS. This column returns a zero-length string if nullability is unknown. The value returned for this column is different from the value returned for the NULLABLE column.
SS_DATA_TYPE	tinyint	SQL Server data type, used by extended stored procedures.

For more information, see the Microsoft ODBC documentation.

sp_columns_ex is executed by querying the COLUMNS rowset of the IDBSchemaRowset interface of the OLE DB provider corresponding to table_server. The table_name, table_schema, table_catalog, and column parameters are passed to this interface to restrict the rows returned.

sp_columns_ex returns an empty result set if the OLE DB provider of the specified linked server does not support the COLUMNS rowset of the IDBSchemaRowset interface.

Requires SELECT permission on the schema.

sp_columns_ex follows the requirements for delimited identifiers. For more information, see Database Identifiers.

The following example returns the data type of the JobTitle column of the HumanResources.Employee table in the AdventureWorks2012 database on the linked server Seattle1.

EXEC sp_columns_ex 'Seattle1',

'Employee',

'HumanResources',

'AdventureWorks2012',

'JobTitle';

sp_droplinkedsrvlogin (Transact-SQL)

SQL Server 2012

Removes an existing mapping between a login on the local server running SQL Server and a login on the linked server.
Transact-SQL Syntax Conventions

sp_droplinkedsrvlogin [ @rmtsrvname= ] 'rmtsrvname' ,

   [ @locallogin= ] 'locallogin'

[ @rmtsrvname = ] 'rmtsrvname'

Is the name of a linked server that the SQL Server login mapping applies to. rmtsrvname is sysname, with no default. rmtsrvname must already exist.

[ @locallogin = ] 'locallogin'

Is the SQL Server login on the local server that has a mapping to the linked server rmtsrvname. locallogin is sysname, with no default. A mapping for locallogin to rmtsrvname must already exist. If NULL, the default mapping created by sp_addlinkedserver, which maps all logins on the local server to logins on the linked server, is deleted.

0 (success) or 1 (failure)

When the existing mapping for a login is deleted, the local server uses the default mapping created by sp_addlinkedserver when it connects to the linked server on behalf of that login. To change the default mapping, use sp_addlinkedsrvlogin.
If the default mapping is also deleted, only logins that have been explicitly given a login mapping to the linked server, by using sp_addlinkedsrvlogin, can access the linked server.
sp_droplinkedsrvlogin cannot be executed from within a user-defined transaction.

Requires ALTER ANY LOGIN permission on the server.

A. Removing the login mapping for an existing user

The following example removes the mapping for the login Mary from the local server to the linked server Accounts. Therefore, login Mary uses the default login mapping.

EXEC sp_droplinkedsrvlogin 'Accounts', 'Mary'

B. Removing the default login mapping

The following example removes the default login mapping originally created by executing sp_addlinkedserver on the linked server Accounts.

EXEC sp_droplinkedsrvlogin 'Accounts', NULL

sp_tables_ex (Transact-SQL)

SQL Server 2012

Returns table information about the tables from the specified linked server.

sp_tables_ex [ @table_server = ] 'table_server'

[ , [ @table_name = ] 'table_name' ]

[ , [ @table_schema = ] 'table_schema' ]

[ , [ @table_catalog = ] 'table_catalog' ]

[ , [ @table_type = ] 'table_type' ]

[ , [@fUsePattern = ] 'fUsePattern' ]

[ @table_server= ] 'table_server'

Is the name of the linked server for which to return table information. table_server is sysname, with no default.

[ , [ @table_name= ] 'table_name']

Is the name of the table for which to return data type information. table_nameis sysname, with a default of NULL.

[ @table_schema= ] 'table_schema']

Is the table schema. table_schemais sysname, with a default of NULL.

[ @table_catalog= ] 'table_catalog'

Is the name of the database in which the specified table_name resides. table_catalog is sysname, with a default of NULL.

[ @table_type= ] 'table_type'

Is the type of the table to return. table_type is sysname, with a default of NULL, and can have one of the following values.

Value	Description
ALIAS	Name of an alias.
GLOBAL TEMPORARY	Name of a temporary table available system wide.
LOCAL TEMPORARY	Name of a temporary table available only to the current job.
SYNONYM	Name of a synonym.
SYSTEM TABLE	Name of a system table.
SYSTEM VIEW	Name of a system view.
TABLE	Name of a user table.
VIEW	Name of a view.

[ @fUsePattern= ] 'fUsePattern'

Determines whether the characters _, %, [, and ] are interpreted as wildcard characters. Valid values are 0 (pattern matching is off) and 1 (pattern matching is on). fUsePattern is bit, with a default of 1.

None

Column name	Data type	Description
TABLE_CAT	sysname	Table qualifier name. Various DBMS products support three-part naming for tables (qualifier.owner.name). In SQL Server, this column represents the database name. In some other products, it represents the server name of the database environment of the table. This field can be NULL.
TABLE_SCHEM	sysname	Table owner name. In SQL Server, this column represents the name of the database user who created the table. This field always returns a value.
TABLE_NAME	sysname	Table name. This field always returns a value.
TABLE_TYPE	varchar(32)	Table, system table, or view.
REMARKS	varchar(254)	SQL Server does not return a value for this column.

sp_tables_ex is executed by querying the TABLES rowset of the IDBSchemaRowset interface of the OLE DB provider corresponding to table_server. The table_name, table_schema, table_catalog, and column parameters are passed to this interface to restrict the rows returned.

sp_tables_ex returns an empty result set if the OLE DB provider of the specified linked server does not support the TABLES rowset of the IDBSchemaRowset interface.

Requires SELECT permission on the schema.

The following example returns information about the tables that are contained in the HumanResources schema in the AdventureWorks2012 database on the LONDON2 linked server.

EXEC sp_tables_ex @table_server = 'LONDON2',

@table_catalog = 'AdventureWorks2012',

@table_schema = 'HumanResources',

@table_type = 'TABLE';

sp_linkedservers (Transact-SQL)

SQL Server 2012

Returns the list of linked servers defined in the local server.

sp_linkedservers

0 (success) or a nonzero number (failure)

Column name	Data type	Description
SRV_NAME	sysname	Name of the linked server.
SRV_PROVIDERNAME	nvarchar( 128)	Friendly name of the OLE DB provider managing access to the specified linked server.
SRV_PRODUCT	nvarchar( 128)	Product name of the linked server.
SRV_DATASOURCE	nvarchar( 4000)	OLE DB data source property corresponding to the specified linked server.
SRV_PROVIDERSTRING	nvarchar( 4000)	OLE DB provider string property corresponding to the linked server.
SRV_LOCATION	nvarchar( 4000)	OLE DB location property corresponding to the specified linked server.
SRV_CAT	sysname	OLE DB catalog property corresponding to the specified linked server.