Postgresql to SQL Server 2008

Hi,

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 
        OpenQuery(NAMEOFLINKEDSERVERHERE, 
            'SELECT * From information_schema.tables')

Open in new window


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!
dearnemoAsked:
Who is Participating?
 
dearnemoConnect With a Mentor Author Commented:
Hi lcohan,

Thanks again for your responses.

On your note: you configured that ODBC system dsn ON that same box where your SQL is running right?
--> I configured ODBC system dsn in the server that houses my SQL Server.
(Please see the first attachment).

I checked the providers in SSMS and I do have SQLNCLI10  provider. Please see the list in the attachment. And YES 'Allow in Process' is checked.

I have POstgreSQl in my local machine and I have uname and pswd for PostgreSQL.
When you created a linked server in SSMS, I didn't see a place where you gave your uname and pswd. Is it because you have postgreSQL and SSMS in the same location and authenticated by your ntid?

Just an FYI- My SQL Server is in 32 bit machine and I installed 32 bit ODBC driver for POstGreSQL. Donno but if that helps to point to solution

Still getting the same error for my linked server.
ODBC-systemDSN.JPG
SSMS-Providers.JPG
0
 
lcohanDatabase AnalystCommented:
Please try first a simple query like below where caution thet PG IS case sensitive so make sure TableName is identical with your public.TableName in PG database configured via that ODBC and used in PG_LinkedServer:

SELECT * FROM OPENQUERY(PG_LinkedServer, 'SELECT * FROM TableName;');


Oh yeah...and choose a small tablename for the begining

A few more commands:

--Transfer to PG from SQL:

INSERT INTO OPENQUERY(PG_LinkedServer, 'SELECT col1,col2,col3 FROM PG_table')
SELECT col1,col2,col3
  FROM [SQLdb].[dbo].[SQL_table];

--Transfer to SQL from PG

INSERT INTO [SQLdb].[dbo].[SQL_table] (col1,col2,col3 )
SELECT * FROM OPENQUERY(PG_LinkedServer, 'SELECT col1,col2,col3 FROM TableName;');
0
 
dearnemoAuthor Commented:
Hi lcohan,
Thanks for your response. I tried your suggestions SELECT * FROM OPENQUERY(PG_LinkedServer, 'SELECT * FROM TableName;');

but still getting the same error. I had a doubt on my linked server, So I tested my linked server in SQL (just by right clicking and selecting test Connection) and found 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".

Any clue to to resolve this issue? TIA.
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
lcohanDatabase AnalystCommented:
So...you configured that ODBC system dsn ON that same box where your SQL is running right?
That ODBC data source connects that box to PG server database.
0
 
lcohanDatabase AnalystCommented:
Also can you please open SSMS, under Server Objects - > Providers you have a SQLNCLI10 provider that will be used by all the linked servers you created to use that ODBC - can you right click it and make sure Allow Inprocess is checked?
0
 
lcohanDatabase AnalystCommented:
Below are the settings from one that I use in my DEV env:
PG-DP1.png
PG-DP2.png
0
 
lcohanDatabase AnalystCommented:
"Is it because you have postgreSQL and SSMS in the same location and authenticated by your ntid? "

No - in my DEV /QA and PROD environments the srevers are separate (prod actualy are clustered both SQL and PG) and you do NOT need to provide any userid/pw on the link server because you put that ID and password IN the System DSN ODBC data config right?
0
 
dearnemoAuthor Commented:
Hi lcohan,

Thanks for your response. I still couldn't figure out why I m getting the same error. But thanks to all your emails.
0
 
dearnemoAuthor Commented:
researched more on this for a solution.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.