Internal SQL Server Error

[Chopstik]

I have the following code that works fine when I test it in Query Analyzer:

SELECT TOP 100 PERCENT OH.prpty_id, OH.floor_cd, OH.co_cd AS OldCo, OH.cst_ctr_cd AS OldCC
FROM SNAP.dbo.T_OCCPT OH
	INNER JOIN SNAP.dbo.V_CURR_STAT S ON OH.prpty_id = S.prpty_id
	INNER JOIN SNAP.dbo.T_PRPTY P ON OH.prpty_id = P.prpty_id
	INNER JOIN SNAP.dbo.T_MTRC M ON P.co_cd = M.co_cd AND P.cst_ctr_cd = M.cst_ctr_cd AND M.mtrc_yr = '2007' 
	LEFT JOIN T_OCCPT O
	ON OH.prpty_id = O.prpty_id AND OH.floor_cd = O.floor_cd
WHERE (OH.co_cd IN ('9999') AND (SELECT COUNT(*) FROM T_OCCPT O1
WHERE O1.prpty_id = O.prpty_id AND O1.floor_cd = O.floor_cd AND O1.co_cd IN ('9999')) < 1)
OR (OH.co_cd IN ('8888') AND (SELECT COUNT(*) FROM T_OCCPT O1
WHERE O1.prpty_id = O.prpty_id AND O1.floor_cd = O.floor_cd AND O1.co_cd IN ('8888')) < 1)
OR (OH.co_cd IN ('7777') AND (SELECT COUNT(*) FROM T_OCCPT O1
WHERE O1.prpty_id = O.prpty_id AND O1.floor_cd = O.floor_cd AND O1.co_cd IN ('7777')) < 1)
OR (OH.co_cd IN ('6666') AND (SELECT COUNT(*) FROM T_OCCPT O1
WHERE O1.prpty_id = O.prpty_id AND O1.floor_cd = O.floor_cd AND O1.co_cd IN ('6666')) < 1)
OR (OH.co_cd IN ('0000') AND (SELECT COUNT(*) FROM T_OCCPT O1
WHERE O1.prpty_id = O.prpty_id AND O1.floor_cd = O.floor_cd AND O1.co_cd IN ('0000')) < 1)
GROUP BY OH.prpty_id, OH.floor_cd, OH.co_cd, OH.cst_ctr_cd
ORDER BY OH.prpty_id, OH.floor_cd

However, when I attempt to create a view with this query and then return the results from the view (using SELECT * FROM [viewname]), I get the following: Server: Msg 8624, Level 16, State 3, Line 1
Internal SQL Server error.

I believe it has something to do with the subselects but I'm not sure what can be done to remedy it. I know in previous examples where I've encountered this error message that I could often use TOP 100 PERCENT but that isn't working this time... Any advice would be appreciated. (Also, I'm working on SQL Server 2000.) Thanks!

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

 

[gmmastros]

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

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Chopstik]

Yeah, I'd seen that one and have put a call into our DBA to find out if it's completely updated with the latest SP's. I'm assuming that it is but cannot say for sure. After all, we all know what assuming does...

Hopefully that is the issue and we can run from there. I'll let you know either way. Thanks.

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

 

[gmmastros]

You don't have to wait for your dba to tell you if it is patched to latest service pack.

You can run this code to see what the service pack level is.

[tt][blue]Select ServerProperty('ProductVersion'), ServerProperty('ProductLevel')[/blue][/tt]

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Chopstik]

Product Version: 8.00.760
Product Level: SP3

That's very useful... Still much to learn about SQL Server, I have...

Of course, this being said, I can't say for certain that this was fixed by SP3 or not... Maybe I'm tired but am I missing this?

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

 

[Chopstik]

Never mind, I see it... First corrected in SP4... Yep, gotta talk with the DBA to see what we can do to get it fixed... Thanks, George!

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

 

[gmmastros]

No problem. I'm glad I could help.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom