rachelbr 87580 (9/2/2012)
Hi,I have this stored proc that I run nightly. I keep on having issues with it failing because it's having issues converting a nvarchar. This is the exact error I'm getting:
Error converting data type nvarchar to numeric
...
On line 1553 of theat stored procedure you have a BEGIN CATCH statement. The first statement(s) within the block should capture the error information, something like this:
BEGIN CATCH
-- Capture error variables before running any other statements
SELECT @EventText =
'The procedure [' + ERROR_PROCEDURE() + '] in [' + OBJECT_NAME(@@PROCID) + '] '
+ 'failed with error message "' + ERROR_MESSAGE() + '" '
+ 'at line ' + CAST(ERROR_LINE() AS VARCHAR(10)) + '. '
+ 'Errornumber = ' + CAST(ERROR_NUMBER() AS VARCHAR(10)) + ', '
+ 'Errorseverity = ' + CAST(ERROR_SEVERITY() AS VARCHAR(10)) + ', '
+ 'Errorstate = ' + CAST(ERROR_STATE() AS VARCHAR(10));
- which can help a lot with debugging.
Next thing if you still can't identify the statement causing the problem, is to open the sproc for editing in a SSMS window, comment out the CREATE PROC statement, any RETURNs and the error-trapping statements, and run it.
Post the offending statement here - the rest should be easy.
Edit - having said all that, you should be able to work out which statement it is from the logging table. Can you tell us?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden