Here's the scenario- I have a postgresql and SQL Server 2008 located in different servers. I want to be able to transfer data from a table in postgresql to my SQL Server 2008 table.
Here's what I have done so far:
Downloaded and installed PostgreSQL Unicode ODBC Driver(psqlODBC) in the server that contains my SQL Server 2008 database.
Added that ODBC in System DSN and tested the connection- Success!
In SQL Server, created linked server using sp_addlinkedserver and created linked server login using sp_addlinkedsrvlogin
Now I simply want to test to see if I could write a query to see postgresql table in my SQL Server 2008. So I did write this query in SQL Server.
'SELECT * From information_schema.tables')
However I got this error message
OLE DB provider "MSDASQL" for linked server "mylinkedservername" returned message "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified".
Msg 7303, Level 16, State 1, Line 2
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "mylinkedservername".
I m new to postgreSQL environment so any help to my issue is greatly appreciated. Thanks in adv!