Solved

Postgresql to SQL Server 2008

Posted on 2013-01-30
9
1,264 Views
Last Modified: 2013-03-04
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!
0
Comment
Question by:dearnemo
  • 5
  • 4
9 Comments
 
LVL 39

Expert Comment

by:lcohan
Comment Utility
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
 

Author Comment

by:dearnemo
Comment Utility
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
 
LVL 39

Expert Comment

by:lcohan
Comment Utility
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
 
LVL 39

Expert Comment

by:lcohan
Comment Utility
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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 39

Expert Comment

by:lcohan
Comment Utility
Below are the settings from one that I use in my DEV env:
PG-DP1.png
PG-DP2.png
0
 

Accepted Solution

by:
dearnemo earned 0 total points
Comment Utility
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
 
LVL 39

Expert Comment

by:lcohan
Comment Utility
"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
 

Author Comment

by:dearnemo
Comment Utility
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
 

Author Closing Comment

by:dearnemo
Comment Utility
researched more on this for a solution.
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Steps to create a PostgreSQL RDS instance in the Amazon cloud. We will cover some of the default settings and show how to connect to the instance once it is up and running.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now