Re: Stored Procedure does not return errors to Excel VBA

Michael D. Bauer wrote:
I am calling a stored procedure (SP) from Excel, which uses a cursor
to process records and inserts them into other tables. The problem is
that the SP does not return an error back to Excel, when it
encounters a Foreign Key Violation (error level 16).

The ADODB.Connection object does not contain any errors after the SP
call: There is no ADODB.Error in the Errors collection of my
connection object, although I know there were errors!

When I run the SP from SQL Query Analyzer, I receive error messages
for any Foreign Key Violation that is encountered. E.g.
"Server: Msg 547, Level 16, State 1, Procedure
proc_Upload_Sales_Monthly, Line 56
INSERT statement conflicted with COLUMN FOREIGN KEY constraint
'FK_TBL_...'....
The statement has been terminated."

I read the Microsoft article "How To Retrieve Values in SQL Server
Stored Procedures with ADO"
(http://support.microsoft.com/default.aspx?scid=kb;en-us;194792) but
it doesn't answer my question why an error such as this is not
returned to Visual Basic.

Any help would be appreciated. Thank you very much.

1. Cursors should be avoided if possible.
2. See if this helps:
http://www.sommarskog.se/error-handling-II.html#NOCOUNT

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"


.