March 26, 2020 at 10:02 am
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
March 26, 2020 at 11:37 am
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-2 int) as
BEGIN TRY;
SELECT @i / @j-2;
END TRY
BEGIN CATCH;
THROW;
END CATCH;
GO
CREATE OR ALTER PROCEDURE MyOuterDemoProc (@i INT, @j-2 INT)
AS
BEGIN TRY;
EXEC MyInnerDemoProc @i, @j-2
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
March 26, 2020 at 1:06 pm
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
March 26, 2020 at 1:55 pm
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.
March 26, 2020 at 4:11 pm
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!
March 31, 2020 at 8:58 am
Run the stored procs individually ?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply