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

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'.

---

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()')

I am getting this error now.

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'.

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?

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.

We are planning to install the latest Postgres 11 build, this bug only seems to apply to procedures not functions. However, when I run update statements directly or through a function in the SSIS package using the Intellisoft driver, they are timing out. The same update statements and function runs fine when executed in pgAdmin query tool. Will this issue also be fixed with the latest build upgrade?

After installing the latest driver build and testing it in our non-prod environment, the "Undefined procedure" error seems to be fixed but we are still getting timeout errors with both update procedures and functions. These update procedures and functions run successfully in the pgAdmin query tool - is there any fix or workaround for this?

This issue is from another team in Verizon trying to use the Intellisoft OLEDB driver:

We have packages which execute stored procedures in Postgres 10.X
We are creating a OLEDB Connection and a SQL Script Task with OLE DB Connection.

Our stored procedure looks like as below

DO
$$
BEGIN
Perform spInsertSampleTask (strSampleInput => $strSampleInput, strSampleInput2 => $strSampleInput2);
END
$$;

We are setting this in SSIS as Direct Input with the above text in query.
We have mapped parameters $strSampleInput and $strSampleInput2.
The parameters are not replaced, we have tried with all options like with in quotes and single quotes.
or just using ?,?
Perform spInsertSampleTask (?,?);

We have to pass named parameters, as we pass part of the input from the SSIS package.
We are hitting this roadblock with PGNP package

Could you send a test SSIS package, and the CREATE FUNCTION script to our Support?

Thank you!

This seems to be bug or configuration issue because the updates (whether in procedures, functions, or outside them) are timing out after exactly 30 seconds every time. This happens both from the SSIS package connection or linked server. We tried checking the Allow Non-Transacted Updates property for the provider but that didn't seem to make a difference. Here are the update statements:

update public.export_referral
set customer_first_name = replace(replace(replace(replace(replace(replace(replace(replace(customer_first_name, '<cr>', CHR (10)), '<lf>', CHR (13)), '<tab>', CHR (9)), '<pipe>', '|'),
'<span>'</span>', CHR (39)), '<span>"</span>', '"'), '<span>~</span>', '~'), '<span>&</span>', '&'),
customer_last_name = replace(replace(replace(replace(replace(replace(replace(replace(customer_last_name, '<cr>', CHR (10)), '<lf>', CHR (13)), '<tab>', CHR (9)), '<pipe>', '|'),
'<span>'</span>', CHR (39)), '<span>"</span>', '"'), '<span>~</span>', '~'), '<span>&</span>', '&'),
business_name = replace(replace(replace(replace(replace(replace(replace(replace(business_name, '<cr>', CHR (10)), '<lf>', CHR (13)), '<tab>', CHR (9)), '<pipe>', '|'),
'<span>'</span>', CHR (39)), '<span>"</span>', '"'), '<span>~</span>', '~'), '<span>&</span>', '&'),
contact_name = replace(replace(replace(replace(replace(replace(replace(replace(contact_name, '<cr>', CHR (10)), '<lf>', CHR (13)), '<tab>', CHR (9)), '<pipe>', '|'),
'<span>'</span>', CHR (39)), '<span>"</span>', '"'), '<span>~</span>', '~'), '<span>&</span>', '&'),
addr1 = replace(replace(replace(replace(replace(replace(replace(replace(addr1, '<cr>', CHR (10)), '<lf>', CHR (13)), '<tab>', CHR (9)), '<pipe>', '|'),
'<span>'</span>', CHR (39)), '<span>"</span>', '"'), '<span>~</span>', '~'), '<span>&</span>', '&'),
addr2 = replace(replace(replace(replace(replace(replace(replace(replace(addr2, '<cr>', CHR (10)), '<lf>', CHR (13)), '<tab>', CHR (9)), '<pipe>', '|'),
'<span>'</span>', CHR (39)), '<span>"</span>', '"'), '<span>~</span>', '~'), '<span>&</span>', '&'),
city = replace(replace(replace(replace(replace(replace(replace(replace(city, '<cr>', CHR (10)), '<lf>', CHR (13)), '<tab>', CHR (9)), '<pipe>', '|'),
'<span>'</span>', CHR (39)), '<span>"</span>', '"'), '<span>~</span>', '~'), '<span>&</span>', '&'),
email_addr = replace(replace(replace(replace(replace(replace(replace(replace(email_addr, '<cr>', CHR (10)), '<lf>', CHR (13)), '<tab>', CHR (9)), '<pipe>', '|'),
'<span>'</span>', CHR (39)), '<span>"</span>', '"'), '<span>~</span>', '~'), '<span>&</span>', '&'),
region = replace(replace(replace(replace(replace(replace(replace(replace(region, '<cr>', CHR (10)), '<lf>', CHR (13)), '<tab>', CHR (9)), '<pipe>', '|'),
'<span>'</span>', CHR (39)), '<span>"</span>', '"'), '<span>~</span>', '~'), '<span>&</span>', '&'),
submit_node_path = replace(replace(replace(replace(replace(replace(replace(replace(submit_node_path, '<cr>', CHR (10)), '<lf>', CHR (13)), '<tab>', CHR (9)), '<pipe>', '|'),
'<span>'</span>', CHR (39)), '<span>"</span>', '"'), '<span>~</span>', '~'), '<span>&</span>', '&'),
comments = replace(replace(replace(replace(replace(replace(replace(replace(comments, '<cr>', CHR (10)), '<lf>', CHR (13)), '<tab>', CHR (9)), '<pipe>', '|'),
'<span>'</span>', CHR (39)), '<span>"</span>', '"'), '<span>~</span>', '~'), '<span>&</span>', '&'),
created_by = replace(replace(replace(replace(replace(replace(replace(replace(created_by, '<cr>', CHR (10)), '<lf>', CHR (13)), '<tab>', CHR (9)), '<pipe>', '|'),
'<span>'</span>', CHR (39)), '<span>"</span>', '"'), '<span>~</span>', '~'), '<span>&</span>', '&'),
modified_by = replace(replace(replace(replace(replace(replace(replace(replace(modified_by, '<cr>', CHR (10)), '<lf>', CHR (13)), '<tab>', CHR (9)), '<pipe>', '|'),
'<span>'</span>', CHR (39)), '<span>"</span>', '"'), '<span>~</span>', '~'), '<span>&</span>', '&'),
arz_center_name = replace(replace(replace(replace(replace(replace(replace(replace(arz_center_name, '<cr>', CHR (10)), '<lf>', CHR (13)), '<tab>', CHR (9)), '<pipe>', '|'),
'<span>'</span>', CHR (39)), '<span>"</span>', '"'), '<span>~</span>', '~'), '<span>&</span>', '&'),
arz_vendor_name = replace(replace(replace(replace(replace(replace(replace(replace(arz_vendor_name, '<cr>', CHR (10)), '<lf>', CHR (13)), '<tab>', CHR (9)), '<pipe>', '|'),
'<span>'</span>', CHR (39)), '<span>"</span>', '"'), '<span>~</span>', '~'), '<span>&</span>', '&'),
director = replace(replace(replace(replace(replace(replace(replace(replace(director, '<cr>', CHR (10)), '<lf>', CHR (13)), '<tab>', CHR (9)), '<pipe>', '|'),
'<span>'</span>', CHR (39)), '<span>"</span>', '"'), '<span>~</span>', '~'), '<span>&</span>', '&'),
district = replace(replace(replace(replace(replace(replace(replace(replace(district, '<cr>', CHR (10)), '<lf>', CHR (13)), '<tab>', CHR (9)), '<pipe>', '|'),
'<span>'</span>', CHR (39)), '<span>"</span>', '"'), '<span>~</span>', '~'), '<span>&</span>', '&'),
executive = replace(replace(replace(replace(replace(replace(replace(replace(executive, '<cr>', CHR (10)), '<lf>', CHR (13)), '<tab>', CHR (9)), '<pipe>', '|'),
'<span>'</span>', CHR (39)), '<span>"</span>', '"'), '<span>~</span>', '~'), '<span>&</span>', '&'),
supervisor = replace(replace(replace(replace(replace(replace(replace(replace(supervisor, '<cr>', CHR (10)), '<lf>', CHR (13)), '<tab>', CHR (9)), '<pipe>', '|'),
'<span>'</span>', CHR (39)), '<span>"</span>', '"'), '<span>~</span>', '~'), '<span>&</span>', '&'),
work_group = replace(replace(replace(replace(replace(replace(replace(replace(work_group, '<cr>', CHR (10)), '<lf>', CHR (13)), '<tab>', CHR (9)), '<pipe>', '|'),
'<span>'</span>', CHR (39)), '<span>"</span>', '"'), '<span>~</span>', '~'), '<span>&</span>', '&');

update public.export_referral_product
set product_comments = replace(replace(replace(replace(replace(replace(replace(replace(product_comments, '<cr>', CHR (10)), '<lf>', CHR (13)), '<tab>', CHR (9)), '<pipe>', '|'),
'<span>'</span>', CHR (39)), '<span>"</span>', '"'), '<span>~</span>', '~'), '<span>&</span>', '&'),
created_by = replace(replace(replace(replace(replace(replace(replace(replace(created_by, '<cr>', CHR (10)), '<lf>', CHR (13)), '<tab>', CHR (9)), '<pipe>', '|'),
'<span>'</span>', CHR (39)), '<span>"</span>', '"'), '<span>~</span>', '~'), '<span>&</span>', '&'),
modified_by = replace(replace(replace(replace(replace(replace(replace(replace(modified_by, '<cr>', CHR (10)), '<lf>', CHR (13)), '<tab>', CHR (9)), '<pipe>', '|'),
'<span>'</span>', CHR (39)), '<span>"</span>', '"'), '<span>~</span>', '~'), '<span>&</span>', '&');

update public.export_rs_trx_history
set nosale_reason = replace(replace(replace(replace(replace(replace(replace(replace(nosale_reason, '<cr>', CHR (10)), '<lf>', CHR (13)), '<tab>', CHR (9)), '<pipe>', '|'),
'<span>'</span>', CHR (39)), '<span>"</span>', '"'), '<span>~</span>', '~'), '<span>&</span>', '&'),
product_comments = replace(replace(replace(replace(replace(replace(replace(replace(product_comments, '<cr>', CHR (10)), '<lf>', CHR (13)), '<tab>', CHR (9)), '<pipe>', '|'),
'<span>'</span>', CHR (39)), '<span>"</span>', '"'), '<span>~</span>', '~'), '<span>&</span>', '&'),
created_by = replace(replace(replace(replace(replace(replace(replace(replace(created_by, '<cr>', CHR (10)), '<lf>', CHR (13)), '<tab>', CHR (9)), '<pipe>', '|'),
'<span>'</span>', CHR (39)), '<span>"</span>', '"'), '<span>~</span>', '~'), '<span>&</span>', '&'),
modified_by = replace(replace(replace(replace(replace(replace(replace(replace(modified_by, '<cr>', CHR (10)), '<lf>', CHR (13)), '<tab>', CHR (9)), '<pipe>', '|'),
'<span>'</span>', CHR (39)), '<span>"</span>', '"'), '<span>~</span>', '~'), '<span>&</span>', '&');

update public.export_rs_code
set TYPE = replace(replace(replace(replace(replace(replace(replace(replace(TYPE, '<cr>', CHR (10)), '<lf>', CHR (13)), '<tab>', CHR (9)), '<pipe>', '|'),
'<span>'</span>', CHR (39)), '<span>"</span>', '"'), '<span>~</span>', '~'), '<span>&</span>', '&'),
CODE = replace(replace(replace(replace(replace(replace(replace(replace(CODE, '<cr>', CHR (10)), '<lf>', CHR (13)), '<tab>', CHR (9)), '<pipe>', '|'),
'<span>'</span>', CHR (39)), '<span>"</span>', '"'), '<span>~</span>', '~'), '<span>&</span>', '&'),
DESCRIPTION = replace(replace(replace(replace(replace(replace(replace(replace(DESCRIPTION, '<cr>', CHR (10)), '<lf>', CHR (13)), '<tab>', CHR (9)), '<pipe>', '|'),
'<span>'</span>', CHR (39)), '<span>"</span>', '"'), '<span>~</span>', '~'), '<span>&</span>', '&'),
SUBGROUP = replace(replace(replace(replace(replace(replace(replace(replace(SUBGROUP, '<cr>', CHR (10)), '<lf>', CHR (13)), '<tab>', CHR (9)), '<pipe>', '|'),
'<span>'</span>', CHR (39)), '<span>"</span>', '"'), '<span>~</span>', '~'), '<span>&</span>', '&'),
OTHER_CODE = replace(replace(replace(replace(replace(replace(replace(replace(OTHER_CODE, '<cr>', CHR (10)), '<lf>', CHR (13)), '<tab>', CHR (9)), '<pipe>', '|'),
'<span>'</span>', CHR (39)), '<span>"</span>', '"'), '<span>~</span>', '~'), '<span>&</span>', '&'),
OTHER_DESCRIPTION = replace(replace(replace(replace(replace(replace(replace(replace(OTHER_DESCRIPTION, '<cr>', CHR (10)), '<lf>', CHR (13)), '<tab>', CHR (9)), '<pipe>', '|'),
'<span>'</span>', CHR (39)), '<span>"</span>', '"'), '<span>~</span>', '~'), '<span>&</span>', '&');

Here is the error message when run through a linked server:
OLE DB provider "PGNP" for linked server "PGSQL_SmartReferralBIDataLoad" returned message "timeout expired
".
Msg 7215, Level 17, State 1, Line 10
Could not execute statement on remote server 'PGSQL_SmartReferralBIDataLoad'.

Also just to clarify, these same update statements run successfully in pgAdmin query tool.

We figured this issue out, the COMMAND_TIMEOUT parameter needed to be set to 0 in the connection string extended properties. I made that change in the SSIS package and it runs successfully now.