November 9, 2007 at 10:16 am
I have built a procedure to send mail using OLE Automation and want to be able to trap error information when it doesn't work. So in an attempt to do that I have the following stored procedure that will return informaiton if the return value is <> 0. Here is how it is being used:
IF @return <> 0
BEGIN
EXECUTE sp_displayoaerrorinfo @handle, @return
END
ELSE
PRINT 'Success'
This works fine, but I would like to write the error message to a table and so I thought I could just alter to to be:
IF @return <> 0
BEGIN
EXECUTE sp_displayoaerrorinfo @handle, @return = @failure
END
ELSE
PRINT 'Success'
Where @failure is a variable I declared earlier. Then I could insert the value of this variable along with some other infomration into a table that would track the errors. However, when I do this I receive the following:
Error: Procedure or Function 'sp_DisplayOAErrorInfo' expects parameter '@HResult', which was not supplied.Number:201Severity:16State:4
So it isn't seeing that I am passing two variables into the stored procedure. I know I must be missing something simple but I've tried a bunch of different itterations and can't seem to get it right. Any help would be great. Thanks.
November 11, 2007 at 12:34 pm
Are you trying to get the error message details given by system stored procedure sp_displayoaerrorinfo into a variable?
November 11, 2007 at 8:01 pm
Yes I am, because my ultimate goal is to take that result along with some other information and write it to an error table so I have some audit information when it does fail.
November 12, 2007 at 1:33 am
The default sp_oaDisplayErrorInfo seem to display the error using print statements. As such it does not seem to return the error back as a parameter.
You may have to customize these to get the error message back.
Option 1 use sp_oaGetErrorInfo
The sp_oaGetErrorInfo returns source and description of error as parameters. Use this to construct your own error message and use it to for logging purposes.
-------------------------------------------------------------
declare @m_ret int
declare @m_hr int
declare @m_obj int
declare @m_source varchar(8000)
declare @m_desc varchar(8000)
declare @m_errInfo varchar(8000)
exec @m_ret = sp_oacreate 'Adodb.test', @m_obj out
if @m_ret <>0
begin
exec @m_hr = sp_OAGetErrorInfo @m_obj, @m_source OUT, @m_desc OUT
if @m_hr<>0
SELECT @m_errInfo = 'Source: ' + @m_source + char(13)+char(10) + @m_desc
else
SELECT @m_errInfo = 'Error calling sp_OAGetErrorInfo'
SELECT @m_errInfo as ErrorInfo --Use this for logging error
end
-------------------------------------------------------------
Option 2: customize sp_oadisplayerrorinfo
You can use something like below to customize the default sp_oadisplayerrorinfo storedprocedure and get back the detailed error information back to calling procedure
-----------------------------------------------------------------------
if exists (select *
from dbo.sysobjects
where id = object_id(N'[dbo].[fn_getoaerrorinfo]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[fn_getoaerrorinfo]
go
create procedure dbo.fn_getoaerrorinfo
@object int,
@hresult int,
@errinfo varchar(8000) OUTPUT
as
BEGIN
declare @output varchar(255),
@hrhex char(10),
@hr int,
@source varchar(255),
@description varchar(255)
SET @errinfo = 'OLE Automation Error Information'
exec sp_hexadecimal @hresult, @hrhex OUT
select @output = ' HResult: ' + @hrhex
SET @errinfo = @errinfo + CHAR(13) + CHAR(10) + @output
exec @hr = sp_OAGetErrorInfo @object, @source OUT, @description OUT
if @hr = 0
begin
select @output = ' Source: ' + @source
--PRINT @output
SET @errinfo = @errinfo + CHAR(13) + CHAR(10) + @output
select @output = ' Description: ' + @description
--PRINT @output
SET @errinfo = @errinfo + CHAR(13) + CHAR(10) + @output
end
else
begin
--print ' sp_OAGetErrorInfo failed.'
SET @errinfo = @errinfo + CHAR(13) + CHAR(10) + @output
end
return 0
end
-----------------------------------------------------------------------
Sample code the call the above fn_getoaerrorinfo is below
-------------------------------------------------------------
declare @m_ret int
declare @m_obj int
declare @m_errinfo varchar(8000)
exec @m_ret = sp_oacreate 'Adodb.test', @m_obj out
if @m_ret<>0
begin
exec fn_getoaerrorinfo @m_obj,@m_ret,@m_errinfo out
SELECT @m_errinfo
end
-------------------------------------------------------------
You can then use @m_errinfo for logging/tracking
Hope this helps
November 12, 2007 at 3:58 pm
Thanks Rajesh,
From your input and some others I ended up altering the stored proc to use an OUTPUT parameter. Here is the resulting proc:
CREATE PROCEDURE udsp_OutputOAErrorInfo
@Object int,
@HResult int,
@Output nvarchar(255) OUTPUT
AS
DECLARE @HRHex nchar(10);
DECLARE @HR int;
DECLARE @Source nvarchar(255);
DECLARE @Description nvarchar(255);
SET @Output = 'OLE Automation Error Information;';
EXEC sp_HexToChar @HResult, @HRHex OUT;
SET @Output = @Output + ' HRESULT: ' + @HRHex;
EXEC @HR = sp_OAGetErrorInfo
@Object,
@Source OUT,
@Description OUT;
IF @HR = 0
BEGIN
SET @Output = @Output + '; Source: ' + @Source;
SET @Output = @Output + '; Description: '
+ @Description;
END
ELSE
BEGIN
PRINT N' sp_OAGetErrorInfo failed.';
RETURN;
END
GO
November 12, 2007 at 11:57 pm
ok.
There is still a print statement in the code
PRINT N' sp_OAGetErrorInfo failed.';
You need to put this one also into @output variable, otherwise, if there is a error calling oageterrorinfo, you will not get it in the calling procedure
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply