Nice article. On a related note do you have any ideas on how to detect whether a stored procedure completed execution when run from ADO?
For instance take the following SQL;
CREATE TABLE TestTable (
x INT NOT NULL
CONSTRAINT CK_TestTable_x CHECK(x = 1)
)
GO
CREATE PROCEDURE TestSP
AS
BEGIN
BEGIN TRANSACTION
INSERT INTO TestTable (x) VALUES (2)
IF @@ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
RETURN -1
END
INSERT INTO TestTable (x) VALUES (1)
IF @@ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
RETURN -2
END
COMMIT TRANSACTION
RETURN 0
END
GO
and the following VB program;
Dim conn As Connection
Dim cmd As Command
Dim prm As Parameter
Set conn = New Connection
Set cmd = New Command
Call conn.Open("File Name=c:\localhost.udl")
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "TestSP"
Set cmd.ActiveConnection = conn
Set prm = cmd.CreateParameter("", adInteger, adParamReturnValue)
Call cmd.Parameters.Append(prm)
cmd.Execute
Call MsgBox(prm.Value)
conn.Close
Set conn = Nothing
Set cmd = Nothing
ADO raises an error for the constraint violation, but actually the stored procedure handles it. To further confuse an error is not raised if the insert statements are swapped so it is the second that fails instead of the first.
Any ideas?