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

[tonydismukes]

I have a view:

CREATE VIEW ViewForCustom867Download
AS
SELECT 
h.customerkey,
d.REF02_SL_SALESMANNUM as salesman_num, d.PID05_F_ITEMDESC as description,
convert(int,d.QTY02_39_SHIPPEDQTY) as qty,d.LIN_UA_UPCCASECODE as upc, 
convert(datetime,h.DTM02_035_DeliveryDate) as DeliveryDate,
n.N102_NAME as name,n.N301_ADDRESSONE +'  '+n.N302_ADDRESSTWO +'  '+n.N401_CITY +', '+n.N402_STATE +' '+ n.N403_ZIP as address
FROM DETAIL d, HEADER h, NAMES n
where h.customerkey = d.customerkey
and h.customerkey = n.customerkey
and h.headerkey = d.headerkey
and h.headerkey = n.headerkey
and n.N101_QUALIFIER = 'BT'

The source fields from the original tables are all varchars. The view works just fine.

I'm generating a dynamically-built query which runs against the view:

select name, address, salesman_num,description,
(select ISNULL(SUM(v1.qty),0) from ViewForCustom867Download v1 
where v1.customerkey = v0.customerkey and v1.name = v0.name 
and v1.address = v0.address and v1.salesman_num = v0.salesman_num 
and v1.description = v0.description 
and v1.deliverydate >= convert(datetime,'01-01-2005') 
and v1.deliverydate <= convert(datetime,'01-31-2005') 
and v1.deliverydate >= convert(datetime,'01-01-2005') 
and v1.deliverydate <= convert(datetime,'02-11-2005') )as Jan ,
(select ISNULL(SUM(v2.qty),0) from ViewForCustom867Download v2 
where v2.customerkey = v0.customerkey and v2.name = v0.name 
and v2.address = v0.address and v2.salesman_num = v0.salesman_num 
and v2.description = v0.description 
and v2.deliverydate >= convert(datetime,'02-01-2005') 
and v2.deliverydate <= convert(datetime,'02-28-2005') 
and v2.deliverydate >= convert(datetime,'01-01-2005') 
and v2.deliverydate <= convert(datetime,'02-11-2005') )as Feb 
from ViewForCustom867Download v0 where v0.customerkey = 2 
group by v0.name, v0.address, v0.salesman_num,v0.description, v0.customerkey

(I'm feeding a start date & end date - in the example above the start date is 1-1-2005, end date is 2-11-2005. The query should return sales quantities for each item by month grouped by location and salesman)

The query checks out okay when I run a syntax check. However, when I run the query in query analyzer I get the following result:

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

I'm running SQL 2000 on a Windows 2000 server.

Microsoft Knowledge Base article 830466 describes what sounds like the same problem, but states that the bug was fixed with service pack 4. I installed service pack 4, but I'm still getting the same result.

Any suggestions for a bug fix or a workaround?

Thanks in advance,

Tony Dismukes

 

[SQLBI]

Have you checked you Windows event log, errors state 21 errors get written there nd it may help diagnose your problem.



Cheers,
Leigh

You're only as good as your last backup!

 

[tonydismukes]

Thanks for the suggestion, Leigh. I checked the event log and didn't find anything.

 

[Chopstik]

I have encountered this problem previously as well and determined it was because of the computed/aggregated subquery I was using. You can see the information for it here:

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

I suspect that the problem is similar to yours above. There is a workaround (as well as a hotfix that did not work for me, either) in the link above, but you will have to figure out what might work best for you. In my case, I was able to use the TOP 1 function and it worked for me.

I was also looking at your query and wondering why you are doing your subquery as you are.

(select ISNULL(SUM(v1.qty),0) from ViewForCustom867Download v1 
where v1.customerkey = v0.customerkey and v1.name = v0.name 
and v1.address = v0.address and v1.salesman_num = v0.salesman_num 
and v1.description = v0.description 
and v1.deliverydate >= convert(datetime,'01-01-2005') 
and v1.deliverydate <= convert(datetime,'01-31-2005') 
and v1.deliverydate >= convert(datetime,'01-01-2005') 
and v1.deliverydate <= convert(datetime,'02-11-2005') )as Jan

You could reduce part of this by simply stripping out the third v1.deliverydate check (since it's the same as the first) and then determining whether you need the second or fourth v1.deliverydate check since they appear to be at odds with one another. Maybe I'm missing something, but thought I'd point it out. Anyway, hope this helps somewhat...

------------------------------------------------------------------------------------------------------------------------
"I am not young enough to know everything."
Oscar Wilde (1854-1900)

 

[tonydismukes]

I have the apparently redundant start/end dates because the query is dynamically built based on a user supplied start/end date, but I'm also grouping the results by month. In the example above, I supplied a start date of 1-1-2005 and end date of 2-11-2005. I suppose that I could put some extra logic in the code which builds the query to just use a start date of (supplied start date/beginning of month - whichever is later) and end date of (supplied end date/end of month - whichever is earlier). I don't know if that will affect the problem, but I'll probably try it anyway.

The problem as described in KB article 290817 was supposedly fixed in service pack 1, so MS wouldn't give me the hotfix. (I was running SP3 originally. I tried updating to SP4 based on the KB article I listed, but that didn't work. I would have tried the workaround you listed, but I can't figure out how using TOP 1 would apply to the query I'm running.

Thanks anyway.

 

[Chopstik]

I would modify the code as you discussed to pull the dates once (later start and earlier ending dates) which will make the query somewhat cleaner. But I agree with you that I'm not sure that will help the problem that you're having at the moment with the ever-so-helpful "SQL Internal Error" message you're receiving.

Not sure why you would not be able to download SP4 (particularly if that is able to fix your problem). I do not think that you can use TOP 1 either with your existing subquery, but you could use TOP 1 in place of the grouping that you're using in the main query. I believe it was the grouping that was causing me the similar problems I was experiencing before. I am not sure why you're using the grouping as you are, but I believe you could replace it as follows and see if this helps:

select [COLOR=red]TOP 1[/color] name, address, salesman_num,description,
(select ISNULL(SUM(v1.qty),0) from ViewForCustom867Download v1 
where v1.customerkey = v0.customerkey and v1.name = v0.name 
and v1.address = v0.address and v1.salesman_num = v0.salesman_num 
and v1.description = v0.description 
and v1.deliverydate >= convert(datetime,'01-01-2005') 
and v1.deliverydate <= convert(datetime,'01-31-2005') 
and v1.deliverydate >= convert(datetime,'01-01-2005') 
and v1.deliverydate <= convert(datetime,'02-11-2005') )as Jan ,
(select ISNULL(SUM(v2.qty),0) from ViewForCustom867Download v2 
where v2.customerkey = v0.customerkey and v2.name = v0.name 
and v2.address = v0.address and v2.salesman_num = v0.salesman_num 
and v2.description = v0.description 
and v2.deliverydate >= convert(datetime,'02-01-2005') 
and v2.deliverydate <= convert(datetime,'02-28-2005') 
and v2.deliverydate >= convert(datetime,'01-01-2005') 
and v2.deliverydate <= convert(datetime,'02-11-2005') )as Feb 
from ViewForCustom867Download v0 where v0.customerkey = 2 
[COLOR=green]/*group by v0.name, v0.address, v0.salesman_num,v0.description, v0.customerkey  --You don't need this grouping here.*/ [/color]

------------------------------------------------------------------------------------------------------------------------
"I am not young enough to know everything."
Oscar Wilde (1854-1900)

 

[tonydismukes]

Thanks, I'll try that.

To clarify, I had no problem installing SP4. It's just that SP4 didn't fix the problem.

Tony

 

[tonydismukes]

OK, I cleared out the extra start/end dates.

select name, address, salesman_num,description,
(select ISNULL(SUM(v1.qty),0) from ViewForCustom867Download v1
where v1.customerkey = v0.customerkey and v1.name = v0.name
and v1.address = v0.address and v1.salesman_num = v0.salesman_num
and v1.description = v0.description
and v1.deliverydate >= convert(datetime,'01-01-2005')
and v1.deliverydate <= convert(datetime,'01-31-2005')
 )as Jan ,
(select ISNULL(SUM(v2.qty),0) from ViewForCustom867Download v2
where v2.customerkey = v0.customerkey and v2.name = v0.name
and v2.address = v0.address and v2.salesman_num = v0.salesman_num
and v2.description = v0.description
and v2.deliverydate >= convert(datetime,'02-01-2005')
and v2.deliverydate <= convert(datetime,'02-11-2005') )as Feb
from ViewForCustom867Download v0 where v0.customerkey = 2
group by v0.name, v0.address, v0.salesman_num,v0.description, v0.customerkey

Didn't help. I realized that using the TOP 1 condition wouldn't work, because I need all the locations, not just the top 1. Nevertheless, I tested it out and got the exact same error:

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

Arrgh! Can anyone suggest a different way to write the query to get the same data? I need to return each location (name + address) + salesman, the items sold at that location, and the monthly totals for each item. I'm using it to build a custom report which will look something like this:

Location Salesman_num Jan Feb Mar ... Total

Store #1
123 Main St
101
Item #1 10 15 15 40
Item #2 30 20 35 85
Total 40 35 50 125
135
Item #3 20 20 20 60
Total 20 20 20 60

Store #2
456 Example Ave
121
Item #1 60 50 40 150

etc, etc. (I hope the columns line up properly when this posts. I had to experiment to make them come out right in preview. If not, you can figure out how it's meant to look.)

I could just run some loops in the ColdFusion code that's creating the SQL query, and break the single query down into a bunch of queries, but that seems really inefficient. My query should work, darn it! If I can't use my original query due to a Microsoft bug, I'd like to come up with another one that would be about as efficient.

Thanks for the suggestions so far.

Tony

 

[tonydismukes]

Update: I tried just bypassing the view entirely and running the query against the underlying tables.

select n.N102_NAME as name, 
n.N301_ADDRESSONE +'  '+n.N302_ADDRESSTWO +'  '+n.N401_CITY +', '+n.N402_STATE +' '+ n.N403_ZIP as address
,d.REF02_SL_SALESMANNUM as salesman_num,
d.PID05_F_ITEMDESC as description,
(select ISNULL(SUM(convert(int,d1.QTY02_39_SHIPPEDQTY)),0) 
from  DETAIL d1, HEADER h1, NAMES n1 
where d1.PID05_F_ITEMDESC = d.PID05_F_ITEMDESC  
and d1.REF02_SL_SALESMANNUM = d.REF02_SL_SALESMANNUM
and d1.customerkey = h1.customerkey and d1.headerkey = h1.headerkey
and d1.customerkey = n1.customerkey and n1.headerkey = h1.headerkey
and n1.N102_NAME = n.N102_NAME
and convert(datetime,h1.DTM02_035_DeliveryDate) >= convert(datetime,'01-01-2005') 
and convert(datetime,h1.DTM02_035_DeliveryDate)<= convert(datetime,'01-31-2005') )as Jan ,
(select ISNULL(SUM(convert(int,d2.QTY02_39_SHIPPEDQTY)),0) 
from  DETAIL d2, HEADER h2, NAMES n2 
where d2.PID05_F_ITEMDESC = d.PID05_F_ITEMDESC  
and d2.customerkey = h2.customerkey and d2.headerkey = h2.headerkey
and d2.customerkey = n2.customerkey and n2.headerkey = h2.headerkey
and n2.N102_NAME = n.N102_NAME
and convert(datetime,h2.DTM02_035_DeliveryDate) >= convert(datetime,'02-01-2005') 
and convert(datetime,h2.DTM02_035_DeliveryDate)<= convert(datetime,'02-11-2005') )as Feb 
from  DETAIL d, HEADER h, NAMES n
where h.customerkey = 17
and d.customerkey = 17
and n.customerkey = 17
and h.headerkey = d.headerkey
and h.headerkey = n.headerkey
and n.N101_QUALIFIER = 'BT' 
group by n.N102_NAME, n.N301_ADDRESSONE, n.N302_ADDRESSTWO,
 n.N401_CITY,n.N402_STATE,n.N403_ZIP,d.REF02_SL_SALESMANNUM,
d.PID05_F_ITEMDESC

It worked. Woo hoo! Apparently the SQL server bug only happens when running the subqueries against a view.

I was really only using the view to make the query a bit more concise and readable, but since it's dynamically built and run behind the scenes, I guess I shouldn't worry too much about the readability.

Next question - the query above works, but it does run a little bit slow. Anyone have any suggestions for improving the speed?