Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2016
    Posts
    3

    Unanswered: Windows 2008 64 linked server to PostgreSQL problem

    I am struggling with connecting a linked server from a Windows 2008 R2 64 bit server to a PostgreSQL server. The Linked server tests successfully, and I can browse the tables of the linked database.
    When I run a query, however, I get the following messages:

    Msg 7399, Level 16, State 1, Line 14
    The OLE DB provider "MSDASQL" for linked server "MyServer" reported an error. The provider did not give any information about the error.
    Msg 7350, Level 16, State 2, Line 14
    Cannot get the column information from OLE DB provider "MSDASQL" for linked server "MyServer".

    I downloaded the driver from here: https://code.google.com/archive/p/visionmap/downloads
    And, I have Configured the OLEDB provider to run in-process, and assigned the appropriate user rights as explained here: https://support.microsoft.com/en-us/kb/2450479

    I have tried all the suggestions listed Here: http://www.postgresonline.com/journa...eSQL-Data.html

    Here: https://connect.microsoft.com/SQLSer...09-00-0200-x64

    And Here: https://connect.microsoft.com/SQLSer...09-00-0200-x64
    No luck so far though. Can anyone please give any additional insight on a remedy for this?

  2. #2
    Join Date
    Nov 2003
    Posts
    2,914
    Provided Answers: 8
    You should download the official ODBC driver, not some outdated one bundled with an application:

    https://odbc.postgresql.org/

    I don't really know what kind of driver SQL Server needs, but maybe the .Net driver would be usable as well:

    http://www.npgsql.org/
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I've used https://www.postgresql.org/about/news/1153/ with several versions of SQL Server successfully. What is your SQL Server build number returned by
    Code:
    SELECT ServerProperty('ProductVersion')
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #4
    Join Date
    Jun 2016
    Posts
    3
    Quote Originally Posted by Pat Phelan View Post
    I've used https://www.postgresql.org/about/news/1153/ with several versions of SQL Server successfully. What is your SQL Server build number returned by
    Code:
    SELECT ServerProperty('ProductVersion')
    -PatP
    My version is 10.50.1600.1
    Thanks so much for your help

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I would expect the PostgreSQL driver on the URL that I posted to work for your SQL Server, but it REALLY needs to have service packs applied to it. The RTM version of SQL 2008 R2 was notably crabby about exchanging data with other platforms, while the Service Packs made it much easier and faster.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  6. #6
    Join Date
    Jun 2016
    Posts
    3

    Ss12

    Pat,
    Have you worked with SS12 and linking it to PostgreSQL? I am told that we should soon be migrating to SS12 from SS08. I'm thinking that it might be best to work with SS12 since we are headed that way, and wave the white flag for SS08.

    Thanks again for your advice!
    Gina

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    A lot depends on what is "soon" and what is your pain-tolerance for not having the Linked Server working. Ultimately that is a call that you need to make, I can't make that one for you.

    As a general observation, connectivity gets better with each passing version, so SQL Server 2012 is better than SQL Server 2008 R2. SQL 2016 is much better still, and you can download the Developer Edition of SQL Server 2016 for free to start testing when you have time.

    In your particular case, using a Linked Server probably isn't the easiest or most efficient way to get data from PostgreSQL. This will become more true (the gap between easy and Linked Server will grow) as you move to later versions of SQL Server.

    If you know precisely what you want to do and already understand how to do that using Linked Servers then I can still make a case for using them. If you don't already understand exactly how to do what you need to do using a Linked Server, then I'd look very hard at some of the other choices. Linked Servers were once the only real solution for cross platform data, but there are many choices today that are lots easier in terms of care and feeding of the solution and happiness of the end users. These are probably worth a good hard look, but will probably require some discussion and guidance.

    Check out both SQLSaturday.com for free training that may be near you, and SQLPass.org for a great organization of user groups world-wide that probably have someone willing to help you. You're always welcome here and the denizens of DBForums will gleefully help you, I just want you to find the folks who can give you the help that you like best!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •