Small and medium business | Business Central, N...

SQL script that allows me to find out which job queue run into an error

(0) Share

Report

Posted on by Maddin

Hello guys,

I would like to know if anyone of you has a SQL script that allows me to find out which job queue in which of my clients has just run into an error?

I use NAV 2017 and have 15 clients there.

It happens from time to time that a job queue runs into an error.

When this happens, I always have to go to all the clients and see where the error occurred.

Unfortunately, this is very time-consuming.

Do you use a script for this?

Thank you very much!

I have the same question (0)

All responses (5)

Answers (0)

Suggested answer

Gearoid on at

Like (2)

Report

Hi Maddin,

Maybe this can help as a starting point:

SELECT TOP (1000) [timestamp]

,[User ID]

,[Start Date_Time]

,[End Date_Time]

,[Object Type to Run]

,[Object ID to Run]

,[Status]

,[Description]

,[Error Message]

,[Processed by User ID]

FROM [dbo].[CRONUS International Ltd_$Job Queue Log Entry]

where Status <> 0

Was this reply helpful? Yes No
Suggested answer

YUN ZHU 95,712 Super User 2025 Season 2 on at

Like (1)

Report

Hi, Gearóid gives a good example of this. Just to add a little bit of information, you can find the specific table by the following method and then query it according to the conditions.

Hope this helps as well.

Thanks.

ZHU

Was this reply helpful? Yes No
Maddin 30 on at

Like (0)

Report

Thank you very much for your great support ;) I have now adapted the script a little and so far I like the result very much.

Do you know if it is possible to combine the tables of the different companies behind the FROM somehow?

For example, I have two companies TEST and REAL.

Now I would formulate the statement for TEST and REAL like this:

FROM [dbo].[TEST$Job Queue Log Entry] or

FROM [dbo].[REAL$Job Queue Log Entry]

But I get an error message when I try to do this:

FROM [dbo].[TEST$Job Queue Log Entry], [dbo].[REAL$Job Queue Log Entry].

With this I hope that I only have to run one script for all my companies and not one script per company.

Is this possible?

Was this reply helpful? Yes No
KTA 1,200 on at

Like (0)

Report

it should be. You can declare a text variable and set it with they name of the company in a loop in companies table, then use than variable in the from

Was this reply helpful? Yes No
Suggested answer

Gearoid on at

Like (0)

Report

Hi Maddin,

You could also consider using the TSQL "UNION ALL" operator to combine the results from your TEST and REAL JQE tables into a single result set, e.g:

SELECT TOP (1000) [timestamp]
,'TEST' as [JQ Name]
,[User ID]
,[Start Date_Time]
,[End Date_Time]
,[Object Type to Run]
,[Object ID to Run]
,[Status]
,[Description]
,[Error Message]
,[Processed by User ID]
FROM [dbo].[TEST$Job Queue Log Entry]
union all
SELECT TOP (1000) [timestamp]
,'REAL' as [JQ Name]
,[User ID]
,[Start Date_Time]
,[End Date_Time]
,[Object Type to Run]
,[Object ID to Run]
,[Status]
,[Description]
,[Error Message]
,[Processed by User ID]
FROM [dbo].[REAL$Job Queue Log Entry]

Was this reply helpful? Yes No