cr2008 stored procedure as data source

1)  Are you using Oracle?  If so, you need to modify the proc to be compatible: http://www.sdn.sap.com/irj/boc/index?rid=/library/uuid/1084c536-711e-2b10-e48a-924a60745253

2)  Crystal Reports requires the Stored Proc to return a single recordset via SELECT statement.  So, no matter what else your proc is doing, it needs to end with SELECT  something FROM something WHERE something...

If you post your proc we might be able to troubleshoot specific issues.

~Kurt

Rhinok

I am using SQL SERVER 2008. I have attached the code for my SP and also a sample of the returned dataset.
 VoucherListing.xlsx

ALTER PROCEDURE [dbo].[voucherInfoByVendor_Date]

(
@vendorID int,
@voDate datetime,
@deptID int,
@cDate datetime
)

AS
BEGIN


SET NOCOUNT ON;

----********** FOR DEBUGGING PURPOSES ONLY ****************
----*******************************************************

----declare @vendorID int
----declare @voDate datetime
----declare @deptID int
----declare @cDate datetime

----set @vendorID =  1238
----set @voDate = '3/7/2011'
----set @cDate = '3/7/2011'
----set @deptID = 18

----*******************************************************
----*******************************************************
	
SELECT DISTINCT vr.vendorNum, vr.vendorName, vr.vendorname2, CASE WHEN vr.vendorAddress2 = '' THEN vr.vendorAddress1 ELSE vr.vendorAddress1 + CHAR(13) + CHAR(10) + vr.vendorAddress2 END as vendorAddress1,
		   vr.vendorCity + ', ' + vr.vendorState + ' ' + vr.vendorZip as vendorAddress3,
		   vr.vendorTaxInfo, f.fullFundNumber as fundNumber, vo.invoiceNumber
		   --,vf.amount as voucherAmount
		   ,(SELECT SUM(amount) FROM voucher_funding vf2 WHERE vf2.voucherID = vo.voucherID AND vf2.fundID = f.fundID) AS voucherAmount
		   ,vo.voucherDate, f.fundName, d.county, @cDate AS commishDate
		   ,vo.voucherID
-- Removed some lines because the voucher was printing all descriptions concatenated 
-- for each voucher, check RC Regional Hospital 6/15/09 for example if added back
		   ,STUFF((SELECT DISTINCT ', ' + v.description
		   FROM voucher v
		   --INNER JOIN voucher_funding vf2 on vf2.voucherID = v.voucherID
		   WHERE vr.vendorID = v.vendorID AND v.deptID = @deptID AND vo.invoiceNumber = v.invoiceNumber AND (v.voucherDate >= @voDate AND v.voucherDate <= @voDate)
		   --WHERE vr.vendorID = v.vendorID AND vf.fundID = vf2.fundID  AND (v.voucherDate >= @voDate AND v.voucherDate <= @voDate)
		   FOR XML PATH('')),1,1,'')AS description
		   --, vo.description
FROM vendor vr
INNER JOIN voucher vo on vo.vendorID = vr.vendorID
INNER JOIN voucher_funding vf on vf.voucherID = vo.voucherID
INNER JOIN fund f on f.fundID = vf.fundID
INNER JOIN department d ON d.deptID = f.deptID
WHERE vr.vendorID = @vendorID AND CONVERT(varchar, vo.voucherDate,101) = @voDate
	  AND f.deptID = @deptID AND vo.multiVoucherID IS NULL AND vo.witnessVoucher=0
ORDER BY f.fullFundNumber, vo.invoiceNumber

END

Select allOpen in new window

Does the query return the correct information in Query Analyzer?

mlmcc