CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 3 of 3

Thread: Report based on a stored procedure stops working, fix is to recreate the SP

  1. #1
    Join Date
    Feb 2016
    Posts
    6

    Angry Report based on a stored procedure stops working, fix is to recreate the SP

    I'm working with Crystal Reports 2008, within an environment where we have about a few dozen custom reports created from Stored Procedures. The stored procedures are on a SQL 2012 environment.

    The Datasource for all of our reports are the same:
    Database Type: OLE DB (ADO)
    Provider: SQLOLEDB
    Integrated Security: False
    Use DSN Default Properties: False

    When we deploy the report, it works as needed. A week will go by and the department that utilizes the report will inform us that the report began to hang again. Using SQL Profiler, I see the request for the Stored Procedure, and then NOTHING. It never responds.

    If I open a copy of the RPT file and do a "Refresh Report Data", it prompts for the report parameters. Upon submission, Crystal just hangs.

    I've found that the following fixes the issue.

    1. Pull up an exact copy of the Stored Procedure by using the Modify option on the Stored Procedure
    2. Drop the Stored Procedure
    3. Change Alter to CREATE and then Execute
    4. From the RPT file, do a Verify Database

    I'm then able to run the Crystal Report without an issue. The user is able to run the Crystal Report without a problem. They are able to continue to use the report for a few days and then it will suddenly hang again. Again, doing the above resolves the issue.


    I'm at a complete LOSS as to why this is happening. The Stored Procedure is not complex at all.

    Code:
    	
    SELECT Co.EV870_CLASS,Co.EV870_NAME,Co.EV870_ACCT_CODE,Co.EV870_ORG_CODE
    		,Co.EV870_ADDRESS_L1,Co.EV870_ADDRESS_L2,Co.EV870_ADDRESS_L3,Co.EV870_CITY,Co.EV870_STATE,Co.EV870_POSTAL_CODE
    		,ER100_ORD_NBR,ER100_ORD_TYPE,ER100_ORD_TOT,ER100_ARR_DATE,ER100_DEP_DATE,ER100_NEW_STS
    		,ER100_NG_BTO_CONTACT,ER100_ORD_ACCT,ER100_INVOICE,ER100_INTERNAL,ER100_PRICE_LIST,ER100_BILL_TO_CUST
    		,ER101_NEW_RES_TYPE,ER101_NEW_ITEM_STS
    		,CC716_DESC,CC716_DSP_SEQ_2,CC716_RATE_COL1
    		,PC.EV870_FIRST_NAME,PC.EV870_MIDDLE_INITIAL,PC.EV870_LAST_NAME,PC.EV870_LAST_NAME2,PC.EV870_SUFFIX
    		,(SELECT isnull(MAX(ER101_UNIT_CHRG),0) 
    			FROM ER101_ACCT_ORDER_DTL
    			WHERE ER101_ORD_NBR = ER100_ORD_NBR
    				AND ER101_ORG_CODE = ER101_ORG_CODE
    				AND ER101_PHASE = 1
    				AND ER101_NEW_RES_TYPE = '8100') HST_GST 
    	FROM EV870_ACCT_MASTER Co with (Nolock)
    	INNER JOIN EV875_ACCT_LINKS with (nolock) ON 
    		Co.EV870_ACCT_CODE = EV875_ACCT_CODE1
    		AND Co.EV870_ORG_CODE = EV875_ORG_CODE1
    	INNER JOIN EV870_ACCT_MASTER PC with (nolock) ON 
    		EV875_ACCT_CODE2=PC.EV870_ACCT_CODE 
    		AND EV875_ORG_CODE2=PC.EV870_ORG_CODE
    	INNER JOIN ER100_ACCT_ORDER with (Nolock) ON 
    		Co.EV870_ORG_CODE=ER100_ORG_CODE 
    		AND Co.EV870_ACCT_CODE=ER100_BILL_TO_CUST 
    	INNER JOIN ER101_ACCT_ORDER_DTL with (Nolock) ON 
    		ER100_ORG_CODE=ER101_ORG_CODE 
    		AND ER100_ORD_NBR=ER101_ORD_NBR 
    	INNER JOIN CC716_PRICE_LIST_DTL with (Nolock) ON 
    		ER101_ORG_CODE=CC716_ORG_CODE 
    		AND ER101_NEW_RES_TYPE=CC716_RES_TYPE_NEW 
    		AND ER101_PRICE_LIST=CC716_PRICE_LIST
    	WHERE ER100_ORG_CODE=@Organization
    		AND EV875_LINK='C40' 
    		AND EV875_STATUS='A'
    		AND ER101_PHASE = 1
    		AND ER100_ORD_NBR in (SELECT MAX(ER100_ORD_NBR)
    								FROM ER100_ACCT_ORDER with (Nolock)
    								INNER JOIN ER101_ACCT_ORDER_DTL with (Nolock) ON 
    									ER100_ORG_CODE=ER101_ORG_CODE 
    									AND ER100_ORD_NBR=ER101_ORD_NBR 
    								WHERE ER100_ORG_CODE = @Organization
    									AND ER100_BILL_TO_CUST=@OrderAcct
    									AND ER100_ORD_ACCT=@OrderAcct
    									AND ER101_PHASE = 1
    									AND ER100_NEW_STS <> 'X'
    									AND ER101_NEW_RES_TYPE in ('3000','3010','3020','3030')
    									AND ER101_NEW_ITEM_STS<>'X'
    									AND ER100_ORD_TYPE='MB'
    									GROUP BY ER101_PRICE_LIST,ER101_NEW_RES_TYPE)
    I've recreated the report from the ground up. No errors are reported. It simply stops responding.

  2. #2
    Join Date
    Feb 2016
    Posts
    6

    Re: Report based on a stored procedure stops working, fix is to recreate the SP

    To clarify, of all the reports we have in the environment. Two of them are experiencing this issue. Can't find what the difference is between these two reports and all the others.

  3. #3
    Join Date
    Feb 2016
    Posts
    6

    Re: Report based on a stored procedure stops working, fix is to recreate the SP

    I believe my previous statement that the report works for several days was in error. Just did a test today and it appears that the report only works for the day that I fixed it.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Windows Mobile Development Center


Click Here to Expand Forum to Full Width




On-Demand Webinars (sponsored)