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

ashish.kilam
Offline
User

More

4 years 2 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.

ashish.kilam
Offline
User

More

4 years 2 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.

ashish.kilam
Offline
User

More

4 years 1 month 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

More

4 years 1 month 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.

ashish.kilam
Offline
User

More

4 years 1 month 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.

ashish.kilam
Offline
User

More

4 years 1 month 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.

Time to create page: 0.174 seconds

Powered by Kunena Forum

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

Login Form

VM - Shopping cart