- Forum
- Main PGNP Forum
- SQL Server/DTS/SSIS/Linked servers/Replication
- 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.
- Zaki.Mohammed@Verizon.Com
-
Topic Author
- Offline
- User
-
- Posts: 2
- Thank you received: 0
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.
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
-
- Posts: 2
- Thank you received: 0
Please Log in or Create an account to join the conversation.
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.
- ashish.kilam
-
- Offline
- User
-
- Posts: 7
- Thank you received: 0
Please Log in or Create an account to join the conversation.
Please Log in or Create an account to join the conversation.
- ashish.kilam
-
- Offline
- User
-
- Posts: 7
- Thank you received: 0
Please Log in or Create an account to join the conversation.
- ashish.kilam
-
- Offline
- User
-
- Posts: 7
- Thank you received: 0
Please Log in or Create an account to join the conversation.
- ashish.kilam
-
- Offline
- User
-
- Posts: 7
- Thank you received: 0
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.
Thank you!
Please Log in or Create an account to join the conversation.
- ashish.kilam
-
- Offline
- User
-
- Posts: 7
- Thank you received: 0
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'.
Please Log in or Create an account to join the conversation.
- ashish.kilam
-
- Offline
- User
-
- Posts: 7
- Thank you received: 0
Please Log in or Create an account to join the conversation.
- ashish.kilam
-
- Offline
- User
-
- Posts: 7
- Thank you received: 0
Please Log in or Create an account to join the conversation.
- Forum
- Main PGNP Forum
- SQL Server/DTS/SSIS/Linked servers/Replication
- How do you call a postgresql stored procedure using SQL Server Link server.
- You are here:
-
Home
-
Forum
-
Main PGNP Forum
-
SQL Server/DTS/SSIS/Linked servers/Replication
- How do you call a postgresql stored procedure using SQL Server Link server.