Problem with stored procedure error -- sql server programming

Ragnar Midtskogen
9/12/2003 8:19:36 PM

Hello,

In my VB application I use an ADO command to run a stored procedure, written
by someone else, in an SQL Server database, connecting through ODBC.
The application has been running fine for years but about a week ago the
stored proc appears to cause a runtime errors in my app. My guess at the
moment is that the problem is caused by the data somehow.
The runtime error # is: -2147217900 and the description is: Microsoft OLE DB
Provider for ODBC Drivers | [Microsoft][ODBC SQL Server Driver][SQL
Server]processing VIN !!!!CKM24AS154486
The last part, "processing VIN !!!!CKM24AS154486" is the result of a PRINT
statement in the proc.
It looks like the proc suffers a fatal error and causes a runtime error in
my VB app, passing the last result of the PRINT statement as the error
description.
The proc uses the join of two tables to create a temporary table with two
rows of varchar, the first contains a vehicle identification number (VIN)
and the other a string of option descriptions. The table does get created
but is missing 16 rows. What would constitute a fatal error in an operation
like this?
Unfortunately the proc does not have any error handling.
I have been looking for info about the results of a proc crash in the
calling program and have found nothing so far.

Any help with this would be appreciated, we urgently need to get this fixed.
The data is used as a data feed to some advertising Web sites and if the
data is not transmitted often enough the data will be deleted.

Ragnar

Andrew John
9/13/2003 1:20:15 PM

Ragnar,

If you aren't getting useable information from the error catch in VB,
try running the stored procedure using QueryAnalyser.

As T-SQL has no error handling (yet), execution just stops when a
severe error is encountered, and an execption is thrown back to the client.
You could try placing a whole lot more print statements in, to localise the
error, but easier would be to use Query Analyser's debugging function,
assuming you don't get enough information on the problem by just running
the procedure in QA.

Regards
AJ

[quoted text, click to view]

sql server programming : Problem with stored procedure error