Finding error in nested stored procedure

  • pwalter83

    SSChampion

    Points: 14548

    Hi,

    Hope everyone is keeping safe from covid-19.

    I need to find which particular procedure is giving an error in the nested stored procedure.

    Also, as the log table in the database does not have a separate column for error message, I am unable to go any further.

    The error I get is -

    String or binary data would be truncated. The statement has been terminated. Warning: Null value is eliminated by an aggregate or other SET operation. The step failed.

    Can anybody please help how can I find which sub procedure is giving this error. Please find the main SP below.

    Many thanks.

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO

    ALTER PROCEDURE [dbo].[usp_PopulateDimensions]

    AS

    SET NOCOUNT ON;

    BEGIN


    DECLARE @LogScope INT
    BEGIN TRY
    EXEC [dbo].[LogEvent]
    @ObjectId = @@PROCID,
    @LogEntryId = @LogScope OUTPUT
    END TRY
    BEGIN CATCH
    PRINT 'Do nothing.'
    END CATCH

    --01
    EXEC [dbo].[usp_PopulateDimSeason]

    --02
    EXEC [dbo].[usp_PopulateDimCurrency]

    --03
    EXEC [dbo].[usp_PopulateDimFXRate]

    --04
    EXEC [dbo].[usp_PopulateDimChannel]

    --06
    EXEC [dbo].[usp_PopulateDimGeography]

    --07
    EXEC [dbo].[usp_PopulateDimSupplierGroup]

    --08
    EXEC [dbo].[usp_PopulateDimSupplier]

    --09
    EXEC [dbo].[usp_PopulateDimTime]

    --10
    EXEC [dbo].[usp_PopulateDimStatus]

    --11
    EXEC [dbo].[usp_PopulateDimPriceList]

    --12
    EXEC [dbo].[usp_PopulateDimCustomerType]

    --13
    EXEC [dbo].[usp_PopulateDimEmployee]

    --14
    EXEC [dbo].[usp_PopulateDimDiscount]

    --15
    EXEC [dbo].[usp_PopulateDimPaymentMethod]

    --16
    EXEC [dbo].[usp_PopulateDimWarehouse]

    --17
    EXEC [dbo].[usp_PopulateDimAirport]
    @Rebuild = 'Y' --always rebuild

    --18
    EXEC [dbo].[usp_PopulateDimAirline]
    @Rebuild = 'Y' --always rebuild

    --19
    EXEC [dbo].[usp_PopulateDimFlight]

    --20
    EXEC [dbo].[usp_PopulateDimReturnReason]

    --21
    EXEC [dbo].[usp_PopulateDimPettyCashType]

    --22
    EXEC [dbo].[usp_PopulateDimStockStatus]

    --23
    EXEC [dbo].[usp_PopulateDimStoreSpace]
    @Rebuild = 'Y' --always rebuild

    --24
    EXEC [dbo].[usp_PopulateDimBudgetArea]

    --25
    EXEC [dbo].[usp_PopulateDimCancellationReason]

    --26
    EXEC [dbo].[usp_PopulateDimDespatchType]

    --27
    EXEC [dbo].[usp_PopulateDimCustomers]

    --28
    EXEC [dbo].[usp_PopulateDimDepartment]

    --29
    EXEC [dbo].[usp_PopulateDimProductGroup]

    --30
    EXEC [dbo].[usp_PopulateDimProductSubGroup]

    --31
    EXEC [dbo].[usp_PopulateDimProductType]

    --32
    EXEC [dbo].[usp_PopulateDimProductGroupNo]

    --33
    EXEC [dbo].[usp_PopulateDimCountryOfOrigin]

    --34
    EXEC [dbo].[usp_PopulateDimProduct]

    --35
    EXEC [dbo].[usp_PopulateDimProductCategory]

    --36
    EXEC [dbo].[usp_PopulateDimProductSubCategory]

    --37
    EXEC [dbo].[usp_PopulateDimProductSubSubCategory]

    -- 38
    EXEC [dbo].[usp_PopulateDimSSASPartitions]


    BEGIN TRY
    EXEC [dbo].[LogEvent]
    @PriorLogId = @LogScope
    END TRY
    BEGIN CATCH
    PRINT 'Do nothing.'
    END CATCH

    END







     

     

     

    • This topic was modified 1 week, 2 days ago by  pwalter83.
  • Adi Cohn

    Old Hand

    Points: 303

    The catch section hides the error message.  If you'll add throw to it, it will send you the full error message that includes the place that the error occurred.  Check the small demo code and play with it.

    CREATE OR ALTER PROC MyInnerDemoProc (@i int, @j int) as

    BEGIN TRY;

    SELECT @i / @j;

    END TRY

    BEGIN CATCH;

    THROW;

    END CATCH;

    GO

    CREATE OR ALTER PROCEDURE MyOuterDemoProc (@i INT, @j INT)

    AS

    BEGIN TRY;

    EXEC MyInnerDemoProc @i, @j

    END TRY

    BEGIN CATCH;

    SELECT 'oh boy, something went wrong';

    --remove the remark so the throw statement will work

    --THROW;

    END CATCH;

    GO

    exec MyOuterDemoProc 1,0

    • This reply was modified 1 week, 2 days ago by  Adi Cohn.
  • pwalter83

    SSChampion

    Points: 14548

    Thanks, I have tried using Throw as you suggested buts its still showing the same error comment without mentioning the exact stored procedure which is giving the error

  • Adi Cohn

    Old Hand

    Points: 303

    You need to write throw also in the inner procedures.  If they have a catch block without throw in it, you won't see the error.  Also I don't know how you run the procedure.  If you run it in an application, then depending the error handling in the application you'll might get the technical information (in a log or in the GUI) and might not, but if you'll run it in SSMS then you should see the location of the error.

  • Jacob Wilkins

    One Orange Chip

    Points: 27880

    Another way, probably easier, is to set up an extended events session collecting the error_reported event, filtered to that error number, and add the tsql frame or tsql stack actions.

    Either of those will give you the sql handle, line, start offset, and end offset for the innermost call (with tsql stack giving you all the parent calls as well).

     

    Cheers!

  • hannes.malan

    SSC Veteran

    Points: 261

    Run the stored procs individually ?

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply