Getting RETURN_VALUE from SQL Server to ADO 
Author Message
 Getting RETURN_VALUE from SQL Server to ADO

Hi Everyone, I hope someone can help, this is driving me mad !!

Have a SQL Server 7.0 or 2000 Stored Procedure, as follows:

CREATE PROC spProc
(

)
AS


IF 1 = 1
BEGIN

   RAISERROR('Something happened', 16, 1)
   RETURN -101
END

RETURN 0
GO

When i execute the following code from a SQL Query window, i get my expected
results:




Server: Msg 50000, Level 16, State 1, Procedure spProc, Line 12
Something happened
ReturnValue ReturnCode
----------- -----------
       -101          99

(1 row(s) affected)

Now from a VB6 COM+ component using ADO 2.7, setup something like this:

Public Function Update
   On Error Goto ErrorHandler
   Dim oCmd As ADODB.Command

   Set oCmd = New ADODB.Command

   With oCmd
      .ActiveConnection = ....
      .CommandText = "spProc"
      .CommandType = adCmdStoredProc

      .Parameters.Append .CreateParameter(, adInteger, adParamReturnValue)

adParamOutput, 4)

      .Execute , , adExecuteNoRecords
   End With

   Set oCmd.ActiveConnection = Nothing
   Set oCmd = Nothing
   Exit Function

ErrorHandler:
   Debug.Print oCmd.Parameters(0).Value, oCmd.Parameters(1).Value
   Debug.Print Err.Source, Err.Number, Err.Description
End Function

I would expect the debug statements in the error handler to return the same
results as the SQL Query window, but it doesn't. both parameters are Empty.

Can anyone tell me how to get a error code back from a SP using the
RAISERROR function ?

Thanks in advance,
James Richardson.



Tue, 07 Sep 2004 01:24:40 GMT  
 Getting RETURN_VALUE from SQL Server to ADO
Have you tried checking the Errors collection of the connection object
during execution of your error handler?

Simon Taylor
Resource Data, Inc.


Quote:
> Hi Everyone, I hope someone can help, this is driving me mad !!

> Have a SQL Server 7.0 or 2000 Stored Procedure, as follows:

> CREATE PROC spProc
> (

> )
> AS


> IF 1 = 1
> BEGIN

>    RAISERROR('Something happened', 16, 1)
>    RETURN -101
> END

> RETURN 0
> GO

> When i execute the following code from a SQL Query window, i get my
expected
> results:




> Server: Msg 50000, Level 16, State 1, Procedure spProc, Line 12
> Something happened
> ReturnValue ReturnCode
> ----------- -----------
>        -101          99

> (1 row(s) affected)

> Now from a VB6 COM+ component using ADO 2.7, setup something like this:

> Public Function Update
>    On Error Goto ErrorHandler
>    Dim oCmd As ADODB.Command

>    Set oCmd = New ADODB.Command

>    With oCmd
>       .ActiveConnection = ....
>       .CommandText = "spProc"
>       .CommandType = adCmdStoredProc

>       .Parameters.Append .CreateParameter(, adInteger, adParamReturnValue)

> adParamOutput, 4)

>       .Execute , , adExecuteNoRecords
>    End With

>    Set oCmd.ActiveConnection = Nothing
>    Set oCmd = Nothing
>    Exit Function

> ErrorHandler:
>    Debug.Print oCmd.Parameters(0).Value, oCmd.Parameters(1).Value
>    Debug.Print Err.Source, Err.Number, Err.Description
> End Function

> I would expect the debug statements in the error handler to return the
same
> results as the SQL Query window, but it doesn't. both parameters are
Empty.

> Can anyone tell me how to get a error code back from a SP using the
> RAISERROR function ?

> Thanks in advance,
> James Richardson.



Tue, 07 Sep 2004 01:37:16 GMT  
 Getting RETURN_VALUE from SQL Server to ADO
Why do it this way...

CREATE PROC spProc
(

)
AS


IF 1 = 1
BEGIN

   RAISERROR('Something happened', 16, 1)
   RETURN -101
END

RETURN 0
GO

Why not;

Create Proc spProc
(
)
AS

if 1 = 1
    BEGIN
           return 99
    END

return 0

Then all you've got to do is


.Execute
xyx =  objCmd.Parameters(0).Value



Tue, 07 Sep 2004 01:48:44 GMT  
 Getting RETURN_VALUE from SQL Server to ADO
