programming4us
programming4us
DATABASE

SQL Server 2008 : Working with Multiple-Source Queries - Using Linked Servers

- How To Install Windows Server 2012 On VirtualBox
- How To Bypass Torrent Connection Blocking By Your ISP
- How To Install Actual Facebook App On Kindle Fire
1/20/2011 3:56:23 PM
Linked Servers enable your SQL Server instance to execute commands against OLE DB or ODBC compatible data sources. Linked Servers can be used in the following scenarios.
  • Accessing data from heterogeneous data sources like MS Access, Oracle, Sybase, and MySQL

  • From Linked Servers, having the ability to issue distributed queries, updates, commands, and transactions on heterogeneous data sources

Head of the Class...: What Is the Use of Linked Servers in SQL Server Integration Services (SSIS)?

Users will often raise questions about user privileges you need to manage in order to add or drop Linked Servers. As Linked Servers are added to the SQL Server, you need to have server roles. As is obvious, the sysadmin server role can add or drop Linked Servers because sysadmin can do everything in the server, whereas the setupadmin server role is tailored to adding and dropping Linked Servers.

Users also often face the dilemma of when to use Linked Servers in SSIS as both options are supporting heterogeneous databases. As this is not a place to discuss features and facilities of SSIS, we will simply discuss where Linked Servers can be used.

Linked Servers can be used to update the remote server within your application. For example, if you need to update a remote server table from a trigger where the trigger is fired after an insert to the SQL Server table, Linked Server is the option.

You can use Linked Servers in your Web application when you need to access a remote server other than SSIS.

Also, if you want to analyze data in a remote server, most professionals prefer to use the Linked Server option because it will give you the luxury of tweaking data, group, Union, and so on, the way you want.


How a Linked Server Works

When a client application executes a distributed query via a Linked Server, SQL Server sends requests to OLE DB. The rowset request may be in the form of executing a query against the provider or opening a base table from the provider.

For a data source to return data through a Linked Server, the OLE DB provider (DLL) for that data source must be present on the same server as the instance of SQL Server.

When a third-party OLE DB provider is used, the account under which the SQL Server service runs must have read and execute permissions for the directory and for all subdirectories in which the provider is installed.

Configuring a Linked Server

You can configure Linked Servers in two different ways. The easiest way is to use SQL Server Management Studio because it provides you with the relevant screen to enter Linked Server parameters.

Here is a list of the parameters you need to configure Linked Servers in Figure 1.

Figure 1. General Page of New Linked Server

  • Provider Select an OLE DB data source from the available list box. The OLE DB provider is registered with the given PROGID in the registry.

  • Product name The product name of the OLE DB data source to add as a Linked Server. This should be a nonempty value.

  • Data source Name of the data source as interpreted by the OLE DB provider. In case of MS Access, Data Source is the.mdb file, and in Excel it is the Excel file.

  • Provider string Unique programmatic identifier (PROGID) of the OLE DB provider that corresponds to the data source. Table 1 lists the parameters needed for different databases.

Table 1. Parameters Needed for Databases
Remote OLE DB Data SourceOLE DB ProviderProvider_NameData_SourceProvider String
SQL ServerMicrosoft SQL Native Client OLE DB ProviderSQLNCISQL Server Instance NameDatabase Name
OracleMicrosoft OLE DB Provider for OracleMSDAORASQL*Net alias for Oracle 
Oracle, version 8 and later for the Oracle databaseOracle Provider for OLE DBOraOLEDB. OracleOracle database Alias 
MS AccessMicrosoft OLE DB Provider for JetMicrosoft.Jet. OLEDB.4.0Full path of MS Access file 
ODBC data sourceMicrosoft OLE DB Provider for ODBCMSDASQLSystem DSN of ODBC data sourceODBC connection string (If no DSN available)
File systemMicrosoft OLE DB Provider for Indexing ServiceMSIDXSIndexing Service catalog name 
ExcelMicrosoft OLE DB Provider for JetMicrosoft.Jet. OLEDB.4.0Full path of Excel fileExcel 5
IBM DB2 DatabaseMicrosoft OLE DB Provider for DB2DB2OLEDB DB2 database Catalog name

Security is a very important configuration in Link Servers. As the remote server is a different server, there is a probability that users in the SQL Server server do not exist in the remote server. Therefore, you need to map users between SQL Server and the remote server. Figure 2 shows the Security tab of the Linked Server.

Figure 2. Security Tab of Configuring Link Server

The login map will either pass the user along without transiting the login name if the impersonate option is checked or translate any user’s login to a remote login and password if the impersonate option is not checked. On the external server, the login must be a valid login and must have been granted security rights in order for the link to be effective.

