Caputuring Postgres exceptions in SQL stored proc

Using PGNP with MS SQL Server, Data Transformation Services (DTS), SQL Server Integration Services (SSIS), Linked Servers, Snapshot and Transactional Replication and more.

Caputuring Postgres exceptions in SQL stored proc

Postby mc_coder » Thu Dec 13, 2012 1:16 am

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
 
Posts: 7
Joined: Wed Nov 14, 2012 7:37 am

Re: Caputuring Postgres exceptions in SQL stored proc

Postby mc_coder » Tue Dec 18, 2012 9:12 am

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
mc_coder
 
Posts: 7
Joined: Wed Nov 14, 2012 7:37 am

Re: Caputuring Postgres exceptions in SQL stored proc

Postby Moderator » Tue Dec 18, 2012 9:33 pm

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.
User avatar
Moderator
Site Admin
 
Posts: 310
Joined: Wed Oct 29, 2008 11:27 pm

Re: Caputuring Postgres exceptions in SQL stored proc

Postby Moderator » Tue Dec 18, 2012 11:19 pm

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.
User avatar
Moderator
Site Admin
 
Posts: 310
Joined: Wed Oct 29, 2008 11:27 pm

Re: Caputuring Postgres exceptions in SQL stored proc

Postby mc_coder » Wed Dec 19, 2012 1:25 am

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
mc_coder
 
Posts: 7
Joined: Wed Nov 14, 2012 7:37 am

Re: Caputuring Postgres exceptions in SQL stored proc

Postby Moderator » Wed Dec 26, 2012 9:02 am

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".
User avatar
Moderator
Site Admin
 
Posts: 310
Joined: Wed Oct 29, 2008 11:27 pm


Return to SQL Server/DTS/SSIS/Linked servers/Replication

Who is online

Users browsing this forum: No registered users and 5 guests

cron