Problems using SQL variables in linked server queries

More
5 years 9 months ago #12627 by dklein
dklein created the topic: Problems using SQL variables in linked server queries
We are running PGOLEDB 1.4.0.3284 on SQL Server 2012 and on SQL Server 2008 R2, connecting to PostGreSQL 9.3 running on Centos.

The provider options we have set are "Dynamic Parameter" and "Allow Inprocess"
The linked server is configured to support data access and RPC in and out.

"Execute... at" and "openquery" seem to work fine, but we are having issues whenever we use variables in our linked server queries as "set" values.

For example,
declare @myval int = 10;
update PGSserver.PGDB.[public].mytable set mycol = @myval;

OLE DB provider "PGNP" for linked server "PGServer" returned message "ERROR:  syntax error at end of input
LINE 1: MOVE BACKWARD 5 FROM 
                            ^

The other problem we are having is executing parameterized functions
declare @myval int = 10;
exec PGSserver.PGDB.[public].[foo] @myval;

OLE DB provider "PGNP" for linked server "PGSserver" returned message "ERROR:  syntax error at or near "{"
LINE 1: {?=call "PGDB"."public"."foo";1(?)}

I can get around it by using "execute at" or "openquery" but then everything needs to be done with dynamic SQL.

Are there any settings or workarounds to solve this - either in PostGreSQL, the PGOLEDB or SQL Server?

Please Log in or Create an account to join the conversation.

More
5 years 9 months ago #12631 by Moderator
Moderator replied the topic: Re: Problems using SQL variables in linked server queries
Let me reproduce the issues. I will update the thread shortly. Thank you!

Please Log in or Create an account to join the conversation.

More
5 years 8 months ago #12658 by dklein
dklein replied the topic: Re: Problems using SQL variables in linked server queries
As a follow up to this. When I downgraded to a driver version from July my first issue went away.

It doesn't necessarily surprise me that the driver does not support calling remote functions with parameters via a linked server since SQL server does not support calling remote functions, so I don't really have a problem working around this one.

Please Log in or Create an account to join the conversation.

Time to create page: 0.065 seconds
Powered by Kunena Forum