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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy