Creating Linked Server for PostgreSQL in MSSQL

Creating a linked server in Microsoft SQL Server to connect PostgreSQL

1) Download the PostgreSQL Unicode (x64) driver to support ODBC.

2) Go to ODBC Data Source Administrator in your machine and create the system DSN.

Capture
3) Go to SQL Server Management Studio and execute the below command in a new query window.

EXEC master.dbo.sp_addlinkedserver 
@server = N’POSTGRES’, 
@srvproduct=N’PostgreSQL35W’, 
@datasrc=’PostgreSQL35W’,
@provider=N’MSDASQL’, 
@provstr=N’Driver=PostgreSQL 64-bit ODBC Drivers;uid=yourUserName;Server=LocalHost;database=YourDatabaseName;pwd=DBPassword’

4) Once executed successfully, go to Object explorer.
 
5) Expand the Server Objects and then Linked Servers. 
 
Now you will be able to see the newly created linked server. If you expand further, you will see the database name in the catalogs.
 
 
Please do let me know if you are facing any issues.
 
 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s