Menu
Simba Technologies
Simba Technologies

Simba ODBC Drivers
Quick Start Guide for Windows

Simba > Drivers > ODBC Quick Start Guide > Connecting to the Data Store > Using a SQL Server Linked Server

Using a SQL Server Linked Server

A linked server enables you to execute distributed queries against tables stored in a Microsoft SQL Server instance and another data store. Use the Microsoft SQL Server Management Studio to link your data store to a SQL Server instance and then execute distributed queries against both data stores.

You can use either of the following methods to create the linked server:

Important:

Make sure that the bitness of the driver that you are using matches the bitness of the SQL Server instance that you are using to create the server link. If you are creating the server link from a 32-bit SQL Server instance, then you need to use the 32-bit version of the driver. If you are creating the server link from a 64-bit SQL Server instance, then you need to use the 64-bit version of the driver. Contact your SQL Server administrator as needed to confirm the bitness of the server. For more information about diagnosing the issue, see Architecture Mismatch Problems.

Creating a Linked Server using the Object Explorer

You can use the options available in the user interface of the Management Studio to create your linked server.

To create a linked server using the Object Explorer:

  1. In Management Studio, connect to your Database Engine instance.
  2. In the Object Explorer, expand Server Objects, then right-click Linked Servers, and then click New Linked Server.
  3. In the New Linked Server dialog box, use the options on the General page to configure your linked server:
    1. In the Linked Server field, type a name for your linked server.
    2. Under Server Type, select Other Data Source.
    3. In the Provider drop-down list, select Microsoft OLE DB Provider for ODBC Drivers.
    4. In the Data Source field, type the name of your DSN.
  4. If your data store requires authentication, then use the options on the Security page to provide your credentials:
    1. Select Be Made Using this Security Context.
    2. In the Remote Login field, type your user name for accessing the data store.
    3. In the With Password field, type the password corresponding to the user name you specified above.
  5. Click OK to save your settings and link the data store to the SQL Server instance.

The linked server is displayed as a node under the Linked Servers branch in the Object Explorer, and you can expand the node to browse the contents of the server down to the table level. You can now start executing distributed queries to work with data from your data store and the SQL Server instance. For information about executing queries against the linked server, see Querying a Linked Server.

Creating a Linked Server using Stored Procedures

As an alternative to using the options in the user interface, you can execute stored procedures in Management Studio to create your linked server.

To create a linked server using stored procedures:

  1. In Management Studio, connect to your Database Engine instance.
  2. In the toolbar at the top of Management Studio, click New Query.
  3. In the editor window that appears, type the following statement for creating the linked server, where [LinkedServerName] is the name of the linked server, [DataStore] is the type of data store that you are linking to SQL Server, and [DSNName] is the name of your DSN for the data store:
  4. EXEC master.dbo.sp_addlinkedserver @server=N'[LinkedServerName]', @srvproduct=N'[DataStore]', @provider=N'MSDASQL', @datasrc=N'[DSNName]'

  5. If your data store requires authentication, then type the following statement, where [LinkedServerName] is the name of the linked server and [UserName] and [Password] are your credentials:
  6. EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'[LinkedServerName]', @useself=N'False', @locallogin=NULL, @rmtuser=N'[UserName]', @rmtpassword=N'[Password]'

  7. In the toolbar at the top, click Execute.
  8. The Messages pane opens and displays a message indicating whether the statements were executed successfully.

  9. In the Object Explorer, select Linked Servers and then click Refresh LinkedServer_Refresh.

The linked server appears as a node under the Linked Servers branch in the Object Explorer, and you can expand the node to browse the contents of the server down to the table level. You can now start executing distributed queries to work with the data from your data store and the SQL Server instance. For information about executing queries against the linked server, see Querying a Linked Server.

Note:

For information about the sp_addlinkedserver stored procedure, see "sp_addlinkedserver (Transact-SQL)" in the Transact-SQL Reference: https://msdn.microsoft.com/en-CA/library/ms190479.aspx. For information about the sp_addlinkedsrvlogin stored procedure, see "sp_addlinkedsrvlogin (Transact-SQL)" in the Transact-SQL Reference: https://msdn.microsoft.com/en-CA/library/ms189811.aspx.

Querying a Linked Server

Normally, you can query data by using scripts in Management Studio; however, scripts are not supported for linked servers. To execute queries against a linked server, use the editor.

To query a linked server:

  1. In the toolbar at the top of Management Studio, click New Query.
  2. In the editor window that appears, type your query.
  3. In the toolbar at the top, click Execute.

Data retrieved from your query is displayed in the Results pane.

Note:

For information about known issues that you might encounter while querying a linked server, see Known Issues When Using a SQL Server Linked Server.