- Thank you received: 0
Store return value to a variable when calling a function from linked server
- ugxbescheh
- Topic Author
- Offline
- User
-
Less
More
5 years 7 months ago #14449
by ugxbescheh
Store return value to a variable when calling a function from linked server was created by ugxbescheh
Hello
I have Postgres a database connected through the linked server "PGLink" in a MSSQL server 2014.
I can call a function in Postgres db and the return value is shown in result pane in Management Studio:
[tt]EXEC ('select schema.getnextloadnr()') AT PGLink[/tt]
When I try to store the return value in a local variable, I get an error:
[tt]DECLARE @LoadNr int
SET @LoadNr = EXEC ('select schema.getnextloadnr()') AT PGLink
SELECT @LoadNr[/tt]
Incorrect syntax near the keyword 'EXEC'.
How to store the return value in the variable?
The function is:
[tt]CREATE OR REPLACE FUNCTION schema.getnextloadnr()
RETURNS numeric
LANGUAGE plpgsql
SECURITY DEFINER
AS $function$
BEGIN
RETURN nextval('LOAD_NR_SEQ');
END; $function$[/tt]
Thanks for any help.
I have Postgres a database connected through the linked server "PGLink" in a MSSQL server 2014.
I can call a function in Postgres db and the return value is shown in result pane in Management Studio:
[tt]EXEC ('select schema.getnextloadnr()') AT PGLink[/tt]
When I try to store the return value in a local variable, I get an error:
[tt]DECLARE @LoadNr int
SET @LoadNr = EXEC ('select schema.getnextloadnr()') AT PGLink
SELECT @LoadNr[/tt]
Incorrect syntax near the keyword 'EXEC'.
How to store the return value in the variable?
The function is:
[tt]CREATE OR REPLACE FUNCTION schema.getnextloadnr()
RETURNS numeric
LANGUAGE plpgsql
SECURITY DEFINER
AS $function$
BEGIN
RETURN nextval('LOAD_NR_SEQ');
END; $function$[/tt]
Thanks for any help.
Please Log in or Create an account to join the conversation.
- admin
- Offline
- Admin
-
Less
More
- Thank you received: 8
5 years 7 months ago #14450
by admin
Replied by admin on topic Store return value to a variable when calling a function from linked server
Please try the following:
INSERT @LoadNr EXEC ('select schema.getnextloadnr()') AT PGLink
INSERT @LoadNr EXEC ('select schema.getnextloadnr()') AT PGLink
Please Log in or Create an account to join the conversation.
- ugxbescheh
- Topic Author
- Offline
- User
-
Less
More
- Thank you received: 0
5 years 7 months ago #14451
by ugxbescheh
Replied by ugxbescheh on topic Store return value to a variable when calling a function from linked server
Thanks for your reply.
This assumes @LoadNr to be declared as table.
This brings the following error:
Incorrect syntax near the keyword 'INSERT'.
Do you have any other suggestion?
This assumes @LoadNr to be declared as table.
This brings the following error:
Incorrect syntax near the keyword 'INSERT'.
Do you have any other suggestion?
Please Log in or Create an account to join the conversation.
- admin
- Offline
- Admin
-
Less
More
- Thank you received: 8
5 years 7 months ago #14452
by admin
Replied by admin on topic Store return value to a variable when calling a function from linked server
I tried the following, and it worked well with the current release:
SET @LoadNr = (select col1 from OPENQUERY(PGLink, 'select schema.getnextloadnr() as col1'))
SET @LoadNr = (select col1 from OPENQUERY(PGLink, 'select schema.getnextloadnr() as col1'))
Please Log in or Create an account to join the conversation.
- ugxbescheh
- Topic Author
- Offline
- User
-
Less
More
- Thank you received: 0
5 years 7 months ago #14453
by ugxbescheh
Replied by ugxbescheh on topic Store return value to a variable when calling a function from linked server
Hello admin
GREAT; this works fine for me too!
Thanks a lot
Hans-Peter
GREAT; this works fine for me too!
Thanks a lot
Hans-Peter
Please Log in or Create an account to join the conversation.
Time to create page: 0.165 seconds