Output from Stored Procedue

Output from Stored Procedue

Post by Darin Horto » Sat, 08 Dec 2001 04:09:53



I have a stored procedure that creates a new tracking # and inserts that
number into a tracking database. I would like the SP to return that number
to my ADP.

SP is:

Alter Procedure TL_NextTrackingNo

(


)

As

SET NOCOUNT ON




FROM TL_Link_Tracking_Log

WHERE Date_Entered = dbo.Short_Date(GetDate())



ELSE

BEGIN



END

return

Routine to call it is:

    Dim con As ADODB.Connection
    Dim cmd As New ADODB.Command

    Set con = CurrentProject.Connection
    With cmd
        .ActiveConnection = con
        .CommandText = query_name
        .CommandType = adCmdStoredProc
        .Execute , array(link_no, tracking_no)

    End With

    Set con = Nothing
    Set cmd = Nothing

I get the error

Parameter object is improperly defined. Inconsistent or incomplete
information was provided.

 
 
 

Output from Stored Procedue

Post by Sue Hoegemeie » Sun, 09 Dec 2001 09:39:39


When you do an execute off of the command object, the parameters
are RecordsAffected, Parameters, Options. So it looks like you
are passing the array for records affected and nothing for
parameters. But...you don't want to use that approach for
parameters when using output parameters as you won't get the
correct values - it's a known issue/feature.
You'll need to explicitly create your parameters on the command
object with CreateParameter methods and then just do an execute
on the command object.

--Sue

On Thu, 6 Dec 2001 13:09:53 -0600, "Darin Horton"


>I have a stored procedure that creates a new tracking # and inserts that
>number into a tracking database. I would like the SP to return that number
>to my ADP.

>SP is:

>Alter Procedure TL_NextTrackingNo

>(



>)

>As

>SET NOCOUNT ON




>FROM TL_Link_Tracking_Log

>WHERE Date_Entered = dbo.Short_Date(GetDate())



>ELSE

>BEGIN



>END

>return

>Routine to call it is:

>    Dim con As ADODB.Connection
>    Dim cmd As New ADODB.Command

>    Set con = CurrentProject.Connection
>    With cmd
>        .ActiveConnection = con
>        .CommandText = query_name
>        .CommandType = adCmdStoredProc
>        .Execute , array(link_no, tracking_no)

>    End With

>    Set con = Nothing
>    Set cmd = Nothing

>I get the error

>Parameter object is improperly defined. Inconsistent or incomplete
>information was provided.


 
 
 

1. output parameters stored procedures / extended stored procedures

I have a problem concerning the returning of stored
procedure output values in my C++ application using
ADODB

When calling a stored procedure sp_A which on his turn
calls an extended stored procedure xp_X (i.e xp_sqlmaint)
the output parameters (and return value) of sp_A cannot be
achieved in the C++ application. No error is indicated,
the value in my GetParameter call just remains unchanged

/******************/

as






else


/**********************/

When replacing the extended stored procedure by a 'normal'
stored procedure sp_B, everything works fine.
/**************/
create sp_B
as
  return 1
/**************/

Any idea or explination is wellcome.....

thx,
Henk Devolder

2. Week number to date??

3. Error at obtaining output parameters of the stored procedure, calling other stored procedure

4. *** CINECA - DATA WAREHOUSE LABORATORY ***

5. Calling a Java Stored Procedure from another Java Stored Stored Procedure

6. Credit Card Auth

7. Nested Stored Procedures & Output Parameters

8. Data disappearing

9. Cursor Type Output Parameter for SQL Stored Procedure

10. Initializing output variables for stored procedures!!

11. Output parameter not listed for Execute SQL task that calls stored procedure

12. SQL 2000 Stored Procedure OUTPUT

13. Stored Proc Return values / Output Params w ADO and SQL Server 7