Caputuring Postgres exceptions in SQL stored proc

[mc_coder]

Hi,

I'm calling functions in a Postgres DB that make use of RAISE EXCEPTION. When I call the function from a SQL stored proc using linked server I don't seem to be able to capture the exception.

I'm tried calling my function as follows:

SELECT myfunc FROM OpenQuery(LINKEDSERVER,'select myfunc(25081) as myfunc ')

exec ('SELECT myfunc FROM OpenQuery(LINKEDSERVER,''select myfunc(25081) as myfunc'')')

Is it possible to capture the Postgres Exceptions when using linked servers and OpenQuery?

Regards,

Martin

[mc_coder]

Hi,

Is this forum still active? Just wondering if this is possible or should I be looking at another way to handle errors and exceptions.

Thanks,

Martin

[Moderator]

Hi Martin, sorry for the delay in answering your question. We were working on this issue today. The fix is almost ready. I just wanted to check how the function is called.
In our test we use:
create or replace function testnotimpl() returns int as
$$
begin
raise exception 'Not implemented' using errcode ='12345';
end;
$$
language plpgsql;

select * from openquery(PGSVR, 'select testnotimpl()');

If you are using a different way of calling the function please let us know. Fix will be available shortly.

[Moderator]

The issue was fixed. The following call:

Code: Select all

select * from openquery(PGSVR, 'select testnotimpl()');

returns:

Code: Select all

OLE DB provider "PGNP" for linked server "PGSVR" returned message "ERROR:  Not implemented
".
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "PGNP" for linked server "PGSVR".

Please contact Support on how to obtain the fixed module.

[mc_coder]

Hi,

Thanks for that. I've sent an email to support requesting the fix. I'll test and get back to you. When I call RAISE EXCEPTION in postgres I don't use the USING ERRCODE. Will that make a difference to this fix?

Regards,

Martin

[Moderator]

No, "USING ERRCODE" is optional. Moreover, the ERRCODE is returned from Postgres as 5 character SQL Error. Generally the SQL Error can be a combination of any characters, while OLEDB can only return integer "Native Errors". For this reason, OLEDB provider will not be able to return the error. So, I would recommend to not use "USING ERRCODE".