Problem has been fixed by add

OPTION (MAXDOP 1)

at the end of the SQL statment.

Thanks


                                                                                                                   
                    William Sze                                                                                    
                                         To:     "ASP Web HowTo" <asp_web_howto@p...>                      
                    06/26/02 11:51       cc:                                                                       
                    AM                   Subject:     SQL problem(Document link: William Sze)                      
                                                                                                                   
                                                                                                                   



Hi,

SELECT [top_asm].top_asm as [Part Number],[defect_type].description as
Area,[area].area as Process,Sum([1st_pass].qty_test) AS Inspected, Sum
([1st_pass].qty_reject) AS Rejected, Sum([1st_pass].qty_sub) AS ReTest,(sum
([1st_pass].qty_reject)*10000/sum([1st_pass].qty_test)) AS Yield FROM
(([1st_pass] LEFT OUTER JOIN [defect_type] ON [1st_pass].inspectionareaID
= [defect_type].inspectionareaID) LEFT OUTER JOIN [top_asm] ON
[1st_pass].partnumberID = [top_asm].partnumberID) LEFT OUTER JOIN [area] ON
[1st_pass].areaID = [area].areaID WHERE [1st_pass].record_date Between
'6/16/2002 12:00:00 AM' And '6/21/2002 11:59:59 PM' and
[1st_pass].partnumberID=197 GROUP BY [top_asm].top_asm,
[defect_type].description, [area].area ORDER BY [1st_pass].area


I ran the above SQL statment but got an error message as below when I
comment out the On error resume next .

                                              
                       Microsoft OLE DB       
                       Provider for SQL       
                       Server (0x80040E14)    
                       Intra-query            
                       parallelism caused     
                       your server command    
                       (process ID #65) to    
                       deadlock. Rerun the    
                       query without          
                       intra-query            
                       parallelism by using   
                       the query hint option  
                       (maxdop 1).            
                                              




With the on error resume next, I gor error message:

                                                
 Error Number 3704                              
 :                                              
                                                
 Error Source ADODB.Recordset                   
 :                                              
                                                
 Error        Operation is not allowed when the 
 Description  object is closed.                 
 :                                              
                                                



Out of my mind, I luckily got the result set without any error once, but I
cannot get the result set again anymore.


However, if I ran the sql with day range
[1st_pass].record_date Between '6/16/2002 12:00:00 AM' And '6/18/2002
11:59:59 PM'

and

[1st_pass].record_date Between '6/19/2002 12:00:00 AM' And '6/21/2002
11:59:59 PM'

separately, I'll get both the result sets without any error.

Any idea/suggestion what's went wrong.?


Thanks
William

Hi,

SELECT [top_asm].top_asm as [Part Number],[defect_type].description as
Area,[area].area as Process,Sum([1st_pass].qty_test) AS Inspected, Sum
([1st_pass].qty_reject) AS Rejected, Sum([1st_pass].qty_sub) AS ReTest,(sum
([1st_pass].qty_reject)*10000/sum([1st_pass].qty_test)) AS Yield FROM
(([1st_pass] LEFT OUTER JOIN [defect_type] ON [1st_pass].inspectionareaID
= [defect_type].inspectionareaID) LEFT OUTER JOIN [top_asm] ON
[1st_pass].partnumberID = [top_asm].partnumberID) LEFT OUTER JOIN [area] ON
[1st_pass].areaID = [area].areaID WHERE [1st_pass].record_date Between
'6/16/2002 12:00:00 AM' And '6/21/2002 11:59:59 PM' and
[1st_pass].partnumberID=197 GROUP BY [top_asm].top_asm,
[defect_type].description, [area].area ORDER BY [1st_pass].area


I ran the above SQL statment but got an error message as below when I
comment out the On error resume next .

                                              
                       Microsoft OLE DB       
                       Provider for SQL       
                       Server (0x80040E14)    
                       Intra-query            
                       parallelism caused     
                       your server command    
                       (process ID #65) to    
                       deadlock. Rerun the    
                       query without          
                       intra-query            
                       parallelism by using   
                       the query hint option  
                       (maxdop 1).            
                                              




With the on error resume next, I gor error message:

                                                
 Error Number 3704                              
 :                                              
                                                
 Error Source ADODB.Recordset                   
 :                                              
                                                
 Error        Operation is not allowed when the 
 Description  object is closed.                 
 :                                              
                                                



Out of my mind, I luckily got the result set without any error once, but I
cannot get the result set again anymore.


However, if I ran the sql with day range
[1st_pass].record_date Between '6/16/2002 12:00:00 AM' And '6/18/2002
11:59:59 PM'

and

[1st_pass].record_date Between '6/19/2002 12:00:00 AM' And '6/21/2002
11:59:59 PM'

separately, I'll get both the result sets without any error.

Any idea/suggestion what's went wrong.?


Thanks
William