SQLTeam.com | Weblogs | Forums

Finding error in nested stored procedure

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

The simplest way to hack that is add a print statement
PRINT 'Procedure Name'; after each procedure call and run the parent proc manually in ssms. If you dont see a proc name, you know that is the one that probably errors

2 Likes