The databases world is expanding and I have been fortunate enough to learn and share my experiences around databases like MySQL, PostgreSQL apart from working with SQL Server. I always try to expand my horizon and try various database products. In case you don’t know, I have a course on Pluralsight about PostgreSQL.
It is always interesting to integrate various products and make them work seamlessly. Due to my knowledge of both database products, I have been asked one question very frequently.
How can I create linked servers in SQL connecting with Postgres?
Though this question looks simple and easy – I thought of writing a note to show you the actual steps to achieve the same. This blog shows a simple example about creating linked server. There is a provider called PGOLEDB which can be used for this purpose.
Create a Database and Table in PostgreSQL
- Create database:
CREATE DATABASE SQLAuthority WITH OWNER = postgres ENCODING = 'UTF8';
- Once database is created, change the connection, create 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 we have data in table
Select * from MyTable
Create a Linked Server in SQL Server
- Go to and choose “Download” from menu bar.
- Go to the page and choose “PGNP OLEDB Providers for Postgres, Greenplum and Redshift” as shown below.
- Install it and then we should see provider it in SSMS
- Modify and run below script in SQL Server. You need to change Server Name, port etc.
-- Change parameter for provider Allow In Procees = true / DynamicParameters = true EXEC MASTER.dbo.sp_MSset_oledb_prop N'PGNP' ,N'AllowInProcess',1 GO EXEC MASTER.dbo.sp_MSset_oledb_prop N'PGNP' ,N'DynamicParameters',1 GO DECLARE @name NVARCHAR(4000); DECLARE @provider NVARCHAR(4000); DECLARE @servername NVARCHAR(4000); DECLARE @port NVARCHAR(4000); DECLARE @db_name NVARCHAR(4000) -- destination postgres 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 = N'PGNP' ,@datasrc = @servername ,@provstr = @port ,@catalog = @db_name -- username and password for postgres 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' GO
- Once completed, we should be able to see linked server created as shown below.
Test the Linked Server
Here are the test queries which I have used based on earlier script.
-- USING FOUR PART NAMING SELECT [id],[name] FROM [SQLAuth_PG].[sqlauthority].[public].[mytable] GO -- USING OPENQUERY SELECT * FROM OPENQUERY(SQLAuth_PG, 'select id, name from mytable');
Have you ever created any other 3rd party linked server in your environments? What methods have you used? Will you be kind enough to share the same via comments?
Reference : Pinal Dave (https://blog.sqlauthority.com)