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 DynamoDB 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" in Troubleshooting.

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. For example, type Simba DynamoDB ODBC DSN.
  4. Click OK to save your settings and link the DynamoDB 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 the DynamoDB 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:

    4. EXEC master.dbo.sp_addlinkedserver @server=N'[LinkedServerName]', @srvproduct=N'DYNAMODB', @provider=N'MSDASQL', @datasrc=N'Simba DynamoDB ODBC DSN'

  4. In the toolbar at the top, click Execute.

    5. The Messages pane opens and displays a message indicating whether the statements were executed successfully.

  5. 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 the DynamoDB 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 Linked Server" in Troubleshooting.

Related topics