using SQL Server stored procs in Crysal 8.5

[yehong]

I am trying to find documentation for using SQL Server stored procs in Crystal 8.5. I am able to find lot of documentation for Oracle stored procs but not a single for SQL Server stored procs. Does anyone have a link or more information about it? I am trying to access following stored proc in Crystal and when I try to add this proc in Crystal (Add Database window), I get error message "No fields in the file".
CREATE PROC dbo.ShowHierarchy
(
@Root int
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @EmpID int, @EmpName varchar(30)

SET @EmpName = (SELECT EmpName FROM dbo.Emp WHERE EmpID = @Root)
PRINT REPLICATE('-', @@NESTLEVEL * 4) + @EmpName

SET @EmpID = (SELECT MIN(EmpID) FROM dbo.Emp WHERE MgrID = @Root)

WHILE @EmpID IS NOT NULL
BEGIN
EXEC dbo.ShowHierarchy @EmpID
SET @EmpID = (SELECT MIN(EmpID) FROM dbo.Emp WHERE MgrID = @Root AND EmpID > @EmpID)
END
END
GO

 

[vidru]

Here is a pretty general document on using stored procs with Crystal:

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

If you look at the General Limitations section (page 3), you'll get an idea of how the procedure should be designed in order for Crystal to accept it as a data source.

In your case, no SELECT statement is ever issued, hence the "No fields in the file:" message.

-dave

 

[yehong]

Hi Dave, Thanks for your reply. I have gone through this doc and it did help little, but I wish it had some samples just as some docs have samples for Oracle store procs. Also, in my store proc there is one select statement, when I execute it I get results back in SQL Analyzer, but crystal still gives me error.

 

[pgtek]

hi
Get the latest download of service pack for oracle
do a search for it

http://support.businessobjects.com/library/kbase/

cheers

pgtek

 

[pgtek]

hi

OPPSSSSSSSSSS
wrong link

http://support.businessobjects.com/search/default.asp?ref=default.asp_shortcuts

cheers

pgtek

 

[synapsevampire]

What Dave is saying is that you aren't using a select statement to return a set of rows with a common structure, you're using select statements to fill variables, executing another proc or function, and you're using a print statement, none of which will be understood by external tools as an intelligent datasource.

Crystal needs a single data source, not a collection of data objects.

What you want to do is have a select statement at the end, and that doesn't mean to return a value to a variable.

-k

 

[ESquared]

Like....

SELECT @EmpID

as your last statement

-------------------------------------
A sacrifice is harder when no one knows you've made it.

 

[vidru]

Actually E², that wouldn't work either... it would have to be:

SELECT EmpID = @EmpID

-dave

 

[ESquared]

I suppose a column name would be nice.

-------------------------------------
A sacrifice is harder when no one knows you've made it.