Post reply

OLE DB provider "PGNP" for linked server "MyLinkedServer" reported an error

  • December 10, 2020 at 5:21 am

    #3818846

    Hi,

    I am trying to migrate SQL Server databases from 2008 to 2019.  My old server has a linked server to PostgreSQL (version 8)

    I've installed the driver and created the linked server:

    I've installed the driver and created the linked server:
    EXEC master.dbo.sp_addlinkedserver @server = N'MyLinkedServer', @srvproduct=N'MyLinkedServer', @provider=N'PGNP', @datasrc=N'N'MyLinkedServer', @provstr=N'PORT=5432;CNV_SPECIAL_FLTVAL=ON;', @catalog=N'MyDB'
    ...

    The linked server is passing the connection test, however, when I run one of my SPs, I am getting this error:

    Msg 7399, Level 16, State 1, Line 1

    The OLE DB provider "PGNP" for linked server "MyLinkedServer" reported an error. Provider caused a server fault in an external process.

    Msg 7350, Level 16, State 2, Line 1

    Cannot get the column information from OLE DB provider "PGNP" for linked server "MyLinkedServer".

    If I change one of the queries in the SP from:

    If I change one of the queries in the SP from:
    DECLARE @sql varchar(MAX) = 'SELECT * FROM
    OPENQUERY('+@serverName+', ''SELECT u.column1 as column1, u.column2 || '''' '''' || u.column3 as name,
    case
    when ...
    when ...
    ...
    else r.column end as type
    FROM '+@dbName+'.[schemaname].table1 c
    INNER JOIN '+@dbName+'.[schemaname].table2 ra ON ra.id=c.id
    INNER JOIN '+@dbName+'.[schemaname].table3 r ON r.id=ra.id
    INNER JOIN '+@dbName+'.[schemaname].table4 u ON u.id=ra.id
    INNER JOIN '+@dbName+'.[schemaname].table5 u2 ON u2.id=c.id
    WHERE ...)';
    EXEC (@sql);

    To:

    To:
    DECLARE @sql varchar(MAX) = 'SELECT u.column1 as column1, u.column2 + '' '' + u.column3 as name,
    case
    when ...
    when ...
    ...
    else r.column end as type
    FROM '+@serverName+'.'+@dbName+'.[schemaname].table1 c
    INNER JOIN '+@serverName+'.'+@dbName+'.[schemaname].table2 ra ON ra.id=c.id
    INNER JOIN '+@serverName+'.'+@dbName+'.[schemaname].table3 r ON r.id=ra.id
    INNER JOIN '+@serverName+'.'+@dbName+'.[schemaname].table4 u ON u.id=ra.id
    INNER JOIN '+@serverName+'.'+@dbName+'.[schemaname].table5 u2 ON u2.id=c.id
    WHERE ...';
    EXEC (@sql);

    It starts working, even though there is another query in the same SP using "OPENQUERY".

    Should I use newer driver to suit sql 2019 or should I keep using the old one as on the old server to suit PostgreSQL?

    Any other ideas why this is not working?

    Thanks.

     

     

     

  • December 11, 2020 at 6:10 am

    #3819215

    Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply