Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Internal Error: Services limit has been reached Expand / Collapse
Author
Message
Posted Monday, October 15, 2012 7:06 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, December 20, 2013 1:32 AM
Points: 92, Visits: 189
Has anyone had this issue before.

I have a report which has a basic query. It selects several columns from 2 tables.

It has been working fine, but since last week the users get the below error.

"Query Execution failed, Internal Error: An expression services limit has been reached. Please look for potentially complex expressions in your query and try to simplfy them"

Im using visual studio 2008. There are 6 parameters on this report one of which is a date and in the 'where' clause there is a filter to convert to a date format as it is a different format to the date picker.

Other than that I cant see what would be so complex. I did a test by removing one of the parameters and the report worked fine!

Any advise please?

Thanks
Rkaria
Post #1372713
Posted Monday, October 15, 2012 7:51 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, January 07, 2014 2:21 PM
Points: 132, Visits: 557
Which parameter did you remove ?

The best thing to do here is, Run SQL Server Profiler to get the exact query with parameters being passed to the database engine.

And run that query on SSMS to check whether it runs fine or not.


BI Developer
SSRS, SSIS, SSAS, IBM Cognos, IBM Infosphere Cubing services, Crystal reports, IBM DB2, SQL Server, T-SQL
Please visit... ApplyBI
Post #1372736
Posted Monday, October 15, 2012 8:34 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, December 20, 2013 1:32 AM
Points: 92, Visits: 189
One with a list of users.

I have found the issue, it seems to be because of having several IN Clause's.

There are 5 parameter that use IN clauses. When I removed one of them it works. I think the limit is 2100 in the list

Not sure how to get round this.

I have ran the query in Management studio and it works fine.
Post #1372760
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse