The world of databases is constantly expanding, and as a database professional, it’s important to have knowledge and experience with various database products. In addition to SQL Server, I have had the opportunity to work with databases like MySQL and PostgreSQL. Integrating different database products and making them work seamlessly can be an interesting challenge.

One question that I frequently receive is how to create linked servers in SQL Server to connect with PostgreSQL. While this may seem like a simple task, I wanted to provide a step-by-step guide to help you achieve this. In this blog post, I will walk you through the process of creating a linked server using the PGOLEDB provider.

Create a Database and Table in PostgreSQL

First, let’s create a database in PostgreSQL. Run the following SQL statement:

CREATE DATABASE SQLAuthority WITH OWNER = postgres ENCODING = 'UTF8';

Once the database is created, we can proceed to create a table and insert some data:

CREATE TABLE MyTable 
( 
ID integer NOT NULL, 
Name varchar(128) NOT NULL
);

INSERT INTO MyTable VALUES (1, 'Pinal Dave');

Verify that the data has been successfully inserted into the table:

SELECT * FROM MyTable;

Create a Linked Server in SQL Server

Now, let’s move on to creating the linked server in SQL Server. Follow these steps:

1. Go to the PGNP website and choose “Download” from the menu bar.
2. On the download page, select “PGNP OLEDB Providers for Postgres, Greenplum and Redshift” and install it.
3. In SQL Server Management Studio (SSMS), modify and run the following script:

-- Change parameter for provider Allow In Process = true / Dynamic Parameters = true
EXEC MASTER.dbo.sp_MSset_oledb_prop N'PGNP', N'AllowInProcess', 1;
EXEC MASTER.dbo.sp_MSset_oledb_prop N'PGNP', N'DynamicParameters', 1;

DECLARE @name NVARCHAR(4000);
DECLARE @provider NVARCHAR(4000);
DECLARE @servername NVARCHAR(4000);
DECLARE @port NVARCHAR(4000);
DECLARE @db_name NVARCHAR(4000);

-- Destination PostgreSQL database
SET @name = N'SQLAuth_PG';
SET @provider = N'PGNP';
SET @servername = N'localhost';
SET @port = 'PORT=5432;';
SET @db_name = N'sqlauthority';

-- Create linked server
EXEC MASTER.dbo.sp_addlinkedserver @server = @name, @srvproduct = N'PGNP', @provider = @provider, @datasrc = @servername, @provstr = @port, @catalog = @db_name;

-- Username and password for PostgreSQL
EXEC MASTER.dbo.sp_addlinkedsrvlogin @rmtsrvname = @name, @useself = N'False', @locallogin = NULL, @rmtuser = N'postgres', @rmtpassword = 'sa';

-- Set up extended properties of the linked server
EXEC MASTER.dbo.sp_serveroption @server = @name, @optname = 'data access', @optvalue = 'true';
EXEC MASTER.dbo.sp_serveroption @server = @name, @optname = 'use remote collation', @optvalue = 'true';
EXEC MASTER.dbo.sp_serveroption @server = @name, @optname = 'rpc', @optvalue = 'true';
EXEC MASTER.dbo.sp_serveroption @server = @name, @optname = 'rpc out', @optvalue = 'true';

Once the script has been executed, you should be able to see the linked server created in SSMS.

Test the Linked Server

Now, let’s test the linked server using the following queries:

-- Using four-part naming
SELECT [id], [name]
FROM [SQLAuth_PG].[sqlauthority].[public].[mytable];

-- Using OPENQUERY
SELECT *
FROM OPENQUERY(SQLAuth_PG, 'SELECT id, name FROM mytable');

These queries demonstrate how to retrieve data from the linked server using both four-part naming and the OPENQUERY function.

Have you ever created any other third-party linked servers in your environments? What methods have you used? Feel free to share your experiences and insights in the comments section below!