"there are no fields in the file" error using a stored proc 1

[Siggy19]

I have a report which had a view attached to it which I have replaced with a stored proc (for efficiency... the view had a load of UNION ALLs but were pulling from the same tables so creating a temporary table and then doing the UNIONs is much faster)

I am now getting a "There are no fields in the File" error.

Having done a search for this it seems to be related to having no select statement that Crystal recognizes, but this is not the case here.

CREATE PROCEDURE RPT_Inventory_1
	@Start_Date	DATETIME,
	@End_Date	DATETIME
AS


SELECT @Start_Date = CONVERT(CHAR(10), @Start_Date, 101) + ' 00:00:00.000'
SELECT @End_Date = CONVERT(CHAR(10), @End_Date, 101) + ' 23:59:59.997'


SELECT
	a.CLM_PHCode,
	b.HPC_Intlzip,
	a.CLM_DateRecd,
	a.CLM_CreateDate,
	c.CLD_AdjCode,
	a.CLM_Net
INTO	#Report_Data
FROM
	DMG_Claim_Masters a (nolock) INNER JOIN
	DMG_HP_Contracts b (nolock) ON a.CLM_HPCode = b.HPC_HPCode INNER JOIN
	DMG_Claim_Details c (nolock) ON a.CLM_ClaimNo = CLD_ClaimNo
WHERE
	a.CLM_DateRecd BETWEEN @Start_Date AND @End_Date



SELECT
	'Paid' AS ClaimType,
	CLM_PHCode,
	HPC_Intlzip,
	MONTH(CLM_DateRecd) AS ClaimMonth,
	CLM_DateRecd,
	CLM_CreateDate
FROM
	#Report_Data
WHERE
	CLD_AdjCode IS NULL
	OR CLD_AdjCode IN (<list 1>)
UNION ALL
SELECT
	'Contested/Denied',
	CLM_PHCode,
	HPC_Intlzip,
	MONTH(CLM_DateRecd) AS ClaimMonth,
	CLM_DateRecd,
	CLM_CreateDate
FROM
	#Report_Data
WHERE
	CLD_AdjCode IN (<list 2>)
UNION ALL
SELECT
	'Adjusted',
	CLM_PHCode,
	HPC_Intlzip,
	MONTH(CLM_DateRecd) AS ClaimMonth,
	CLM_DateRecd,
	CLM_CreateDate
FROM
	#Report_Data
WHERE
	CLD_AdjCode IN (<list 3>)
UNION ALL
SELECT
	'Forwarded',
	CLM_PHCode,
	HPC_Intlzip,
	MONTH(CLM_DateRecd) AS ClaimMonth,
	CLM_DateRecd,
	CLM_CreateDate
FROM
	#Report_Data
WHERE
	CLD_AdjCode = 'FORW'
UNION ALL
SELECT
	'Acknowledged 1',
	CLM_PHCode,
	HPC_Intlzip,
	MONTH(CLM_DateRecd) AS ClaimMonth,
	CLM_DateRecd,
	CLM_CreateDate
FROM
	#Report_Data
UNION ALL
SELECT
	'Acknowledged 2',
	CLM_PHCode,
	HPC_Intlzip,
	MONTH(CLM_DateRecd) AS ClaimMonth,
	CLM_DateRecd,
	CLM_CreateDate
FROM
	#Report_Data
WHERE
	CLD_AdjCode NOT IN (<list 4>)
	AND((CLM_Net > 0)
		OR (CLM_Net = 0
		AND CLD_AdjCode <> '#C'))
UNION ALL
SELECT
	'Input',
	CLM_PHCode,
	HPC_Intlzip,
	MONTH(CLM_DateRecd) AS ClaimMonth,
	CLM_DateRecd,
	CLM_CreateDate
FROM
	#Report_Data

I have tried selecting the UNION stuff into a second temporary table and then selecting from there, but that gives me the same error.

Any ideas ?

 

[Turkbear]

Hi,
What database?
Usually an SP must have some return or OUT) method..Like a Ref Cursor ( if Oracle)...





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[Siggy19]

It's SQL Server, but the same code would work in Oracle without the (nolock) statements...

The bit that should return data to the Crystal Report (and does work if I run the Stored Proc on its own) is from

SELECT
'Paid' AS ClaimType,
CLM_PHCode,
HPC_Intlzip,
MONTH(CLM_DateRecd) AS ClaimMonth,
CLM_DateRecd,
CLM_CreateDate

onwards.

 

[Turkbear]

Hi,
See if this doc helps:

http://support.businessobjects.com/communityCS/TechnicalPapers/storproc.pdf.asp

To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[vidru]

Try adding a SET NOCOUNT ON statement to the beginning of your procedure definition. The 'n row(s) affected' messages have been known to be interpreted as result sets by CR.

-dave

 

[Siggy19]

I tried that and still no joy. <sigh>

 

[synapsevampire]

From what I see, there isn't a UNION between the 1st and 2nd select statement.

Crystal doesn't allow multiple datasets to be returned.

-k

 

[vidru]

That's what I thought at first too, k, but that SELECT statement is actually populating a Temp table (#ReportData). That's why I thought the problem might be the resulting 'n row(s) affected' message that it's going to generate.

-dave

 

[MisterMo]

I have had problems in the past with CR not liking SP which returned more than one sql statement and one way to fix it is to set the no count on

BEGIN
SET NOCOUNT ON

at the start of your SP.

Another thing I would do for te sake of CR is to keep the SP more traditional

eg Create the temp table beforehand and then insert the data into it.

All the Best

Mo

 

[Siggy19]

I ended up creating the temporary table explicitly in the stored procedure, rather than having the first select statement do it with the 'INTO #Report_Data' command.

No idea why, but it now seems to work.

Thank you all for your help and advice.