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

Start
Prev
1
Next
End

Zaki.Mohammed@Verizon.Com
Topic Author
Offline
User

2 years 7 months ago #14524 by Zaki.Mohammed@Verizon.Com

How do you call a postgresql stored procedure using SQL Server Link server. was created 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.

admin
Offline
Admin

2 years 7 months ago #14525 by admin

Replied by admin on topic How do you call a postgresql stored procedure using SQL Server Link server.

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

2 years 7 months ago #14526 by Zaki.Mohammed@Verizon.Com

Replied by Zaki.Mohammed@Verizon.Com on topic How do you call a postgresql stored procedure using SQL Server Link server.

I am getting this error now.

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

admin
Offline
Admin

2 years 7 months ago #14527 by admin

Replied by admin on topic How do you call a postgresql stored procedure using SQL Server Link server.

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.

2 years 7 months ago #14528 by ashish.kilam

Replied by ashish.kilam on topic How do you call a postgresql stored procedure using SQL Server Link server.

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.

admin
Offline
Admin

2 years 7 months ago #14529 by admin

Replied by admin on topic How do you call a postgresql stored procedure using SQL Server Link server.

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.

2 years 7 months ago #14530 by ashish.kilam

Replied by ashish.kilam on topic How do you call a postgresql stored procedure using SQL Server Link server.

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?

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

2 years 7 months ago #14531 by ashish.kilam

Replied by ashish.kilam on topic How do you call a postgresql stored procedure using SQL Server Link server.

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?

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

2 years 7 months ago #14534 by ashish.kilam

Replied by ashish.kilam on topic How do you call a postgresql stored procedure using SQL Server Link server.

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

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

admin
Offline
Admin

2 years 7 months ago #14535 by admin

Replied by admin on topic How do you call a postgresql stored procedure using SQL Server Link server.

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

Thank you!

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

2 years 7 months ago #14537 by ashish.kilam

Replied by ashish.kilam on topic How do you call a postgresql stored procedure using SQL Server Link server.

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>', '|'),
''', CHR (39)), '"', '"'), '~', '~'), '&', '&'),
customer_last_name = replace(replace(replace(replace(replace(replace(replace(replace(customer_last_name, '<cr>', CHR (10)), '<lf>', CHR (13)), '<tab>', CHR (9)), '<pipe>', '|'),
''', CHR (39)), '"', '"'), '~', '~'), '&', '&'),
business_name = replace(replace(replace(replace(replace(replace(replace(replace(business_name, '<cr>', CHR (10)), '<lf>', CHR (13)), '<tab>', CHR (9)), '<pipe>', '|'),
''', CHR (39)), '"', '"'), '~', '~'), '&', '&'),
contact_name = replace(replace(replace(replace(replace(replace(replace(replace(contact_name, '<cr>', CHR (10)), '<lf>', CHR (13)), '<tab>', CHR (9)), '<pipe>', '|'),
''', CHR (39)), '"', '"'), '~', '~'), '&', '&'),
addr1 = replace(replace(replace(replace(replace(replace(replace(replace(addr1, '<cr>', CHR (10)), '<lf>', CHR (13)), '<tab>', CHR (9)), '<pipe>', '|'),
''', CHR (39)), '"', '"'), '~', '~'), '&', '&'),
addr2 = replace(replace(replace(replace(replace(replace(replace(replace(addr2, '<cr>', CHR (10)), '<lf>', CHR (13)), '<tab>', CHR (9)), '<pipe>', '|'),
''', CHR (39)), '"', '"'), '~', '~'), '&', '&'),
city = replace(replace(replace(replace(replace(replace(replace(replace(city, '<cr>', CHR (10)), '<lf>', CHR (13)), '<tab>', CHR (9)), '<pipe>', '|'),
''', CHR (39)), '"', '"'), '~', '~'), '&', '&'),
email_addr = replace(replace(replace(replace(replace(replace(replace(replace(email_addr, '<cr>', CHR (10)), '<lf>', CHR (13)), '<tab>', CHR (9)), '<pipe>', '|'),
''', CHR (39)), '"', '"'), '~', '~'), '&', '&'),
region = replace(replace(replace(replace(replace(replace(replace(replace(region, '<cr>', CHR (10)), '<lf>', CHR (13)), '<tab>', CHR (9)), '<pipe>', '|'),
''', CHR (39)), '"', '"'), '~', '~'), '&', '&'),
submit_node_path = replace(replace(replace(replace(replace(replace(replace(replace(submit_node_path, '<cr>', CHR (10)), '<lf>', CHR (13)), '<tab>', CHR (9)), '<pipe>', '|'),
''', CHR (39)), '"', '"'), '~', '~'), '&', '&'),
comments = replace(replace(replace(replace(replace(replace(replace(replace(comments, '<cr>', CHR (10)), '<lf>', CHR (13)), '<tab>', CHR (9)), '<pipe>', '|'),
''', CHR (39)), '"', '"'), '~', '~'), '&', '&'),
created_by = replace(replace(replace(replace(replace(replace(replace(replace(created_by, '<cr>', CHR (10)), '<lf>', CHR (13)), '<tab>', CHR (9)), '<pipe>', '|'),
''', CHR (39)), '"', '"'), '~', '~'), '&', '&'),
modified_by = replace(replace(replace(replace(replace(replace(replace(replace(modified_by, '<cr>', CHR (10)), '<lf>', CHR (13)), '<tab>', CHR (9)), '<pipe>', '|'),
''', CHR (39)), '"', '"'), '~', '~'), '&', '&'),
arz_center_name = replace(replace(replace(replace(replace(replace(replace(replace(arz_center_name, '<cr>', CHR (10)), '<lf>', CHR (13)), '<tab>', CHR (9)), '<pipe>', '|'),
''', CHR (39)), '"', '"'), '~', '~'), '&', '&'),
arz_vendor_name = replace(replace(replace(replace(replace(replace(replace(replace(arz_vendor_name, '<cr>', CHR (10)), '<lf>', CHR (13)), '<tab>', CHR (9)), '<pipe>', '|'),
''', CHR (39)), '"', '"'), '~', '~'), '&', '&'),
director = replace(replace(replace(replace(replace(replace(replace(replace(director, '<cr>', CHR (10)), '<lf>', CHR (13)), '<tab>', CHR (9)), '<pipe>', '|'),
''', CHR (39)), '"', '"'), '~', '~'), '&', '&'),
district = replace(replace(replace(replace(replace(replace(replace(replace(district, '<cr>', CHR (10)), '<lf>', CHR (13)), '<tab>', CHR (9)), '<pipe>', '|'),
''', CHR (39)), '"', '"'), '~', '~'), '&', '&'),
executive = replace(replace(replace(replace(replace(replace(replace(replace(executive, '<cr>', CHR (10)), '<lf>', CHR (13)), '<tab>', CHR (9)), '<pipe>', '|'),
''', CHR (39)), '"', '"'), '~', '~'), '&', '&'),
supervisor = replace(replace(replace(replace(replace(replace(replace(replace(supervisor, '<cr>', CHR (10)), '<lf>', CHR (13)), '<tab>', CHR (9)), '<pipe>', '|'),
''', CHR (39)), '"', '"'), '~', '~'), '&', '&'),
work_group = replace(replace(replace(replace(replace(replace(replace(replace(work_group, '<cr>', CHR (10)), '<lf>', CHR (13)), '<tab>', CHR (9)), '<pipe>', '|'),
''', CHR (39)), '"', '"'), '~', '~'), '&', '&');

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>', '|'),
''', CHR (39)), '"', '"'), '~', '~'), '&', '&'),
created_by = replace(replace(replace(replace(replace(replace(replace(replace(created_by, '<cr>', CHR (10)), '<lf>', CHR (13)), '<tab>', CHR (9)), '<pipe>', '|'),
''', CHR (39)), '"', '"'), '~', '~'), '&', '&'),
modified_by = replace(replace(replace(replace(replace(replace(replace(replace(modified_by, '<cr>', CHR (10)), '<lf>', CHR (13)), '<tab>', CHR (9)), '<pipe>', '|'),
''', CHR (39)), '"', '"'), '~', '~'), '&', '&');

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>', '|'),
''', CHR (39)), '"', '"'), '~', '~'), '&', '&'),
product_comments = replace(replace(replace(replace(replace(replace(replace(replace(product_comments, '<cr>', CHR (10)), '<lf>', CHR (13)), '<tab>', CHR (9)), '<pipe>', '|'),
''', CHR (39)), '"', '"'), '~', '~'), '&', '&'),
created_by = replace(replace(replace(replace(replace(replace(replace(replace(created_by, '<cr>', CHR (10)), '<lf>', CHR (13)), '<tab>', CHR (9)), '<pipe>', '|'),
''', CHR (39)), '"', '"'), '~', '~'), '&', '&'),
modified_by = replace(replace(replace(replace(replace(replace(replace(replace(modified_by, '<cr>', CHR (10)), '<lf>', CHR (13)), '<tab>', CHR (9)), '<pipe>', '|'),
''', CHR (39)), '"', '"'), '~', '~'), '&', '&');

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>', '|'),
''', CHR (39)), '"', '"'), '~', '~'), '&', '&'),
CODE = replace(replace(replace(replace(replace(replace(replace(replace(CODE, '<cr>', CHR (10)), '<lf>', CHR (13)), '<tab>', CHR (9)), '<pipe>', '|'),
''', CHR (39)), '"', '"'), '~', '~'), '&', '&'),
DESCRIPTION = replace(replace(replace(replace(replace(replace(replace(replace(DESCRIPTION, '<cr>', CHR (10)), '<lf>', CHR (13)), '<tab>', CHR (9)), '<pipe>', '|'),
''', CHR (39)), '"', '"'), '~', '~'), '&', '&'),
SUBGROUP = replace(replace(replace(replace(replace(replace(replace(replace(SUBGROUP, '<cr>', CHR (10)), '<lf>', CHR (13)), '<tab>', CHR (9)), '<pipe>', '|'),
''', CHR (39)), '"', '"'), '~', '~'), '&', '&'),
OTHER_CODE = replace(replace(replace(replace(replace(replace(replace(replace(OTHER_CODE, '<cr>', CHR (10)), '<lf>', CHR (13)), '<tab>', CHR (9)), '<pipe>', '|'),
''', CHR (39)), '"', '"'), '~', '~'), '&', '&'),
OTHER_DESCRIPTION = replace(replace(replace(replace(replace(replace(replace(replace(OTHER_DESCRIPTION, '<cr>', CHR (10)), '<lf>', CHR (13)), '<tab>', CHR (9)), '<pipe>', '|'),
''', CHR (39)), '"', '"'), '~', '~'), '&', '&');

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

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

2 years 7 months ago #14538 by ashish.kilam

Replied by ashish.kilam on topic How do you call a postgresql stored procedure using SQL Server Link server.

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

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

2 years 7 months ago #14539 by ashish.kilam

Replied by ashish.kilam on topic How do you call a postgresql stored procedure using SQL Server Link server.

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.

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

Start
Prev
1
Next
End

Time to create page: 0.149 seconds

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

Attachments:

Login Form

Search

VM - Shopping cart

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

Attachments:

Login Form

Search

VM - Shopping cart

Sign in to your account