none
SQL Server ADO Output Param Issue param not returned when the proc returns a recordset RRS feed

  • Question

  • I am calling a stored proc using ADO via Excel VBA.  The stored proc has an output parameter that is not being set if I also return a recordset. If I do not return a recordset then the output param gets set.

    Using Microsoft ActiveX Data Objects 2.8 Lib. Note that I can’t use a newer version due to client issues

    Any thoughts as to why this is happening.

    --------------------------------------------------------------------------------------------------

    Stored Proc Code

    Alter PROCEDURE [dbo].[zExcel_ScheduleAdherDetail_Assoc]

           -- Add the parameters for the stored procedure here

          

           @AssocID Int,

           @StartDate as smalldatetime,

           @EndDate as smalldatetime,

           @AssocName as varchar(100) output

    AS

    BEGIN

           SET NOCOUNT ON;

           -- Update Assoc name

           SELECT @AssocName = LastName + ', ' +  FirstName

           FROM   DimAssociates

           WHERE (AssocID = @AssocID)

           --=========================

           --== Select Data ... if I comment out yellow part then the output param gets set

           --=========================

          

           SELECT *

           From Projects

           Where  AssocId =      @AssocID

    --------------------------------------------------------------------------------------------------

    VBA Code

    With DataBase.SQLCmd

          

            .CommandType = adCmdStoredProc

            .CommandText = "zExcel_ScheduleAdherDetail_Assoc"

           

            '*** Remove Parameters for gettinng report id

            While .Parameters.Count <> 0

                .Parameters.Delete 0

            Wend

           

            Set param = .CreateParameter("@AssocId", adInteger, adParamInput)

                        .Parameters.Append param

           

            Set param = .CreateParameter("@StartDate", adDate, adParamInput)

                        .Parameters.Append param

            Set param = .CreateParameter("@EndDate", adDate, adParamInput)

                        .Parameters.Append param

                       

             Set param = .CreateParameter("@AssocName", adVarChar, adParamOutput, 100)

                        .Parameters.Append param

          

            .Parameters("@AssocId") = lAssocID

            .Parameters("@StartDate") = mdStartDate

            .Parameters("@EndDate") = mdEndDate

           

            Set rs = .Execute()

     Debug.Print .Parameters("@AssocName").Value ‘ Nothing returned if I have a select                              statement

                    

        End With

    ---------------------

    Thanks in advance,

    Stewart


    Stewart Rogers DataSort Software, L. C.

    Monday, April 6, 2015 7:45 PM

Answers

All replies