Hi,

Try to add SET NOCOUNT ON at the beginning of SP and SET
NOCOUNT OFF at the end.

Val

Quote:
>-----Original Message-----
>Hi Everyone, I hope someone can help, this is driving me
mad !!

>Have a SQL Server 7.0 or 2000 Stored Procedure, as
follows:

>CREATE PROC spProc
>(

>)
>AS


>IF 1 = 1
>BEGIN

>   RAISERROR('Something happened', 16, 1)
>   RETURN -101
>END

>RETURN 0
>GO

>When i execute the following code from a SQL Query

window, i get my expected

- Show quoted text -

Quote:
>results:




>Server: Msg 50000, Level 16, State 1, Procedure spProc,
Line 12
>Something happened
>ReturnValue ReturnCode
>----------- -----------
>       -101          99

>(1 row(s) affected)

>Now from a VB6 COM+ component using ADO 2.7, setup

something like this:

- Show quoted text -

Quote:

>Public Function Update
>   On Error Goto ErrorHandler
>   Dim oCmd As ADODB.Command

>   Set oCmd = New ADODB.Command

>   With oCmd
>      .ActiveConnection = ....
>      .CommandText = "spProc"
>      .CommandType = adCmdStoredProc

>      .Parameters.Append .CreateParameter(, adInteger,
adParamReturnValue)

adInteger,
>adParamOutput, 4)

>      .Execute , , adExecuteNoRecords
>   End With

>   Set oCmd.ActiveConnection = Nothing
>   Set oCmd = Nothing
>   Exit Function

>ErrorHandler:
>   Debug.Print oCmd.Parameters(0).Value, oCmd.Parameters
(1).Value
>   Debug.Print Err.Source, Err.Number, Err.Description
>End Function

>I would expect the debug statements in the error handler
to return the same
>results as the SQL Query window, but it doesn't. both

parameters are Empty.

- Show quoted text -

Quote:

>Can anyone tell me how to get a error code back from a SP
using the
>RAISERROR function ?

>Thanks in advance,
>James Richardson.

>.



Tue, 07 Sep 2004 03:19:05 GMT  
 Getting RETURN_VALUE from SQL Server to ADO
I would also like to use the WITH LOG option on the RAISERROR function, for
error logging in the event log. Yes, i could do as yo say, but this is just
a workround. Is this the expected behaviour when you use RAISERROR ?

Thanks again for you input.


Quote:
> Why do it this way...

> CREATE PROC spProc
> (

> )
> AS


> IF 1 = 1
> BEGIN

>    RAISERROR('Something happened', 16, 1)
>    RETURN -101
> END

> RETURN 0
> GO

> Why not;

> Create Proc spProc
> (
> )
> AS

> if 1 = 1
>     BEGIN
>            return 99
>     END

> return 0

> Then all you've got to do is


....))
> .Execute
> xyx =  objCmd.Parameters(0).Value



Tue, 07 Sep 2004 02:02:18 GMT  
 Getting RETURN_VALUE from SQL Server to ADO
Yes, I have.

Errors(0).Number = -2147217900
.Description = 'Something happened'
.NativeError = 50000
.Source = Microsoft OLE DB Provider for SQL Server

None of the parameter info.


Quote:
> Have you tried checking the Errors collection of the connection object
> during execution of your error handler?

> Simon Taylor
> Resource Data, Inc.



> > Hi Everyone, I hope someone can help, this is driving me mad !!

> > Have a SQL Server 7.0 or 2000 Stored Procedure, as follows:

> > CREATE PROC spProc
> > (

> > )
> > AS


> > IF 1 = 1
> > BEGIN

> >    RAISERROR('Something happened', 16, 1)
> >    RETURN -101
> > END

> > RETURN 0
> > GO

> > When i execute the following code from a SQL Query window, i get my
> expected
> > results:




> > Server: Msg 50000, Level 16, State 1, Procedure spProc, Line 12
> > Something happened
> > ReturnValue ReturnCode
> > ----------- -----------
> >        -101          99

> > (1 row(s) affected)

> > Now from a VB6 COM+ component using ADO 2.7, setup something like this:

> > Public Function Update
> >    On Error Goto ErrorHandler
> >    Dim oCmd As ADODB.Command

> >    Set oCmd = New ADODB.Command

