Setting up a linked server from SQL Server 2005 to PostgreSQL

Question

Since I struggled a bit with this one and couldn't find a good online source with simple steps, here it is.

Solution

1. Download ODBCng and install it
2. Set up a System DNS that connects to your PostgreSQL server. I named mine POSTGRESQL, which is used in the next couple of steps

3. Run the following code in SSMS to create the linked server. This assumes a PostgreSQL instance on the local machine (hence localhost):

   EXEC master.dbo.sp_addlinkedserver @server = N'POSTGRESQL', @srvproduct=N'Microsoft OLE DB Provider for ODBC Driver', @provider=N'MSDASQL', @datasrc='PostgreSQL', @location='localhost', @catalog='public'

4. Run the following code in SSMS to create a login mapping for the linked server:

   EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'POSTGRESQL', @useself=N'False', @locallogin=NULL, @rmtuser='', @rmtpassword=''

5. Issue statements such as:

   SELECT * FROM OpenQuery(POSTGRESQL, 'select my_column from my_table limit 10')

I had to use the double-quote