locked
A severe error occurred on the current command. The results, if any, should be discarded. RRS feed

  • Question

  • User-1228233955 posted

    Hi,

    I am using SqlServer 2008 when ever I execute following query  

    SELECT Deduction_Details_ID,HeadName=(case when z_EntityDtl_Deduction_Details_007003.HeadName is null then (select Deduction_Name from z_EntityMast_Deduction_007002 where Deduction_ID=z_EntityDtl_Deduction_Details_007003.Deduction_ID)else z_EntityDtl_Deduction_Details_007003.headname End)FROM z_EntityDtl_Deduction_Details_007003 where isnull(Submission_of_Bills,0)=1  and isnull(UserExemption,0)=1  and Financial_Year='2009-2010' order by HeadName
    


    I get error: error is mentioned in subject part.

    after this error connection is automatically closed.

    Please tell me how to resolve.

     

    Wednesday, March 31, 2010 5:13 AM

Answers

  • User599926440 posted

    Hi,

    I think you should use the elimination technique for reaching the cause of the problem. By this I mean, try simplifying the query step by step as shown below:

    SELECT Deduction_Details_ID,   
    HeadName=   
        (   
            case    
                when z_EntityDtl_Deduction_Details_007003.HeadName is null    
                then (   
                        select 1  
                      )   
                else z_EntityDtl_Deduction_Details_007003.headname    
            End   
        )   
                   
    FROM z_EntityDtl_Deduction_Details_007003    
    where isnull(Submission_of_Bills,0)=1     
    and isnull(UserExemption,0)=1     
    and Financial_Year='2009-2010'    
    order by HeadName  


    If this works, then there is a problem in the inner select clause. If it doesn't, move to the next step:

    SELECT Deduction_Details_ID,   
    HeadName=   
        (   
            case    
                when z_EntityDtl_Deduction_Details_007003.HeadName is null    
                then (   
                        select 1  
                      )   
                else z_EntityDtl_Deduction_Details_007003.headname    
            End   
        )   
                   
    FROM z_EntityDtl_Deduction_Details_007003    
    order by HeadName  


    If this works, then there is a problem with the where clause. If it doesn't move forward.....

    I hope this technique shall help you reach the cause of problem and hence the solution....

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, April 1, 2010 3:03 AM
  • User1867929564 posted

     is other sql query running ok in 2008 ?
    How much records your existing query should return ?
    Make another query,which would return same record count records.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, April 1, 2010 4:01 AM

All replies

  • User599926440 posted

    Hi,

    Did u try this?

    SELECT Deduction_Details_ID,
    HeadName=
    	(
    		case 
    			when z_EntityDtl_Deduction_Details_007003.HeadName is null 
    			then (
    					select Deduction_Name 
    					from z_EntityMast_Deduction_007002 
    					inner join z_EntityDtl_Deduction_Details_007003 ON z_EntityMast_Deduction_007002.Deduction_ID = z_EntityDtl_Deduction_Details_007003.Deduction_ID
    					where Deduction_ID=z_EntityDtl_Deduction_Details_007003.Deduction_ID
    				  )
    			else z_EntityDtl_Deduction_Details_007003.headname 
    		End
    	)
    			
    FROM z_EntityDtl_Deduction_Details_007003 
    where isnull(Submission_of_Bills,0)=1  
    and isnull(UserExemption,0)=1  
    and Financial_Year='2009-2010' 
    order by HeadName


    I just added an inner join within the inner select clause in the case statement.

    Hope it works... 

    Wednesday, March 31, 2010 6:35 AM
  • User-1228233955 posted

    Hi chaituprince;

    This problem is still coming on SqlServer 2008, if I run my script as well as your modified script on SqlServer 2005 it is executed well with aspected output.

    but on SqlServer 2008 still error... 

    Wednesday, March 31, 2010 6:45 AM
  • User599926440 posted

    Hi,

    I think you should use the elimination technique for reaching the cause of the problem. By this I mean, try simplifying the query step by step as shown below:

    SELECT Deduction_Details_ID,   
    HeadName=   
        (   
            case    
                when z_EntityDtl_Deduction_Details_007003.HeadName is null    
                then (   
                        select 1  
                      )   
                else z_EntityDtl_Deduction_Details_007003.headname    
            End   
        )   
                   
    FROM z_EntityDtl_Deduction_Details_007003    
    where isnull(Submission_of_Bills,0)=1     
    and isnull(UserExemption,0)=1     
    and Financial_Year='2009-2010'    
    order by HeadName  


    If this works, then there is a problem in the inner select clause. If it doesn't, move to the next step:

    SELECT Deduction_Details_ID,   
    HeadName=   
        (   
            case    
                when z_EntityDtl_Deduction_Details_007003.HeadName is null    
                then (   
                        select 1  
                      )   
                else z_EntityDtl_Deduction_Details_007003.headname    
            End   
        )   
                   
    FROM z_EntityDtl_Deduction_Details_007003    
    order by HeadName  


    If this works, then there is a problem with the where clause. If it doesn't move forward.....

    I hope this technique shall help you reach the cause of problem and hence the solution....

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, April 1, 2010 3:03 AM
  • User1867929564 posted

     is other sql query running ok in 2008 ?
    How much records your existing query should return ?
    Make another query,which would return same record count records.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, April 1, 2010 4:01 AM
  • User-1228233955 posted

    Hi,

    other query are fetching thousands of record  very well but my required query espectes only 10 records.

    but its is unable to execute. and throws error. 

    Monday, April 5, 2010 6:12 AM
  • User-1228233955 posted

    Hi,

    I am surprosed, the query whic was unable to execute earlier, now the same query is executing well with espected output without any changes.

    but Still I want to find the reason of error ?... 

    Monday, April 5, 2010 6:21 AM
  • User-1255995948 posted

    My appologies for replying to an old thread but I just wanted an answer here in case I go searching for this again. Apparently some SP's need to be recompiled after migrating from 2005 to Sql Server 2008. 

    Try recompiling the sp(s) then check.

    Monday, January 3, 2011 3:45 PM