> >    With oCmd
> >       .ActiveConnection = ....
> >       .CommandText = "spProc"
> >       .CommandType = adCmdStoredProc

> >       .Parameters.Append .CreateParameter(, adInteger,
adParamReturnValue)

> > adParamOutput, 4)

> >       .Execute , , adExecuteNoRecords
> >    End With

> >    Set oCmd.ActiveConnection = Nothing
> >    Set oCmd = Nothing
> >    Exit Function

> > ErrorHandler:
> >    Debug.Print oCmd.Parameters(0).Value, oCmd.Parameters(1).Value
> >    Debug.Print Err.Source, Err.Number, Err.Description
> > End Function

> > I would expect the debug statements in the error handler to return the
> same
> > results as the SQL Query window, but it doesn't. both parameters are
> Empty.

> > Can anyone tell me how to get a error code back from a SP using the
> > RAISERROR function ?

> > Thanks in advance,
> > James Richardson.



Tue, 07 Sep 2004 01:58:08 GMT  
 Getting RETURN_VALUE from SQL Server to ADO
Hi Val,

You're a star, works perfectly - Thanks very much !!

James.


Quote:
> Hi,

> Try to add SET NOCOUNT ON at the beginning of SP and SET
> NOCOUNT OFF at the end.

> Val

> >-----Original Message-----
> >Hi Everyone, I hope someone can help, this is driving me
> mad !!

> >Have a SQL Server 7.0 or 2000 Stored Procedure, as
> follows:

> >CREATE PROC spProc
> >(

> >)
> >AS


> >IF 1 = 1
> >BEGIN

> >   RAISERROR('Something happened', 16, 1)
> >   RETURN -101
> >END

> >RETURN 0
> >GO

> >When i execute the following code from a SQL Query
> window, i get my expected
> >results:




> >Server: Msg 50000, Level 16, State 1, Procedure spProc,
> Line 12
> >Something happened
> >ReturnValue ReturnCode
> >----------- -----------
> >       -101          99

> >(1 row(s) affected)

> >Now from a VB6 COM+ component using ADO 2.7, setup
> something like this:

> >Public Function Update
> >   On Error Goto ErrorHandler
> >   Dim oCmd As ADODB.Command

> >   Set oCmd = New ADODB.Command

> >   With oCmd
> >      .ActiveConnection = ....
> >      .CommandText = "spProc"
> >      .CommandType = adCmdStoredProc

> >      .Parameters.Append .CreateParameter(, adInteger,
> adParamReturnValue)

> adInteger,
> >adParamOutput, 4)

> >      .Execute , , adExecuteNoRecords
> >   End With

> >   Set oCmd.ActiveConnection = Nothing
> >   Set oCmd = Nothing
> >   Exit Function

> >ErrorHandler:
> >   Debug.Print oCmd.Parameters(0).Value, oCmd.Parameters
> (1).Value
> >   Debug.Print Err.Source, Err.Number, Err.Description
> >End Function

> >I would expect the debug statements in the error handler
> to return the same
> >results as the SQL Query window, but it doesn't. both
> parameters are Empty.

> >Can anyone tell me how to get a error code back from a SP
> using the
> >RAISERROR function ?

> >Thanks in advance,
> >James Richardson.

> >.



Tue, 07 Sep 2004 16:50:31 GMT  
 
 [ 7 post ] 

 Relevant Pages 

1. Not getting a return_value from SQL7 using ADO

2. Problem Accessing RETURN_VALUE from SQL Server stored procedures.

3. Getting tables name from SQL Server via ADO

4. Getting the counter value after an addnew-SQL SERVER 2000/ADO

5. VB Sql Server Via ADO Problem: [Microsoft][ODBC Sql Server Driver]

6. ADO, SQL Server, VB problem posted to the microsoft.public.data.ado group as well

7. VB6 - CR7 - SQL Server - Want to send either SQL or ADO recordset to report

8. VB6: Oracle 8/SQL Server 2000 compatibility problems using ADO 2.5 and SQL

9. sql-problem ADO - MS Sql Server

10. VB6: Oracle 8/SQL Server 2000 compatibility problems using ADO 2.5 and SQL Syntax

11. VB6: Oracle 8/SQL Server 2000 compatibility problems using ADO 2.5 and SQL Syntax

12. Getting ADO Sort property to work with Server Side Cursors

 

 
Powered by phpBB® Forum Software