SET NOCOUNT ON works.
The stored proc returns a resultset from a temporary table (created within the stored proc).
thanks
I now have a new problem.
When the ADO recordset, which is the result of the stored Proc, is passed to a Crystal Report object, I get this message from Crystal Report:
unable to access the first record
SQL Server Error.
set rs = cmd.execute
Set Report = New CRQuesSummary
Report.Database.SetDataSource rs
CRViewer1.ReportSource = Report
CRViewer1.ViewReport '<----- gives me the error here
I think, the crystal reports navigates the recordset back and forth, but the recordset is forwardonly.
I could create a the rs with dynamic cursortype instead of forward only.
However, to do this, I would have to call the stored proc from RS.OPEN instead of from the command object, which gives me errors passing the parameters to the stored proc:
set rs = new adodb.recordset
rs.CursorLocation = adUseClient
rs.CursorType = adOpenDynamic
rs.open "sp_test 'strParmater1', 'strParameter2'" , cn, _
adOpenDynamic, adLockOptimistic, adCmdStoredProc
this gives me errors in the rs.open when passing parameters. No errors when not passing paramters.
(I have even tried these: "EXEC sp_test 'paramter1', 'parametr2'"
and "EXEC sp_test('paramter1', 'parameter2')"
and "sp_test ('paramter1', 'parameter2')". I always get an error.
thanks
Bharat Sanghvi
Vice President, Information Technology
Deyta
Continuous Quality Measurement Systems