SQL 2000 Internal SQL Server Error 1

[Chopstik]

My error message is

Server: Msg 8624, Level 16, State 3, Line 1
Internal SQL Server error.

I get it when I attempt to run a view that I have created. The code in the view works fine independently but not inside the view itself. I will be using this with another view later to create a new set of results once I get past this error message. My initial research has indicated that a temp table may work better than a derived table but I'm drawing a mental blank... I think lack of sleep is beginning to do me in... Any suggestions are greatly appreciated. (I am using SQL Server 2000 and I know there is a hotfix to correct the error but our server admin group will not allow our DBA's to implement it and I can't convince our DBA's to move our DB to 2005.) Here is the code:

CREATE VIEW dbo.V_ALC_TOTAL_CO_VALUES_PT2
AS
SELECT TOP 100 PERCENT P.co_cd, P.cst_ctr_cd, (SUM(E.jan_bal + E.feb_bal + E.mar_bal + E.apr_bal + E.may_bal + E.june_bal + 
E.jly_bal + E.aug_bal + E.sept_bal + E.oct_bal + E.nov_bal + E.dec_bal) / 
	(SELECT COUNT(*) FROM T_PRPTY P1 JOIN V_CURR_STAT S1 ON P1.prpty_id = S1.prpty_id AND S1.stat_ty_cd <> 'Inactive'
	WHERE P1.co_cd = P.co_cd AND P1.cst_ctr_cd = P.cst_ctr_cd)) AS Budget
FROM T_PRPTY P JOIN T_EXPNS E ON P.co_cd = E.co_cd AND P.cst_ctr_cd = E.cst_ctr_cd
INNER JOIN V_CURR_STAT S ON P.prpty_id = S.prpty_id AND S.stat_ty_cd <> 'Inactive'
WHERE E.expns_ty = 'b' AND E.expns_yr = 2008 AND (E.acct_cd LIKE '8%' OR E.acct_cd LIKE '9%')
AND P.co_cd + P.cst_ctr_cd IN ([COLOR=green]--There is a list too long to put here[/color])
GROUP BY P.co_cd, P.cst_ctr_cd

------------------------------------------------------------------------------------------------------------------------
"Men occasionally stumble over the truth, but most of them pick themselves up and hurry off as if nothing ever happened."
- Winston Churchill

 

[Crowley16]

this was the first link returned when doing a google search on "error 8624"

http://support.microsoft.com/kb/290817

--------------------
Procrastinate Now!

 

[gmmastros]

And this was the 2nd or 3rd.

thread183-1159898

My suggestion would be to convert the sub-queries to derived tables. I suspect the error will go away AND it will probably run faster too.

Let me know if you need some help converting it.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

I think this query is functionally equivalent to yours. The difference is that I am using a derived table and your query uses a sub-query.

[COLOR=blue]CREATE[/color] [COLOR=blue]VIEW[/color] dbo.V_ALC_TOTAL_CO_VALUES_PT2
[COLOR=blue]AS[/color]
[COLOR=blue]SELECT[/color]  TOP 100 PERCENT 
        P.co_cd, 
        P.cst_ctr_cd, 
        (SUM(E.jan_bal + E.feb_bal + E.mar_bal + E.apr_bal + E.may_bal + E.june_bal + E.jly_bal + E.aug_bal + E.sept_bal + E.oct_bal + E.nov_bal + E.dec_bal) /
        (SomeAlias.SomeCount)) [COLOR=blue]AS[/color] Budget
[COLOR=blue]FROM[/color]    T_PRPTY P 
        [COLOR=blue]JOIN[/color] T_EXPNS E 
          [COLOR=blue]ON[/color] P.co_cd = E.co_cd 
          AND P.cst_ctr_cd = E.cst_ctr_cd
        [COLOR=blue]INNER[/color] [COLOR=blue]JOIN[/color] V_CURR_STAT S 
          [COLOR=blue]ON[/color] P.prpty_id = S.prpty_id 
          AND S.stat_ty_cd <> [COLOR=red]'Inactive'[/color]
        [COLOR=blue]INNER[/color] [COLOR=blue]JOIN[/color] (
          [COLOR=blue]SELECT[/color] co_cd, cst_ctr_cd, [COLOR=#FF00FF]COUNT[/color](*) [COLOR=blue]As[/color] SomeCount
          [COLOR=blue]FROM[/color]   T_PRPTY P1 
                 [COLOR=blue]JOIN[/color] V_CURR_STAT S1 
                   [COLOR=blue]ON[/color]  P1.prpty_id = S1.prpty_id 
                   AND S1.stat_ty_cd <> [COLOR=red]'Inactive'[/color]
          [COLOR=blue]GROUP[/color] [COLOR=blue]BY[/color] co_cd, cst_ctr_cd
          ) [COLOR=blue]As[/color] SomeAlias
            [COLOR=blue]On[/color]  P.co_cd = SomeAlias.co_cd
            And P.cst_ctr_cd = SomeAlias.cst_ctr_cd
[COLOR=blue]WHERE[/color]   E.expns_ty = [COLOR=red]'b'[/color] 
        AND E.expns_yr = 2008 
        AND (E.acct_cd LIKE [COLOR=red]'8%'[/color] OR E.acct_cd LIKE [COLOR=red]'9%'[/color])
        AND P.co_cd + P.cst_ctr_cd IN ([COLOR=green]--There is a list too long to put here)
[/color][COLOR=blue]GROUP[/color] [COLOR=blue]BY[/color] P.co_cd, P.cst_ctr_cd

I encourage you to try this. Hopefully, the error message will go away and this query will actually return the same data. Of course, if you have any problems with it, let me know.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SQLDenis]

I ran into this problem when using functions buried inside views. The error I got was "database id 6 can not be found"

anyway it looks like the optimizer chokes on all the subqueries, rewrite this beast

SELECT TOP 100 PERCENT and order by does not do anything on SQL 2005, don't rely on it to work on 2005, there are hacls like 99.99% or top 2 billion but I would just do an order when selecting from the view not in the view itself

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[Chopstik]

Crowley, I had already tried the workaround in that article and it had not worked. Thanks for the suggestion, though.

George, thanks for both pointing out that I'm obviously an idiot for not looking back through my own threads here and for your help with the derived tables. However, it still gives me the internal SQL Server error...

Denis, I think you're right and I'm just going to have to re-write this beastie... Due to tight time constraints at the moment, I'm trying another (longer) workaround and then I'll have another go at a re-write. When/if I get a better resolution, I'll try to post it.

Thanks for the help!

------------------------------------------------------------------------------------------------------------------------
"Men occasionally stumble over the truth, but most of them pick themselves up and hurry off as if nothing ever happened."
- Winston Churchill