Raising an error in SP and sending to ADO

[Banana]

I'm trying to figure out what is the best method of communicating a error back to an ADO provider. My initial reaction was to use RAISEERROR, but it is my understanding that ADO doesn't make it easy to retrieve the specific error message raised by the SQL Server.

I also considered using a Return value but it sounds to me that I'd be just getting one more recordset and while I could just do .NextRecordset, it sounds too much for what should be a quick peek to verify that the operation suceeded.

So, what would be considered the most simplest way to inform the client via ADO that the operation was aborted by the SP (due to excessive number of records)?

 

[SQL_Hell]

Output parameter or return value will do, you can then exec the procedure in ADO and load the output parameter into a VB variable and do IF ELSE or whatever from there.

It always worth putting your t-sql code within a TRY CATCH block in your stored proc, like so:

ALTER PROCEDURE [dbo].[SelectObjects] 
@error int output
AS
BEGIN TRY
SELECT * FROM SYS.SYSOBJECTS
END TRY
BEGIN CATCH
SET @error = 1
END CATCH

Further more, you can load the full error message into a table, like so:

insert dbo.ErrorLog (
ErrorNumber
,ErrorSeverity
,ErrorState
,ErrorProcedure
,ErrorLine
,ErrorMessage
,EventType
,Parameters
,EventInfo
) 
exec DBCC INPUTBUFFER('+CAST(@@spid as varchar)+') WITH NO_INFOMSGS

 

[SQL_Hell]

To capture the output parameter from ado command object execution, please see this example:

Dim objConn
Dim objCmd
 
'Instantiate objects
Set objConn = Server.CreateObject("ADODB.Connection")
set objCmd = Server.CreateObject("ADODB.Command")
conn.Open Application("ConnectionString")
 
With objCmd
    .ActiveConnection = conn 'You can also just specify a connection string here
    .CommandText = "sp_InsertArticle" 
    .CommandType = adCmdStoredProc 'Requires the adovbs.inc file or typelib meta tag
 
    'Add Input Parameters
    .Parameters.Append .CreateParameter("@columnist_id", adDouble, adParamInput, , columnist_id)
    .Parameters.Append .CreateParameter("@url", adVarChar, adParamInput, 255, url)
    .Parameters.Append .CreateParameter("@title", adVarChar, adParamInput, 99, url)
    .Parameters.Append .CreateParameter("@description", adLongVarChar, _
adParamInput, 2147483647, description)
 
    'Add Output Parameters
    .Parameters.Append .CreateParameter("@link_id", adInteger, adParamOutput, , 0)
 
    'Execute the function
    'If not returning a recordset, use the adExecuteNoRecords parameter option
    .Execute, , adExecuteNoRecords
    link_id = .Parameters("@link_id")
End With

 

[Banana]

Excellent, just what I needed. Thanks again!