Store return value to a variable when calling a function from linked server

More
5 years 7 months ago #14449 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.

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

More
5 years 7 months ago #14450 by admin
Please try the following:
INSERT @LoadNr EXEC ('select schema.getnextloadnr()') AT PGLink

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

More
5 years 7 months ago #14451 by ugxbescheh
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?

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

More
5 years 7 months ago #14452 by admin
I tried the following, and it worked well with the current release:
SET @LoadNr = (select col1 from OPENQUERY(PGLink, 'select schema.getnextloadnr() as col1'))

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

More
5 years 7 months ago #14453 by ugxbescheh
Hello admin

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
Powered by Kunena Forum