Counter of Festivals

Ashok Blog for SQL Learners and Beginners and Experts

Thursday, 18 April 2013

How to Add a Linked Server




Adding a Linked server can be done by either using the GUI interface or the sp_addlinkedserver command.
Adding a linked Server using the GUI
There are two ways to add another SQL Server as a linked server.  Using the first method, you need to specify the actual server name as the “linked server name”.  What this means is that everytime you want to reference the linked server in code, you will use the remote server’s name.  This may not be beneficial because if the linked server’s name changes, then you will have to also change all the code that references the linked server.  I like to avoid this method even though it is easier to initially setup.  The rest of the steps will guide you through setting up a linked server with a custom name:
To add a linked server using SSMS (SQL Server Management Studio), open the server you want to create a link from in object explorer.
  1. In SSMS, Expand Server Objects -> Linked Servers -> (Right click on the Linked Server Folder and select “New Linked Server”)
Add New Linked Server

  1. The “New Linked Server” Dialog appears.  (see below).
Linked Server Settings
  1. For “Server Type” make sure “Other Data Source” is selected.  (The SQL Server option will force you to specify the literal SQL Server Name)
  2. Type in a friendly name that describes your linked server (without spaces). I use AccountingServer.
  3. Provider – Select “Microsoft OLE DB Provider for SQL Server”
  4. Product Name – type: SQLSERVER (with no spaces)
  5. Datasource – type the actual server name, and instance name using this convention: SERVERNAMEINSTANCENAME
  6. ProviderString – Blank
  7. Catalog – Optional (If entered use the default database you will be using)
  8. Prior to exiting, continue to the next section (defining security)
Define the Linked Server Security
Linked server security can be defined a few different ways. The different security methods are outlined below.  The first three options are the most common:
Option Name
Description
Be made using the login’s current security context
Most Secure. Uses integrated authentication, specifically Kerberos delegation to pass the credentials of the current login executing the request to the linked server. The remote server must also have the login defined. This requires establishing Kerberos Constrained Delegation in Active Directory, unless the linked server is another instance on the same Server.  If instance is on the same server and the logins have the appropriate permissions, I recommend this one.
Be made using this security context
Less Secure. Uses SQL Server Authentication to log in to the linked server. The credentials are used every time a call is made.
Local server login to remote server login mappings
You can specify multiple SQL Server logins to use based upon the context of the user that is making the call.  So if you have George executing a select statement, you can have him execute as a different user’s login when linking to the linked server.  This will allow you to not need to define “George” on the linked server.
Not be made
If a mapping is not defined, and/or the local login does not have a mapping, do not connect to the linked server.
Be made without using a security context
Connect to the server without any credentials.  I do not see a use for this unless you have security defined as public.
  1. Within the same Dialog on the left menu under “Select a Page”, select Security
  2. Enter the security option of your choice.
Linked Server Security Settings
  1. Click OK, and the new linked server is created
4.      The system stored procedure sp_addlinkedserver is used in order to link a server from the server in which you are executing the command. In order to connect to a remote SQL Server, you basically only need 3 parameters.

Using sp_addlinkedserver


EXEC sp_addlinkedserver  
   @server=N'HOGEN-PC',             -- Remote Computer Name
   @srvproduct=N'',                 -- Not Needed
   @provider=N'SQLNCLI',            -- SQL Server Driver
   @datasrc=N'HOGEN-PCSQLEXPRESS';  -- Server Name And Instance

If the remote SQL Server does not have an instance name, then the @datasrc parameter nee only contain the remote server name and not the instance.

How to Config Linked Servers in a Minute

 

Introduction - How To Setup Linked Servers

If you want to use a database from another Server Instance in your queries, you should do some workaround to reach your goal, or better say if you have distributed databases for an application and you want to use distributed queries here is the simple, easy solution.

Background

Googling around would get you to this solution but it's important to know how much time you would spend on something. On this issue, it was so time consuming to find out how to really config the server to get it working, so now it's not.

Using the Code

This option is also available for SQL Server 2000. You should go to [Security]>[Linked Servers] tab under the connected SQL Server Instance.

I. Setting up Linked Server for Using as a NamedServer

  1. Connect to the specified DB Instance that you are going to use the Shared Server (Linked Server) in it.
1.JPG
  1. Go to [Server Objects] and on the [Linked Servers] perform a [Right-Click] and select [New Linked Server...].
  2. In the new window on [General] page, you have to set several parameters as below:
