SQL 2014 Stored Proceudre Error(s)

Question

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.

Answer 1

Hi @Abby Palcer ,

Please refer to this: https://stackoverflow.com/questions/52121574/working-stored-procedure-fails-when-called-from-sql-agent-job,
which may has a similar situation.

Best regards,
Seeya

---

If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar queries.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Since this is a SQL Agent Job, the job history only contains the first 1000 chars of the output. You need to set your step to log to a file or table to get the complete output on the "Advanced" tab of your task. That should be your first step.

https://learn.microsoft.com/en-us/sql/ssms/agent/job-step-properties-new-job-step-advanced-page?view=sql-server-ver15

Answer 3

Those are not error statements, but the way SQL Agent Jobs report PRINT statements.

What exactly is the error you are receiving?