CODEWITHSUNDEEP
sql-server-2008ssissql-server-2012ssis-2008
dinesh kalva
dinesh kalva

Reputation: 11

Data Truncation error in stored procedure but SSIS package not failing

I'm having stored procedure: usp_data. If I run in SSMS, am not getting any error but if I remove being and end trans then am I'm getting data truncation error. I'm using SSIS package data flow task to run this stored procedure. Job is going thru and not failing. What needs to be done to fix this SP and SSIS package. Which one needs to fix? I have put dummy fields and table name. Server : MS SQL Appreciate your help.

 create procedure usp_data as 
   begin
     begin trans
      begin try
        insert into table1 (field1, field2)
           select field1,field2 from table2
      commit trans
      return
      end try
begin catch
 if @@transcount>0
   begin
     rollback trans
   end
   set @err = ERROR_MESSAGE()
   RAISERROR (@err,-1,-1,'usp_data')
   print(ERROR_MESSAGE())
   RETURN -1
  END Catch
 END

Upvotes: 0

Views: 280

Answers (1)

Dan Guzman
Dan Guzman

Reputation: 46193

An error severity of 11 or higher is needed in order for the error message to be considered an exception. A severity of 10 or less are considered informational/warning messages and do not throw an exception in consuming applications.

You can observe this in SSMS with the following:

--this is informational message
RAISERROR ('example %s',-1,-1,'usp_data');
--this is an error message
RAISERROR ('example %s',16,-1,'usp_data');

In SQL Server 2012 and later, consider using THROW to re-raise the original error. Below is the boiler plate code I suggest. Additionally, it's a good practice to specify SET XACT_ABORT ON; in stored procedures with explict transactions to ensure the transaction is rolled back immediately when a client query timeout occurs.

BEGIN CATCH
    IF @@transcount > 0 ROLLBACK;
    THROW;
END CATCH;

Upvotes: 0

Related Questions