2.JPG
    1. [Linked server]: LLSS (The name that will be used for addressing the Shared server)
http://www.codeproject.com/images/minus.gifCollapse | Copy Code
SELECT * FROM [LLSS].[DBName].[Schema].[TableName] 
 
SELECT * FROM [SERVER134].[CompanyDB].[dbo].[Employee] 
    1. [Server type]: Select [Other data source] Option
    2. [Provider]: [Choose SQL Native Client]
    3. [Product Name]: ZZZZZZZZZ (Shouldn't be empty and anything can be set, e.g. Instance Name)
http://www.codeproject.com/images/minus.gifCollapse | Copy Code
[ZZZZZZZZZ]
 
[Server2005]
    1. [Data source]: XXX.XXX.XXX.XXX\DDSS (The Network name of SQL Server that is going to be shared on current Instance).
http://www.codeproject.com/images/minus.gifCollapse | Copy Code
[ XXX.XXX.XXX.XXX\DDSS]
 
[192.168.100.134\Server2005]
    1. [Provider string]: (leave this parameter empty)
    2. [Location]: is disabled (leave this parameter empty)
    3. [Catalog]: the database Name (set your default database or leave it empty)
  1. Go to [Security] page, select [be made using this security context] option and set parameters as below:
3.JPG
    1. [Remote login]: XX (the shared server login user name)
http://www.codeproject.com/images/minus.gifCollapse | Copy Code
[XX] 
 
[sa] 
    1. [With Password]: YY (the shared server login password)
http://www.codeproject.com/images/minus.gifCollapse | Copy Code
[YY]
 
[MyS@P@ss ]
  1. Press [OK ] and you are ready to go … and use the Linked Server as mentioned in part 3.1.

II. Setting up Linked Server for Using as a NetworkName

  1. Connect to specified DB Instance that you are going to use the Shared Server (Linked Server) in it.
  2. Go to [Server Objects] and on the [Linked Servers] perform a [Right-Click] and select [New Linked Server...].
  3. In the new window, you have to set several parameters as below:
    1. [ Linked server] : XXX.XXX.XXX.XXX\DDSS (The NetworkName of Shared Server that will be used for addressing the Shared server).
http://www.codeproject.com/images/minus.gifCollapse | Copy Code
SELECT * FROM [XXX.XXX.XXX.XXX\DDSS].[DBName].[Schema].[TableName]
 
SELECT * FROM [192.168.100.134\SERVER2005].[ CompanyDB].[dbo].[Employee]
    1. [Server type] : Select [SQL Server] Option
  1. Go to [Security] page, select [be made using this security context] option and set parameters as below:
    1. [Remote login]: XX (the shared server login user name)
http://www.codeproject.com/images/minus.gifCollapse | Copy Code
[XX] 
 
[sa] 
    1. [With Password]: YY (the shared server login password)
http://www.codeproject.com/images/minus.gifCollapse | Copy Code
[YY]
 
[MyS@P@ss ]
  1. Press [OK ] and you are ready to go … and use the Linked Server as mentioned in part 3.1.

sp_addlinkedserver (Transact-SQL)

SQL Server 2012
http://i3.msdn.microsoft.com/Areas/Epx/Content/Images/ImageSprite.png
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.
Topic link iconTransact-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.
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.

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 noteSecurity 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.
NoteNote
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.
NoteNote
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.
NoteNote
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
http://i3.msdn.microsoft.com/Areas/Epx/Content/Images/ImageSprite.png
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.
Topic link iconTransact-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.
NoteNote
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.
NoteNote
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';
Or
EXEC sp_addlinkedsrvlogin 'Accounts', 'true';
NoteNote
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 noteSecurity 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
http://i3.msdn.microsoft.com/Areas/Epx/Content/Images/ImageSprite.png
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.
NoteNote
For sp_catalogs to provide useful information, the OLE DB ODBC Linked Server #3 must already exist.
USE master
GO
EXEC sp_catalogs 'OLE DB ODBC Linked Server #3'
sp_column_privileges_ex (Transact-SQL)
SQL Server 2012
http://i3.msdn.microsoft.com/Areas/Epx/Content/Images/ImageSprite.png
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.
Topic link iconTransact-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 rmtsrvnamelocallogin 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.


Requires SELECT permission on the schema.