March 1, 2014

Linked Server for a Remote Database Server

Linked server helps SQL Server Database Engine to execute the query against OLE data sources outside of the instance (Remote Server) of SQL Server.

To add a Linked Server, it can be done either using the GUI interface or the so_addlinkedserver command.

Suppose, we want to create Linked Server for Postgres Database Instance. For this:

1) Firstly, we need to create ODBC Data Source Administrator aster installing the Postgres ODBC Driver.

Go to Run. Type "odbcad32" - System DSN - Add-  Then configure

2) Now, to create Linked Server, you need to go to SQL Server Management Studio - expand the "Server Objects" folder, and then right click on the "Linked Server".

In the below screen shot, datasoure name should be PostgresSQL64W, as given above in the odbc connector. Here, give the linked server name, choose provider and give the datasource name. The provider for remote database will be Microsoft OLE DB Provider SQL Server. Provider string should be blank. Catalog is optional. (Default database can be used).

3) In detail, security and server option:

No comments: