troubleshooting Question

ADO SQL Server using command for stored procedure

Avatar of Doug Foster
Doug FosterFlag for United States of America asked on
Microsoft SQL ServerVisual Basic Classic
5 Comments1 Solution264 ViewsLast Modified:
I have some code using Excel 2007 vba, which should work, when I try to bring a sql server 2008 stored procedure into an ado recordset, it isn't working. I  get a closed recordset.  

The sp should be returning a recordset.  I run this same code against another procedure with dummy input parameters and I get a recordset just fine.  The input variables are simple, just an integer input.   The sp works fine when I run it on its own.

What am I missing?

I have the procedure code and vb code attached separately...
ALTER procedure [dbo].[sp_PostVOD](@StartYrMo int, @EndYrMo int) as

-- execute sp_PostVOD '201006', '201008'

Declare @StartDate smalldatetime, @EndDate smallDateTime, @YearMonth int
Declare @DatesTable table(idKey tinyint identity(1,1), YearMonth int, StartDate smalldatetime, EndDate smalldatetime)
declare @LastIdKey tinyint, @idKey tinyint

insert @DatesTable (YearMonth, StartDate, EndDate)
select	clYearMonth, clStartDate, clEndDate   
from	Calendar cl
where	clYearMonth between @StartYrMo and @EndYrMo

Select @LastIdKey = @@IDENTITY

Create table #Totals(
	shKey int, nwKey smallint, snKey smallint, epKey smallint, 
	nwName varchar(100), snName varchar(100), epName  varchar(50), adName  varchar(50), brName  varchar(50), spPosition varchar(50), 
	SpotsPerEpisode int, shStart smalldatetime, shEnd smalldatetime, trYearMonth int, trCount int, Imps int)

select @idKey=1
While @idKey < @LastIdKey + 1	begin
	select @YearMonth=YearMonth, @StartDate=StartDate, @EndDate=EndDate from @DatesTable where idKey = @idKey
	insert #Totals(
			shKey, nwKey, snKey, epKey, nwName, snName, epName, adName, brName, spPosition, SpotsPerEpisode, shStart, shEnd, trYearMonth, trCount, Imps)
	select	vod.shKey, vod.nwKey, vod.snKey, vod.epKey, nwName, snName, epName, adName, brName, spPosition, SpotsPerEpisode, shStart, shEnd,
			trYearMonth, trCount, Imps = SpotsPerEpisode * trCount
	from	vw_VODPost vod
	left	join (select shKey, SpotsPerEpisode=count(*) from vw_Spots group by shKey) spc on vod.shKey = spc.shKey
	left	join Transactions tr on tr.trAssetID = vod.shAssetId and tr.nwKey = vod.nwKey
	where	trYearMonth=@YearMonth and
			(shStart between @StartDate and @EndDate or shEnd between @StartDate and @EndDate or 
			(shStart < @StartDate and shEnd > @EndDate)) 
	set @idKey = @idKey + 1 

select adName, brName, nwName, snName, epName, SpotsPerEpisode=SUM(SpotsPerEpisode), Transactions=SUM(trCount), Imps=SUM(imps)
from	#Totals
Group	by adName, brName, nwName, snName, epName
order	by adName, brName, nwName, snName, epName
VB CODE (not complete procedure)

    Dim oRS As New ADODB.Recordset
    Dim oFld As ADODB.Field
    Dim cmd As ADODB.Command
    Set cmd = New ADODB.Command

    sDB = "tng_discovery"
    sTableName = "Transactions"
    If Not OpenSQLConn(sDB) Then GoTo exitMe 'open global variable connection
    sStartDate = "201006"
    sEndDate = "201008"
    With cmd
       .ActiveConnection = goConn
       .CommandType = adCmdStoredProc
       .CommandText = "sp_PostVOD"

       .Parameters.Append cmd.CreateParameter("@StartYrMo", adInteger, adParamInput, 4, Int(Val(sStartDate)))
       .Parameters.Append cmd.CreateParameter("@EndYrMo", adInteger, adParamInput, 4, Int(Val(sEndDate)))

        'procedure returns recordset already in first row
       Set oRS = .Execute
    End With

    If oRS.State = adStateOpen Then
        If Not (oRS.BOF And oRS.EOF) Then
            MsgBox "Field: " & oRS.Fields(1).Value
        End If
    End If
    MsgBox "Done!  "
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 5 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros