PRINT 'Creating temporary stored procedure with error messages and print messages.'
if object_id('tempdb..##test_err_sp') is not null
        drop proc ##test_err_sp
go
Create proc ##test_err_sp
AS
PRINT 'Print Error Message' RAISERROR('Error in stored procedure.',15,-1)
GO
DECLARE @object int, @hr int, @property varchar(255), @objResults int,
        @error_source varchar(255), @error varchar(255), @error_description varchar(255),
        @method_name varchar(255), @command_str varchar(255), @command_length varchar(10)

-- Create the SQL Server Object
-- For SQL Server 6.0/6.5, change SQLDMO.SQLServer to SQLOLE.SQLServer
EXEC @hr = sp_OACreate 'SQLDMO.SQLServer', @object OUT
IF @hr <> 0
BEGIN
    EXEC sp_OAGetErrorInfo @object, @error_source OUT, @error_description OUT
    PRINT @error_source
    PRINT @error_description
    RETURN
END
-- Call the Connect method.
EXEC @hr = sp_OAMethod @object, 'Connect', NULL, '(local)'
IF @hr <> 0
BEGIN
    EXEC sp_OAGetErrorInfo @object, @error_source OUT, @error_description OUT
    PRINT @error_source
    PRINT @error_description
    RETURN
END
-- Call ExecuteImmediate method here.
-- You can use other methods like ExecuteWithResults & ExecuteWithResultsAndMessages also.
-- I have not been successful in calling ExecuteWithResultsAndMessages correctly.
SELECT @command_str = '##test_err_sp'
SELECT @command_length = Str(DATALENGTH(@command_str)),
        @method_name = 'ExecuteImmediate("' + @command_str + '", 0, ' + @command_length + ')'
EXEC @hr = sp_OAMethod @object, @method_name, @objResults OUT
IF @hr <> 0
BEGIN
    EXEC sp_OAGetErrorInfo @object, @error_source OUT, @error_description OUT
    PRINT @error_source
    PRINT @error_description
    RETURN
END

This page was last updated on May 01, 2006 04:28 PM.