-
February 3rd, 2016, 03:39 PM
#1
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.
-
February 3rd, 2016, 03:56 PM
#2
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.
-
February 4th, 2016, 10:31 AM
#3
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|