We are looking into alternative database platforms (for us, alternative to SQL Server 2005), and we came across PostgreSQL. Back in the day when I used to do much more development using open-source platforms, I used MySql, and was frankly never impressed. However, PostgreSQL really shines, especially for someone used to working with Microsoft tools.





The interface is slick - it is easy to setup, and there are many features SQL Server heads will enjoy like transactions and stored procedures.



Anyway, as part of this project I needed to query PostgreSQL from SQL Server 2005, so I wouldn’t have to rewrite my data layer for the bits of data that are going to be handled by PostgreSQL.



I searched the internet high and low and came across this article: http://www.thescripts.com/forum/thread424219.html



Basically, if you follow the steps outlined:

• Add a System DSN in ODBC Manager on your SQL Server and point it to your PostgreSQL instance.
• Set up the security using the following statement in SQL Server:
• EXEC sp_AddLinkedSrvLogin
  
  
  @rmtsrvname = ‘PostgreSQL’,
  
  
  @useself = ‘FALSE’,
  
  
  @locallogin = NULL,
  
  
  @rmtuser = ‘postgre’, — User and password created in PostgreSQL pgAdmin
  
  
  @rmtpassword = ‘password’
  
  
  GO
• Run your query using OPENQUERY:
  SELECT * FROM OPENQUERY(PostgreSQL, ‘SELECT * FROM “Customer”‘)

Unforunately, I can’t seem to find (nor has the original author of the thread above) a way to query it using normal SQL Server linking syntax, but OPENQUERY is good enough for me.