December 10, 2020 at 5:21 am
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
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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy