Solved

Postgresql to SQL Server 2008

Posted on 2013-01-30
9
1,272 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
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 39

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
 
LVL 39

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 39

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 39

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
CONVERT date time to a different time zone. 2 44
Caste datetime 2 51
Error in query 3 52
Need a SQL Server 2014 plug-in to scan the DB schema 4 12
Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
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.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

910 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

24 Experts available now in Live!

Get 1:1 Help Now