SSRS Report Upgrade issue with # Temp tables for 800+ lines of Query

Question

SSRS Report Upgrade issue with # Temp tables for 800+ lines of Query

ralahari

Old Hand

Points: 301
More actions
September 30, 2023 at 9:08 pm

#4298616

one of the Report which calls two Data sets ,
Data set1 Contains , 8 # temp Tables (#1,#2,#3 ) etc.
Dataset2 Contains , 8 # temp Tables (#1,#2,#3 ) etc.
Note: Both Datasets # TEmp Table Names are Same, Please let us know which is best way to replace #temptables.
I am getting Below Error, either in Dataset1 or Dataset2.
We Upgraded Sql Server to 2019, SSRS Server to 2014 from 2008
Microsoft.ReportingServices.ReportProcessing.ReportProcessingException:
Query execution failed for dataset 'Dataset2'. --->
System.Data.SqlClient.SqlException: A severe error occurred on the current command.
The results, if any, should be discarded.
Operation cancelled by user.
processing!ReportServer_0-1!2580!09/27/2023-15:18:54:: i INFO: Some other thread has already aborted processing.
processing!ReportServer_0-1!2f9c!09/27/2023-15:18:54:: e ERROR:
Throwing Microsoft.ReportingServices.ReportProcessing.ProcessingAbortedException:
[AbnormalTermination:ReportProcessing],
Microsoft.ReportingServices.ReportProcessing.ProcessingAbortedException:
An error has occurred during report processing. ---> Microsoft.ReportingServices.ReportProcessing.ReportProcessingException:
Query execution failed for dataset 'Dataset1'. ---> System.Data.SqlClient.SqlException:
The query processor ran out of internal resources and could not produce a query plan.
This is a rare event and only expected for extremely complex queries or queries that reference a very large number of
tables or partitions. Please simplify the query. If you believe you have received this message in error,
contact Customer Support Services for more information.
When we run query in SSMS Query is taking MAX 1 Min:30 SEC,
But report level we are getting above error.
Any suggestions highly appreciated.
October 3, 2023 at 11:47 pm

This was removed by the editor as SPAM

Viewing 6 posts - 1 through 5 (of 5 total)

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

Phil Parkin SSC Guru Points: 246938 More actions · Answer 1

One idea is to put your dataset queries into stored procs and call those from your report.

My preference is to keep complex business logic such as this within the database. It makes maintenance easier and may run faster.

If you are concerned about there being a clash because of temp table names being the same, change the names – it won't affect the output!

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

ralahari Old Hand Points: 301 More actions · Answer 2

Thanks for the Response!

I will implement your suggestions.

https://www.mssqltips.com/sqlservertip/5279/sql-server-error-query-processor-ran-out-of-internal-resources-and-could-not-produce-a-query-plan/

after Upgradation we got above error (PREV Post) is below solutions will help us to resolve error.

Please Guide me on this.

If you find this error in SQL Server 2016 or later then try to change the database compatibility level to a lower version 120 or 110, etc.

There is also a database level scope configuration option available LEGACY_CARDINALITY_ESTIMATION. After enabling the Legacy CE, the query processor works as compatibility 110.

Jeff Moden SSC Guru Points: 1004280 More actions · Answer 3

ralahari wrote:

Thanks for the Response!
I will implement your suggestions.
https://www.mssqltips.com/sqlservertip/5279/sql-server-error-query-processor-ran-out-of-internal-resources-and-could-not-produce-a-query-plan/
after Upgradation we got above error (PREV Post) is below solutions will help us to resolve error.
Please Guide me on this.
If you find this error in SQL Server 2016 or later then try to change the database compatibility level to a lower version 120 or 110, etc. There is also a database level scope configuration option available LEGACY_CARDINALITY_ESTIMATION. After enabling the Legacy CE, the query processor works as compatibility 110.

I would first try changing the Legacy Cardinality Estimator without changing the database compatibility level to a lower version. Only do the latter if nothing else works with the understanding that you really need to find and fix the bad code because some of the new functionality in 2022 is definitely worth it.

As a bit of a sidebar, we're having some massive performance issues after we went from 2016 to 2022 even on super simple queries that run nasty fast in 2016/2017 but are a lot slower in 2022 even with all the "special features" turned off and just about every configuration between that and having everything enabled. 🙁

--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)

Jeff Moden SSC Guru Points: 1004280 More actions · Answer 4

ralahari wrote:

The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information.
When we run query in SSMS Query is taking MAX 1 Min:30 SEC, But report level we are getting above error. Any suggestions highly appreciated.

I think that's the key to your problem and it strongly supports Phil's recommendation.

--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)