0

I am facing a weird problem and I would be glad if anyone can help me.

In our company we use SSRS to send daily Reports to the Same Management.

We Have 3 Types of Reports, which of 3 is executed more times with different parameters. The first it is executed 2 times, the second 3 times and the third, which is the heavier and is falling 6 times.

All the 3 queries have the same philosophy using CTE tables. The queries developed in SSMS and added to the Report via Visual Studio.

And here starts the madness.

In the first edition we have them at the same schedule. Two of them are executed with no error and the emails were delivered.

The third report is falling with error

Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Query execution failed for dataset 'ZZZ'. ---> System.Data.SqlClient.SqlException: Invalid object name 'tempdb.dbo.TEMP_ID_XXXX'.

When we disable the other 2 reports and executed the 3rd manually the report completed successfully.

But this only some days, some other days all the reports delivered successfully.

Then we decided to split the schedule of these 3 reports. So, we create 2 job steps. One for the first two and one for the third report.

The first two days the reports delivered as we want, but from yesterday we are facing the same issue.

As I saw the job steps have a time difference some milliseconds although the reports of the first step are taking around 15 seconds to complete.

I checked the Execution log and saw that for the one parameter version Execution of the third report it takes:

  • 12990ms when it is executed manually through the portal.

  • 23845ms when it is executed as a job (Parallel with the other 5 parameters execution)

  • 35761ms when it is executed parallel with all the other reports (11 in total) Failed

I thought that maybe if adding and extra step between the 2 reports step that will pause job for 1 minute will fix my problem.

Has anyone facing any similar issue?

Any help would be appreciated!

Serg
  • 2,346
  • 3
  • 29
  • 38
Gio
  • 1
  • 2
  • The reason for the failure is because a table you're created, in `tempdb` doesn't exist. Perhaps you're referencing the wrong database, as it's quite uncommon to create a table in `tempdb` (apart from proper temp tables, which are prefixed with a `#`). – Thom A Mar 18 '19 at 09:54
  • Hello, thank you for the answer. I don't know if the table is one of the CTEs, but it is very strange that the same query is executed again without any problem. – Gio Mar 18 '19 at 10:03
  • Maybe you're referencing the object before creating it. Impossible to know without the SQL you're running. – Thom A Mar 18 '19 at 10:04
  • And my question is why the same exactly query is running with no problem??? I don't know if the tempdb that provides the error is the tempdb of the ReportServer or the tempdb in the Database that the query is executed (Azure DataWarehouse). A CTE is created as a table in tempdb? – Gio Mar 18 '19 at 10:09
  • No, a CT**E** is an Common Table **E**xpression; it doesn't create an object. A CTE is more like a `CASE` expression than it is a `CREATE` statement. – Thom A Mar 18 '19 at 10:13
  • This is why i cannot understand the error. Is it possible when the schedule for the 2 first reports finishes, to finalize the connection and so the third report fails? – Gio Mar 18 '19 at 10:30
  • Clearly, in the report that is failing, you are referencing the object `tempdb.dbo.TEMP_ID_XXXX` and that object doesn't exist. Where or how you referencing that object I have no idea. We cannot access or see what you see. – Thom A Mar 18 '19 at 10:37
  • Hello to everyone. I decided to add one step between the steps for 2 reports that pauses the job for 2 minutes. So when the Session for Report 3 starts the previous session for the other 2 reports have finished. For 2 consecutive days all the reports delivered successfully. This actions seems to solve my issue. Thank you. – Gio Mar 20 '19 at 14:18

0 Answers0