How do you call a postgresql stored procedure using SQL Server Link server.

  • Zaki.Mohammed@Verizon.Com
  • Topic Author
  • Offline
  • User
  • User
More
4 years 8 months ago #14524 by Zaki.Mohammed@Verizon.Com
How do you call a postgresql stored procedure using SQL Server Link server.
I am looking at the correct syntax for executing the procedure using a four part link server like
LinkServerName.databasename.public.mystoredproc()

Our Stored procedure doesn't take any input parameters and the procedure just insert a row in a table.
I tried both four part name as well as below and getting error.
I am executing this from SQL Server 2016.
I can extract the data thru link server. Its just that I cannot execute a postgresql stored procedure.
We are using licensed version PGNP-Postgres-SE-1.4.0.3547-x64



CREATE OR REPLACE PROCEDURE public.testproc(
)
LANGUAGE 'plpgsql'

AS $BODY$
BEGIN

insert into public.test(Pkey) Values (3);
END;
$BODY$;
Declare @Str nvarchar(1000);
Set @str = N'"PGSQL_LinkServer"."TestDatabase"."public"."testproc()"'
EXEC SP_EXECUTESQL @str

OLE DB provider "PGNP" for linked server "PGSQL_LinkServer" returned message "ERROR: syntax error at or near "{"
LINE 1: {?=call "TestDatabase"."public"."testproc()";...
^
".
Msg 7212, Level 17, State 1, Line 1
Could not execute procedure 'zakitestproc()' on remote server 'PGSQL_SmartReferralBIDataLoad'.

EXEC('public.testproc()') AT PGSQL_LinkServer

LE DB provider "PGNP" for linked server "PGSQL_LinkServer" returned message "Undefined procedure public.testproc".
Msg 7215, Level 17, State 1, Line 12
Could not execute statement on remote server 'PGSQL_LinkServer'.

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

More
4 years 8 months ago #14525 by admin
If stored procedure does not return values/records, then it can be called as
EXEC('testproc') AT PGSQL_LinkServer

If stored procedure returns records, it can be called as
SELECT * FROM OPENQUERY(PGSQL_LinkServer, 'SELECT * FROM public.testproc()')

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

  • Zaki.Mohammed@Verizon.Com
  • Topic Author
  • Offline
  • User
  • User
More
4 years 8 months ago #14526 by Zaki.Mohammed@Verizon.Com
I am getting this error now.

Attachment LinkServer_09242019.png not found

Attachments:

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

More
4 years 8 months ago #14527 by admin
What version of Postgres are you using? I tried the following with Postgres 10, and it worked as expected:
CREATE FUNCTION public.testproc()
RETURNS integer LANGUAGE 'plpgsql'
AS $BODY$
DECLARE
a_count integer;
BEGIN
insert into public.sptest(Pkey) Values (3);
GET DIAGNOSTICS a_count = ROW_COUNT;
RETURN a_count;
END;
$BODY$;

And then
EXEC ('testproc') AT PG10
the SSMS showed correct result '1'.

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

More
4 years 8 months ago #14528 by ashish.kilam
I am also on same team as Zaki, we are using Postgres 11. I'm getting a similar error "Undefined procedure" when calling a procedure from an SSIS package using the Intellisoft driver even though the procedure executes successfully when called within pgAdmin query tool. Is this a bug with the driver, what is the fix or workaround?

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

More
4 years 8 months ago #14529 by admin
You may need the latest PGNP provider build for Postgres 11. Please contact our support at pgoledb dot com in order to obtain the latest build.

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

Time to create page: 0.148 seconds
Powered by Kunena Forum