insertion error

[sheridan101]

I am using C#.NET, Crystal Reports.NET, Win2K and SQL Server 2000, and using a DSN ODBC connection to the database.

I have a report that uses a stored procedure to obtain data. In the procedure, it builds a temporary table, INSERTing the results of various queries into it, and then dumping the contents of the table as the procedure query result.

The problem I am having is that on one INSERT command, the select part which chooses the data to insert occasionally produces no data (consistently reproduceable), so no data is inserted. When this happens, upon return from the stored procedure, Crystal Reports gets an exception and presents the dialog "Failed to open a Rowset", terminating the report. If I use Query analyzer to examine the contents of the table, there is data in it produced by one of the other INSERTS in the SP.

Is there a way to get around this error without having to add an additional "select count()" ahead of the insert. The table can have over 2 million records and adding an additional query will add segnificant process time to the report.

 

[synapsevampire]

I think that you're either going to have to check first, or use an onerror resume next in your SP if you want to eliminate the error dialog.

-k

 

[vidru]

Also, if you're doing an INSERT in your sp, put a SET NOCOUNT ON statement at the beginning of the proc, as Crystal will sometimes consider the "N Rows Affected" message as output from the procedure.

-dave

 

[sheridan101]

SQL Server does not seem to understand ONERROR or ON ERROR or any other variant of it.