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



           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



            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 Rogers DataSort Software, L. C.

    Monday, April 6, 2015 7:45 PM


All replies