Stored Procedures and Cursors in MS SQL 7 and Crystal Reports 7

[baralong]

I've got a stored procedure that is intended for use with a crystal report. The procedure is quite complex,
building up a temporary table and then updating it with denormalised data, eg alias's for multiple people
associated with the base record.

The SP runs well enough from query analyser but from crystal it just crashes. Useing SQLServer Profiler
I find that crystal is trying to do the following:

declare @P1 int
declare @P3 int
declare @P4 int
declare @P5 int
set @P1=NULL
set @P3=561160
set @P4=98305
set @P5=NULL
exec sp_cursoropen @P1 output, N' EXEC wars_D.dbo.all_marriage_details;1 @P1,@P2 ', @P3 output, @P4
output, @P5 output, N'@P1 datetime,@P2 datetime', NULL, NULL
select @P1, @P3, @P4, @P5

If I run that in query analyser I get:

Server: Msg 16937, Level 16, State 1, Line 0
A server cursor is not allowed on a stored procedure with more than one SELECT statement. Use a default
result set or client cursor.
Server: Msg 16945, Level 16, State 1, Line 0
The cursor was not declared.

----------- ----------- ----------- -----------
0 561160 98305 0

(1 row(s) affected)

So my question is: how can I get the procedure to run in crystal? Can I get crystal to use a client
cursor, rather than a server one?

I'm using an OLEDB connection to the database.

The basic form of the stored procedure is:

Create temporaty table
Insert the rows
Step through the temporary table
for each record create an additional cursors to find all the aliases for the husband and wife
Update the denormalised alias fields for the husband and wife
Perform a few more updateson the code
return the temporary table.

 

[FowlerConsulting]

Could you call sub-stored procedures or views? Steven Fowler, Principal
steve.fowler@fowlerconsulting.com

http://www.fowlerconsulting.com

- Development, Training, and Consulting

 

[baralong]

Steven,

I did try that but it doesn't work. It looks like what I need to do is set the type of cursor that Crystal uses.

So far this seems to be impossible unless I use a record set to establish the connection for me.

 

[MalcolmW]

Check out the FAQ on reporting from a stored proc - Aaron was using SCR and SQL Server with a client side cursor. I asked him to clarify a few things in his FAQ, perhaps if you emailed him that might help nag him again Malcolm
Remember, if it wasn't for electricity, we'd be surfing the net by candlelight.