Execution of SP

Question

Execution of SP

sqlguru

SSC-Addicted

Points: 471
More actions
December 21, 2022 at 7:08 pm

#4128588

One of the store proc failed as part of schedule job which is not a SQL agent job it is a part of nightly batch job. However, the logs from the batch job shows it failed with incorrect syntax. I don't have parameters to plug in and reproduce the issue. Is there a way to find which part of the query failed by looking at some kind of logs? I did not find any entry in the logs for this particular SP and also looked at monitoring tool nothing I could find. Please advise?

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Jeff Fant SSC Rookie Points: 38 More actions · Answer 1

Do you know for a fact which stored procedure the batch file is calling? If so, you could add some logging to that procedure and then rigorously test the procedure. I am curious too if it is failing every time it runs or just failed once.

sqlguru SSC-Addicted Points: 471 More actions · Answer 2

Yes. That got captured in the batch log. Are you referring to logging and error handline something like TRY/CATCH blocks?

Jeff Fant SSC Rookie Points: 38 More actions · Answer 3

That would work if you can zero in on some suspected places. Also, do you have good third party monitoring software?

sqlguru SSC-Addicted Points: 471 More actions · Answer 4

So dedicated table needs to be created to capture the error handling? We do have third party monitoring but it didn't capture that particular proc as database activity.

Jeff Fant SSC Rookie Points: 38 More actions · Answer 5

I used to like SQL Diagnostic Manager by Idera for monitoring. It kept more history than the basic logging would give. Perhaps you can check into what your monitoring software has a record of. Otherwise, yes, you would be adding try-catch or some sort of output and dropping it in a table or raising an event or something for next time it happens (or during your testing of the procedure).

cyrusbaratt SSC-Addicted Points: 417 More actions · Answer 6

You may use SQL server profiler to see where your job is failing. Sometimes, the windows event viewer - application can help. You may use a debugging SQL server table to find the failure location using the t-sql insert statements. Please review the SQL server error logs and the SQL agent job history log.

DBASupport

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 7

I would ask an "sqlguru". 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 8

Jeff Moden wrote:

I would ask an "sqlguru". 😉

🙂

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs