question

AbbyPalcer-4041 avatar image
0 Votes"
AbbyPalcer-4041 asked SeeyaXi-msft commented

SQL 2014 Stored Proceudre Error(s)

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.




sql-server-general
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @AbbyPalcer-4041 ,

We have not received a response from you. Did the reply could help you? If the response helped, do "Accept Answer". If it doesn't work, please let us know the progress. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.

0 Votes 0 ·
TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered

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

What exactly is the error you are receiving?

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered TomPhillips-1744 edited

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://docs.microsoft.com/en-us/sql/ssms/agent/job-step-properties-new-job-step-advanced-page?view=sql-server-ver15

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

SeeyaXi-msft avatar image
0 Votes"
SeeyaXi-msft answered

Hi @AbbyPalcer-4041,

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.