The default connection options for a user not mapped are as follows:

  • Not be made This option restricts the ability to run distributed queries to those users in the user mapping list. If a user not on the user mapping list attempts to run a distributed query, he will receive an error.

  • Be made without using a security context This option is for non-SQL Server external data sources and is not useful for SQL Server. SQL Server will attempt to connect as the user SQL without the password. If a user not on the server mapping list attempts to run the distributed query, he will return an error.

  • Be made using the logins current security context When the local SQL server connects to the external server, it can delegate security, meaning that the local SQL server will connect to the local users’ login of the SQL server. Using this method is similar to listing the user and selecting the impersonate option except that this uses security delegation, and to pass the security context, the login must be the exact same account and not just the same login and password.

  • Be made using the security context This option simply assigns every nonmapped local user to a hardcoded external SQL Server login. Although this may be the simplest method, it allows every local user the same access to external SQL Server. Using this option should not violate any responsible security regulations.

Server options can be set, but most of the time you can get away with default settings. Here is a list of these default settings:

  • Collation Compatibility Set this option to true if the two servers are using the same collation.

  • Data Access If this option is set to false, it disables distributed queries to the external server.

  • RPC If this option is set to true, remote procedure calls may be made to the external server.

  • RPC Out If this option is set to true, remote procedure calls may be made from the external server.

  • Use Remote Collation If this option is set to true, distributed queries will use the collation of the external SQL server rather than that of the local server.

  • Collation Name This option specifies a collation for distributed queries. It cannot be chosen if collation compatibility is set.

  • Connection Time-out The connection timeout is in milliseconds.

  • Query Time-out The distributed query timeout is in milliseconds.

Exercise 1 will take you through the steps to create Linked Servers using SQL Server Management Studio. In this exercise, we will discuss how to create a Linked Server for Microsoft Access mdb.

Exercise 1. Creating Linked Servers Using SQL Server Management Studio
  1. Start SQL Server Management Studio from Program Files| SQL Server 2008.

  2. Select Database Engine as Database Type and the SQL Server Database instance name and provide the necessary user credentials to log in to SQL Server. If you supply the correct information, you will be taken to the SQL Server Management Studio.

  3. If the Object Explorer is not visible, select the Object Explorer menu option from the View menu or press F8.

  4. Expand Server Object in the Object Explorer and select Linked Servers. Right-click Linked Servers and select New Linked Server as shown in Figure 3.

    Figure 3. New Linked Server Option in Object Explorer

  5. After selecting the name of the option, you will be taken to Figure 9.1, which is the general page of the Linked Server configuration.

  6. Specify a Linked Server Name, which is the name of the Linked Server used when accessing the server.

  7. Select the Other data source option.

  8. Select Microsoft Jet OLEDB 4.0 Provider from the provider drop-down list.

  9. Enter any text for the product name. Data Source should be the file path to the Microsoft Access.mdb file.

  10. Leave the other two tabs with default values.

  11. Click OK to confirm the changes.

You can drop the Linked Servers if you follow the steps in Exercise 2.

Exercise 2. Dropping a Linked Server in SQL Server Management Studio
  1. Start SQL Server Management Studio from Program Files | SQL Server 2008.

  2. Select Database Engine as Database Type and the SQL Server Database instance name and provide the necessary user credentials to log in to SQL Server. If you supply the correct information, you will be taken to SQL Server Management Studio.

  3. If the Object Explorer is not visible, select the Object Explorer menu option from the View menu or press F8.

  4. Expand Server Object in the Object Explorer and select Linked Servers. Right-click the Linked Server you want to delete and select Delete.

  5. Press OK to confirm the deletion of the Linked Server.

Similarly, you can create or drop Linked Servers using T-SQL scripts. Exercise 3 explains how to get the template of the Linked Server.

Exercise 3. Opening Template for Linked Server in SQL Server Management Studio
  1. Start SQL Server Management Studio from Program Files | SQL Server 2008.

  2. Select Database Engine as Database Type and the SQL Server Database instance name and provide the necessary user credentials to log in to SQL Server. If you supply the correct information, you will be taken to SQL Server Management Studio.

  3. If the Template Explorer is not visible, select the Template Explorer menu option from the View menu or press CTRL+ALT+T.

  4. In the Template Explorer, navigate to the Linked Server as seen in Figure 4.

    Figure 4. Linked Server in Template Explorer

    The Add Linked Server Access MDB template has the template to add Linked Server of MS Access MDB file to the SQL Server. Add Linked Server Simple template has the template for To add a Link Server. Drop Linked Server template has the template to drop a Linked Server.

  5. Double-click the required template from the Template Explorer to enter the relevant parameters to suit your environment.

  6. Execute the script by clicking the Execute button.

