Solved

Postgresql to SQL Server 2008

Posted on 2013-01-30
9
1,285 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 40

Expert Comment

by:lcohan
ID: 38836834
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
ID: 38837156
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 40

Expert Comment

by:lcohan
ID: 38837211
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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
LVL 40

Expert Comment

by:lcohan
ID: 38837222
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
 
LVL 40

Expert Comment

by:lcohan
ID: 38837241
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
ID: 38840082
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 40

Expert Comment

by:lcohan
ID: 38840385
"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
ID: 38856761
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
ID: 38949155
researched more on this for a solution.
0

Featured Post

Free Webinar: AWS Backup & DR

Join our upcoming webinar with experts from AWS, CloudBerry Lab, and the Town of Edgartown IT to discuss best practices for simplifying online backup management and cutting costs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
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.
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

733 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