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

More
1 year 1 week ago #14449 by ugxbescheh
ugxbescheh created the topic: Store return value to a variable when calling a function from linked server
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:

EXEC ('select schema.getnextloadnr()') AT PGLink

When I try to store the return value in a local variable, I get an error:

DECLARE @LoadNr int
SET @LoadNr = EXEC ('select schema.getnextloadnr()') AT PGLink
SELECT @LoadNr


Incorrect syntax near the keyword 'EXEC'.

How to store the return value in the variable?

The function is:
CREATE OR REPLACE FUNCTION schema.getnextloadnr()
RETURNS numeric
LANGUAGE plpgsql
SECURITY DEFINER
AS $function$
BEGIN
RETURN nextval('LOAD_NR_SEQ');
END; $function$


Thanks for any help.

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

More
1 year 1 week ago #14450 by admin
admin replied the 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

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

More
1 year 1 week ago #14451 by ugxbescheh
ugxbescheh replied the 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?

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

More
1 year 1 week ago #14452 by admin
admin replied the 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'))

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

More
1 year 1 week ago #14453 by ugxbescheh
ugxbescheh replied the 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

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

Time to create page: 0.117 seconds
Powered by Kunena Forum