Apart from creating Linked Servers from SQL Server Management Studio, you have the option of creating them using T-SQL in Exercise 4.

Exercise 4. Creating Linked Servers Using T-SQL
  1. Start SQL Server Management Studio from Program Files | SQL Server 2008.

  2. Select Database Engine as Database Type and the SQL Server Database instance name and provide the necessary user credentials to log in to SQL Server. If you supply the correct information, you will be taken to SQL Server Management Studio.

  3. Select the New Query button, which is most probably below the main menu. You will have a fresh query screen.

  4. The following query will create a Linked Server named CUSTOMERACCESS, which is linked to Customer.MDB MS Access.

    -- ========================================
    -- Add Linked Server Access MDB
    -- ========================================
    EXEC sp_addlinkedserver
    @server = N'CUSTOMERACCES',
    @provider = N'Microsoft.Jet.OLEDB.4.0',
    @srvproduct = N'OLE DB Provider for Jet',
    @datasrc = N'C:\LinkSrv\CUSTOMER.MDB'
    GO
  5. The following query will set up login mapping between SQL Server and MS Access.

    -- Set up login mapping using current user's security context
    EXEC sp_addlinkedsrvlogin
    @rmtsrvname = N'CUSTOMERACCES',
    @useself = N'TRUE',
    @locallogin = NULL,
    @rmtuser = N'dinesh', --Remote User Name
    @rmtpassword = N'pa$$w0rd' –Remote user password
    GO
  6. After creating the Linked Server, you can verify by listing the tables in the Linked Server.

    EXEC sp_tables_ex N'CUSTOMERACCESS'
    GO

You have the option of dropping the Linked Server using T-SQL as shown in Exercise 5.

Exercise 5. Droping a Linked Server Using T-Sql
  1. Start SQL Server Management Studio from Program Files| SQL Server 2008.

  2. Select Database Engine as Database Type and the SQL Server Database instance name and provide the necessary user credentials to log in to SQL Server. If you supply the correct information, you will be taken to SQL Server Management Studio.

  3. Select the New Query button, which is most probably below the main menu. You will have a fresh query screen.

  4. The following query will drop the Link Server from SQL Server.

    -- ==============================
    -- Drop Linked Server template
    -- ==============================
    -- Drops a linked server reference to a Database Engine instance
    -- Related logins will also be dropped in this example
    EXEC master.dbo.sp_dropserver @server=N'CUSTOMERACESS',
    @droplogins='droplogins'
    GO

Provider Options

In addition to configuring Linked Server, you can also configure various options for each provider. These options are applied to all Linked Servers configured from the given provider. You can configure providers through the following steps in Exercise 6.

Exercise 6. Configuring Provider Options
  1. Start SQL Server Management Studio from Program Files| SQL Server 2008.

  2. Select Database Engine as Database Type and the SQL Server Database instance name and provide the necessary user credentials to log in to SQL Server. If you supply the correct information, you will be taken to SQL Server Management Studio.

  3. If the Object Explorer is not visible, select the Object Explorer menu option from the View menu or press F8.

  4. Expand Server Object in the Object Explorer and expand to Providers in the Linked Servers further as shown in Figure 5.

    Figure 5. List of Providers

    Double-click the Provider on which you want to set the configuration options and you will see Figure 9.6.

    Figure 9.6. Provider Options

6. You can select the option from the list.

7. Also, you can see the configured Linked Server using this provider.

Other  
 
Top 10
- Microsoft Visio 2013 : Adding Structure to Your Diagrams - Finding containers and lists in Visio (part 2) - Wireframes,Legends
- Microsoft Visio 2013 : Adding Structure to Your Diagrams - Finding containers and lists in Visio (part 1) - Swimlanes
- Microsoft Visio 2013 : Adding Structure to Your Diagrams - Formatting and sizing lists
- Microsoft Visio 2013 : Adding Structure to Your Diagrams - Adding shapes to lists
- Microsoft Visio 2013 : Adding Structure to Your Diagrams - Sizing containers
- Microsoft Access 2010 : Control Properties and Why to Use Them (part 3) - The Other Properties of a Control
- Microsoft Access 2010 : Control Properties and Why to Use Them (part 2) - The Data Properties of a Control
- Microsoft Access 2010 : Control Properties and Why to Use Them (part 1) - The Format Properties of a Control
- Microsoft Access 2010 : Form Properties and Why Should You Use Them - Working with the Properties Window
- Microsoft Visio 2013 : Using the Organization Chart Wizard with new data
REVIEW
- First look: Apple Watch

- 3 Tips for Maintaining Your Cell Phone Battery (part 1)

- 3 Tips for Maintaining Your Cell Phone Battery (part 2)
programming4us programming4us
programming4us
 
 
programming4us