I have a SQL Server Agent Job that executes an overly complex stored procedures in 2014. This store procedure will execute multiple store procedures within it. There is one stored procedure that been throwing errors and causing the job to stop prematurely.
I have a few problems with this and I don't know how to get around it.
What I know.. I know the stored procedure that is failing.
Issues I am having..
1) The stored procedure is wrapped in a Try/Catch. I don't think the Catch is being called.
2) There are number of SQL statements like ... exec sp_executesql
3) I added a number of Print statements. To help me figure out why it would be throwing an error.
a. All I get with this is failures at different points.
Examples of what i am seeing...
[SQLSTATE 01000] (Message 0) @ReportedAddressPoint
[SQLSTATE 01000] (Message 0) @CustomerCallLogPK
[SQLSTATE 0100... The step failed.
Original code for this section...
Print('@ReportedAddressPoint')
Print (@ReportedAddressPoint) ---> SInce I did not see any value here I am assuming this is NULL
Print('@CustomerCallLogPK')
Print (@CustomerCallLogPK) ---> SInce I did not see any value here I am assuming this is NULL
Print('@CustomerName') ---> This did not even show up. So am I assuming this failed on Print ('xxxxxx') ?????????
Print (@CustomerName)
When the stored procedure failed again, it was in a different section of the code.
I am confused. What other debugging techniques can I use to narrow down what is going. I am going to wager the issue is data. Since it is SO inconsistent on where it fails I am hoping it is not multiple data points.