Writing SQL runtime/syntax errors to a text file-CodePudding

I want to generate a text file if there are syntax or runtime errors in a SQL stored procedure. For example, I want to create a text file with this information when the procedure is called and if the following error is generated:

Msg 8114, Level 16, State 5, Procedure sp_LoadKAD_UAT, Line 94 [Batch Start Line 94] Error converting data type varchar to float.

I am not using any other front-end tools. I need the solution in SQL server. I am using Microsoft SQL Server 2017

I have done research on TRY and catch blocks and @@Error but I have not got the solution yet.

CodePudding user response：

You can use the TRY and CATCH blocks to handle errors in a SQL stored procedure. The TRY block contains the code that might generate an error, and the CATCH block contains the code to handle any errors that occur in the TRY block.

To create a text file with the error information when an error occurs, you can use the xp_cmdshell extended stored procedure to run the command-line utility "echo" to write the error information to a text file.

Here is an example of how you can use TRY and CATCH blocks with xp_cmdshell to create a text file with the error information:

    BEGIN TRY
  -- code that might generate an error
END TRY
BEGIN CATCH
  DECLARE @ErrorMessage NVARCHAR(4000);
  DECLARE @ErrorSeverity INT;
  DECLARE @ErrorState INT;
  DECLARE @ErrorLine INT;
  DECLARE @ErrorNumber INT;
  DECLARE @ErrorProcedure NVARCHAR(200);
  DECLARE @ErrorLineNumber INT;

  SELECT 
      @ErrorLineNumber = ERROR_LINE(),
      @ErrorProcedure = ERROR_PROCEDURE(),
      @ErrorNumber = ERROR_NUMBER(),
      @ErrorLine = ERROR_LINE(),
      @ErrorSeverity = ERROR_SEVERITY(),
      @ErrorState = ERROR_STATE(),
      @ErrorMessage = ERROR_MESSAGE();

  -- create the text file with the error information
  EXEC xp_cmdshell 'echo Error Number: '   CAST(@ErrorNumber AS NVARCHAR(10))   ', Error Message: '   @ErrorMessage   ', Error Procedure: '   @ErrorProcedure   ', Error Line Number: '   CAST(@ErrorLineNumber AS NVARCHAR(10))   '>> C:\errors.txt';
END CATCH

This example captures the error information and writes it to a text file named errors.txt located in C drive. You can change the location of the text file as per your requirement.

Note that xp_cmdshell is a SQL server extended stored procedure that allows you to run command prompt commands from within SQL Server, so you may need to enable it on your SQL Server 2017 instance by executing the following command:

sp_configure 'show advanced options', 1;  
GO  
RECONFIGURE;  
GO  
sp_configure 'xp_cmdshell', 1;  
GO  
RECONFIGURE;  
GO

CodePudding user response：

Using Try Catch block you can handle the error message and insert into any Temporary table.

Declare @Catcherror as Table (Errornumber int, Errormessage varchar(100))

BEGIN TRY 
    Declare @num1 as int=1, @num2 as int = 0, @result as int
    set @result= @num1/@num2

end TRY
BEGIN CATCH
    insert into @Catcherror (Errornumber, Errormessage)
    Select ERROR_NUMBER() as Errornumber, ERROR_MESSAGE() as Errormessage
end CATCH

Select * from @Catcherror