January 19, 2009 at 6:41 am
Hello All,
Environment
=========
Server: Windows 2000 Server SP4
SQL: Microsoft SQL Server 2000 - 8.00.760
I have query that contains a number of sub-queries and also performs a SUM.
At the end of the query is a HAVING SUM > 0 so that the query only return results with sum > 0.
If I run the query from SQL Server Enterprise I receive the error:
Msg 8624, Level 16, State 21, Line 1
Internal SQL Server error.
However, if I comment out the HAVING SUM > 0 clause, the query runs without error.
We have a SQL 2005 version of the database as we plan to upgrade at a future date and are testing the application functionality.
With the HAVING SUM > 0 clause uncommented, the query runs without error on the SQL 2005 database.
Could anyone suggest a solution please?
Many Thanks.
Julian
January 19, 2009 at 7:15 am
Can you post the query please?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
January 19, 2009 at 7:31 am
Hello Jack,
Yes, please find attached the query.
Since I created the original post, I have also discovered that if I uncomment the HAVING clause and comment out the call to the scalar function (for which I've also attached the code), the query runs without error 🙁
Many Thanks
Julian
January 19, 2009 at 8:34 am
Wow, there's a lot going on there, the first thing I did was eliminate thee first 3 derived tables (the select *'s without any where clauses).
There isn't anything that jumps out at me that would cause the error though. Anything in the SQL Server event logs?
Could you explain what's the purpose of the query is? I think there must be a simpler way.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
January 19, 2009 at 8:54 am
Hello Jack,
Yeah - there sure is a lot going on in the query 🙂
The way the data is structured in the database means that to go from the first 3 tables to the others results in a lot of LO Joins.
Why the reason for so many Derived Tables?? Well, as you can see from the code, the table names on the database contain ***. The query is the SQL from a BusinessObjects report. When building the Universe, I was unable to add the tables to the Universe directly because of the tablenames containing *** and hence created Derived Tables.
Unfortunately, other than put things into Temp Tables to avoid having all the sub-queries, there is no simpler way to construct the query based on the structure of the data that is being reported from.
I agree with you that there is nothing in the code of either the function or the report that would cause this error and its got me extremely baffled.
The report works across an ODBC/OLEDB connection without error, but is now returning this internal error that I am getting when running in SQL Server Management Studio.
As mentioned in my previous post, I am only getting this error on the SQL Server 2000 database and not on the SQL Server 2005 database. The strange thing is is that I HAVE run this code in SQL Server Management Studio on the SQL Server 2000 database previously yet have been ensured by the DBA for the particular application that nothing has changed.
I have checked through the Database History Logs and also the Application Event Logs on the database server but can find nothing that indicates this error occurring 🙁
We cannot yet use the SQL Server 2005 database since this will be part of a business application upgrade which will scheduled for a future date.
Any suggestions??
Cheers
Julian
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply