[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Postgresql to SQL Server 2008

Posted on 2013-01-30
9
Medium Priority
?
1,361 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
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 
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 learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Many developers have database experience, but are new to PostgreSQL. It has some truly inspiring capabilities. I have several years' experience with Microsoft's SQL Server. When I began working with MySQL, I wanted a quick-reference to MySQL (htt…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
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.
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

649 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