Introduction

In situations where you need access to remote servers or, run queries on servers which are distributed linking servers provides a good solution. This article explains how to set up a Postgres linked server on SQL Server 2008. Querying a table on the linked server is also described. Configuring the linked server to support remote procedural calls that enables executing queries on the linked server is also described with an example.

MS SQL servers always provided remote access to servers through RPC mechanisms, but they had the limitation of being confined to invoking procedures on remote locations. A linked server (a virtual server) may be considered a more flexible way of achieving the same thing, with the added benefits of remote table access and distributed queries. Microsoft manages the link mechanism using OLE DB technology. An OLE DB datasource points to the specific database that can be accessed using OLEDB.

The Postgres EnterpriseDB Server

The Postgres EnterpriseDB Server is described in my previous articles. EnterpriseDB has its own management console, Postgres Studio. If you are new to EnterpriseDB, I recommend that you read my article on the Packt site which completely describes how to install it on your machine.

We will be creating a linked server that can be accessed on SQL Server 2008. The next figure shows the PGNorthwind database, a copy of Microsoft's well known Northwind database in the Postgres Studio console. This database was created using the migration tool, also from EnterpriseDB (the Migration Studio). The migration process is completely explained in this article.

Figure 1: Postgres Studio 8.3

Using the SQL Server 2008 Management Studio to set up a linked server

Both Microsoft SQL Server Management Studio as well as stored procedures can be used to set up a linked server. In order to achieve this linking we need the support of OLE DB provider as mentioned earlier.

OLE DB Provider for ODBC

In the list of OLE DB providers, you will see in this section an OLE DB provider for ODBC. When you install EnterpriseDB you will also be bringing in ODBC connectivity provided by the EnterpriseDB driver. As explained in my article on this site, you need to create an ODBC data source that can establish the linking. A System DSN source (System Specific), Linked_EnterpriseDB created along the same lines as described in the article is shown in the next figure. System DSN stays in the Windows Registry and it is one of the fastest connections requiring just the DSN name you provided.

Figure 2: System DSN for EnterpriseDB

Creating a linked server

Open the SQL Server 2008 Management Studio. Expand the Server Objects node, highlight the Linked Servers node and right click on the mouse, you can add a linked server by choosing the New Linked Server... ellipsis link as shown.

Figure 3: Linked Servers in SQL Server Management Studio

This opens the New Linked Server… window which is all empty except for the default provider, MediaCatalogDB OLEDB Provider. There are other OE DB providers as you can see from this partial list displayed in the drop-down list box.

Figure 4: OLE DB Providers for Linked Servers

The very first thing to do is to provide a Name for the linked server in the Select a page/General tab of New Linked Server. Herein it is named Linked_EDB. You need to fill in other information as shown in this filled form of the New Linked Server window.

Note that the page is already saved to reflect the Name. As explained earlier, the Provider is the OLE DB Provider for ODBC which you must select from the drop-down. The data source is the System DSN you created earlier. The catalog or the database on the server that will be linked is the PGNorthwind mentioned in the beginning. LINKED_EDB is a custom name I provided. The provider string is completed by the wizard using the information you provided in the rest of the wizard items such as Linked Server name; provider, product name; data source and catalog.

Figure 5: Properties of LINKED_EDB – General page

You click on the Security navigational link on the left side of the New Linked Server. You are required to make a mapping between local and remote servers and the default option shown in the figure is chosen (the computer owner is also the administrator). Note that there are other login options.

Figure 6: Properties of LINKED_EDB: Security page

Click the OK button on the window. Click on the Server Options navigational link on the left. This brings up the window as shown in the next figure. Do not make any changes to the element values in this window. But if you need, you should make changes to some of the attributes such as Distributor, Publisher and Subscriber.

Figure 7: Properties of LINKED_EDB - Server Options

Click on the OK button in the above screen. The linked server gets added to the list of linked servers.

Reviewing Linked Server in Management Studio

Go back to the SQL Server Management Studio and open the Object explorer to review the Linked_EDB as shown in the next figure. If necessary (that is, if you don't see the Linked Server) refresh the node objects.

Figure 8: LINKED_EDB Server in SQL Server Management Studio

The public.ADO.NET Destination table in the linked server was a table created in an earlier project using the SQL Server Integration Services. The categories table was migrated using the Migration Studio. The sys prefixed tables are Postgres tables.

Querying the linked server

It is easy to find how the linked server is configured using system stored procedures on the SQL Server 2008. Open a Query window from File | New |Query Current Connection to open the query window and type in the following exec procedure statement. The next figure shows the procedure as well as the returned values. This shows all servers both local and remote.

Figure 9: Stored Procedure sp_helplinkedsrvrlogin

For the Linked_EDB server you can pass an argument to the query to find more information as shown in the next figure.

Figure 10: Login for the remote server

One of the great new features of SQL Server 2008 is IntelliSense support. Make full use of this important productive feature shown in the next figure while executing the above procedure.

Figure 11: IntelliSense support in SQL Server 2008

Reviewing Table information on the Linked Server

The linked server shows the tables but will not expand out the columns for you. Table and View details may also be obtained by running the stored procedure sp_tables_ex. Note that only a couple of rows from the 58 rows returned from this procedure are shown in the next figure.

Figure 12: The stored procedure sp_tables_ex

Running queries on the linked server

Table data in a linked server can be queried using the openquery() function. The syntax for this function shown next is very simple.

The next figure shows the result of running the openquery() function on the Linked_EDB linked server (only 10 rows from the 63 rows are shown).

Figure 13: Openquery () function

Configuring the linked server for Remote Procedural Calls (RPC)

Support for remote procedural calls is not configured by default. If the linked server is not configured for RPC, then running a query on the remote server shown in the following figure would not be possible.

Figure 14: Linked Server not configured for RPC

However you can tweak the properties of the linked server as shown in the next figure which will then support RPC. You should set both the RPC and RPC Out to True. This can be configured in the Server Option’s page of the Linked Server’s page.

Figure 15: Enabling RPC for the Linked Server

When the above changes are applied by clicking OK, you will be able to run and execute remotely on the linked server as shown in the next figure.

Figure 16: Execting SQL Statement on the remote server

Summary

The article described in detail setting up a linked EnterpriseDB server on SQL Server 2008. Methods to query the linked server as well as executing statements remotely on the linked server were also described.

About Jayaram Krishnaswamy

Sorry, no bio is available This author has published 7 articles on DotNetSlackers. View other articles or the complete profile here.