Cannot Run Stored Procedure through Crystal

[kjaz11]

I have a simple stored procedure that accepts one integer parameter. The stored procedure works fine in query analyzer. The database is SQL server 2000. I use crystal all the time to retrieve data from tables and views, but when I try a stored procedure (through the same odbc connection) I get the following error.

ODBC Error: Syntax Error or Access violation

What is different in Crystal about running a stored procedure rather than a view? Or is this an odbc/sql server problem.

Any thoughts?

Thanks in Advance...

 

[bdreed35]

How is your Stored Procedure structured? The SP needs to return a dataset at the end of it. Please post the SP if it is not too large.

~Brian

 

[kjaz11]

Here is the stored procedure:

CREATE PROCEDURE [usp_PlayerLookup]

@piPlayerID AS integer

AS

SELECT FirstName,
MI,
LastName,
FROM dbo.tbl_Player
WHERE PlayerID = @piPlayerID

GO

 

[kjaz11]

Here is the stored procedure with correct syntax:

CREATE PROCEDURE [usp_PlayerLookup]

@piPlayerID AS integer

AS

SELECT FirstName,
MI,
LastName
FROM dbo.tbl_Player
WHERE PlayerID = @piPlayerID

GO

 

[robnmo]

Crystal Reports requires a REF CURSOR be defined
For CRYSTAL 9 & 10 and Oracle see:

http://support.businessobjects.com/communityCS/TechnicalPapers/cr_oracle_stored_procedures.pdf

 

[kjaz11]

I am using SQL Server 2000. I am not finding reference to REF CURSOR for SQL Server. What am I missing?