How to return ado recordset from temp table in stored procedure

I am using a stored procedure to collate some information into a temp table (#tmp) and I want to return the resultant set to an  ado recordset in MS Access or VBScript.

The stored prodecure works like this:

/* procedure sptest  */

create table #tmp(txtFilename varchar(100))
Insert #tmp(txtFileName)
select top 100 txtFileName from tblfile1 where criteria1

Insert #tmp(txtFileName)
select top 100 txtFileName from tblfile2 where criteria2

select * from #tmp

GO

The caller is like this:

function testsp

Dim con As New ADODB.Connection
Dim rs as ADODB.Recordset
con.ConnectionString = "Provider=SQLOLEDB;Server=xxxxxx;Initial Catalog=XXXX;User id=XXXX;Password=xxxx"
con.open
set rs = new adodb.Recordset
con.sptest rs
if not rs is nothing then
if rs.recordcount > 0 then

' do stuff

end if
end if
end function


If I simply select records from a real table in the stored procedure, the records are returned to the calling script and into the recordset, but if I use a temp table, and select from that, the recordset is not created.


Any ideas what I'm doing wrong?
mimpactAsked:
Who is Participating?
 
Anthony PerkinsConnect With a Mentor Commented:
It is as I stated, you need to add SET NOCOUNT ON as in:
ALTER     Procedure Rep_Totals

@MonthNo int = 9,
@YearNo int = 2011

AS

declare @np int
declare @retE int
declare @retP int
declare @supp int

SET NOCOUNT ON

create table #Totals (Title varchar(50), Qty int)

Unrelated, but there are many problems with your Stored Procedure as written and if you are having perfomance problems it can be vastly improved.

>>It seems to me that I should be able to go thru the #totals table at the end and get the qty results i want but I don't know how.<<
Let's address the subject of your original question first.

0
 
Anthony PerkinsCommented:
Without seeing the full Stored Procedure it is difficult to say.  At the very least you are missing SET NOCOUNT ON at the very start of your Stored Procedure.  Incidentally, if that is the sum total of your Stored Procedure, than you should not need a temporary table.
0
 
Anthony PerkinsCommented:
Also, a TOP clause without an ORDER BY clause does not make a lot of sense, unless you don't care what results you get back as they could be random.
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
mimpactAuthor Commented:
OK - sorry

Those snippets are just indicative - not real - just to show the process. (thought it would be simpler)

Here's the whole 9 yards

SP:

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
SET NOCOUNT ON
GO
ALTER     Procedure Rep_Totals

@MonthNo int = 9,
@YearNo int = 2011

AS

declare @np int
declare @retE int
declare @retP int
declare @supp int

create table #Totals (Title varchar(50), Qty int)

Insert #Totals (Title,Qty)
/* No of files received */
SELECT     'Total Files Received' AS Title, COUNT(*) AS qty
FROM         tblFile
WHERE     (intFileTypeURN = 1)
      AND (DATEPART(m, dteCreatedDate) = @MonthNo)
      AND (DATEPART(yyyy, dteCreatedDate) = @YearNo)

/* No of records rec */

Insert #Totals (Title,Qty)
SELECT     'Total records Received' as Title,SUM(intNumberOfStatements) AS qty
FROM         tblFile
WHERE     (intFileTypeURN = 1)
      AND (DATEPART(m, dteCreatedDate) = @MonthNo)
      AND (DATEPART(yyyy, dteCreatedDate) = @YearNo)

/* Suppressed records */
Insert #Totals (Title,Qty)
SELECT     'Total Suppressed' as Title, COUNT(tblFileStructure.intURN) AS qty
FROM         tblFile INNER JOIN
                      tblFileStructure ON tblFile.intURN = tblFileStructure.intFileURN AND
                      tblFile.intURN = tblFileStructure.intFileURN
WHERE     (tblFile.intFileTypeURN = 1)
      AND (tblFileStructure.txtSuppressPrint = 'Y')
      AND (DATEPART(m,tblFile.dteCreatedDate) = @MonthNo)
      AND (DATEPART(yyyy, tblFile.dteCreatedDate) = @YearNo)

set @supp = (
SELECT     COUNT(tblFileStructure.intURN) AS qty
FROM         tblFile INNER JOIN
                      tblFileStructure ON tblFile.intURN = tblFileStructure.intFileURN AND
                      tblFile.intURN = tblFileStructure.intFileURN
WHERE     (tblFile.intFileTypeURN = 1)
      AND (tblFileStructure.txtSuppressPrint = 'Y')
      AND (DATEPART(m,tblFile.dteCreatedDate) = @MonthNo)
      AND (DATEPART(yyyy, tblFile.dteCreatedDate) = @YearNo)
)                    
/* no of errors */

Insert #Totals (Title,Qty)
SELECT     'Total Errors/Not processed' as Title,SUM(intNumberOfWarnings + intNumberOfFatals) AS Qty
FROM         tblFile
WHERE     (intFileTypeURN = 1)
      AND (DATEPART(m, dteCreatedDate) = @MonthNo)
      AND (DATEPART(yyyy, dteCreatedDate) = @YearNo)

set @np =
(
SELECT     SUM(intNumberOfWarnings + intNumberOfFatals) AS Qty
FROM         tblFile
WHERE     (intFileTypeURN = 1)
      AND (DATEPART(m, dteCreatedDate) = @MonthNo)
      AND (DATEPART(yyyy, dteCreatedDate) = @YearNo)
)

/* No Ret via email*/
Insert #Totals (Title,Qty)
SELECT     'Total Returned Via Email' AS Title, COUNT(tblFileStructure.intURN) AS qty
FROM         tblFile INNER JOIN
                      tblFileStructure ON tblFile.intURN = tblFileStructure.intFileURN AND
                      tblFile.intURN = tblFileStructure.intFileURN
WHERE     (tblFile.intFileTypeURN = 10)
      AND (DATEPART(m, tblFile.dteCreatedDate) = @MonthNo)
      AND (DATEPART(yyyy, tblFile.dteCreatedDate) = @YearNo)
      AND (tblFileStructure.txtReturnFairfax = '01')

set @retE =
(
SELECT     COUNT(tblFileStructure.intURN) AS qty
FROM         tblFile INNER JOIN
                      tblFileStructure ON tblFile.intURN = tblFileStructure.intFileURN AND
                      tblFile.intURN = tblFileStructure.intFileURN
WHERE     (tblFile.intFileTypeURN = 10)
      AND (DATEPART(m, tblFile.dteCreatedDate) = @MonthNo)
      AND (DATEPART(yyyy, tblFile.dteCreatedDate) = @YearNo)
      AND (tblFileStructure.txtReturnFairfax = '01')
)

/* No Ret via Print*/
Insert #Totals (Title,Qty)
SELECT     'Total Returned via Print' as Title,COUNT(tblFileStructure.intURN) AS qty
FROM         tblFile INNER JOIN
                      tblFileStructure ON tblFile.intURN = tblFileStructure.intFileURN AND
                      tblFile.intURN = tblFileStructure.intFileURN
WHERE     (tblFile.intFileTypeURN = 5)
      AND (DATEPART(m, tblFile.dteCreatedDate) = @MonthNo)
      AND (DATEPART(yyyy, tblFile.dteCreatedDate) = @YearNo)  
      AND (tblFileStructure.txtReturnFairfax = '01')
      AND (tblFile.txtFileName LIKE '%999999%')

set @retP =
(
SELECT     COUNT(tblFileStructure.intURN) AS qty
FROM         tblFile INNER JOIN
                      tblFileStructure ON tblFile.intURN = tblFileStructure.intFileURN AND
                      tblFile.intURN = tblFileStructure.intFileURN
WHERE     (tblFile.intFileTypeURN = 5)
      AND (DATEPART(m, tblFile.dteCreatedDate) = @MonthNo)
      AND (DATEPART(yyyy, tblFile.dteCreatedDate) = @YearNo)  
      AND (tblFileStructure.txtReturnFairfax = '01')
      AND (tblFile.txtFileName LIKE '%999999%')
)
/* Records Email*/

Insert #Totals (Title,Qty)
SELECT     'Total Email records' AS Title, COUNT(tblFileStructure.intURN) AS qty
FROM         tblFile INNER JOIN
                      tblFileStructure ON tblFile.intURN = tblFileStructure.intFileURN AND
                      tblFile.intURN = tblFileStructure.intFileURN
WHERE     (tblFile.intFileTypeURN = 10)
      AND (tblFileStructure.txtReturnFairfax IS NULL)
      AND (DATEPART(m, tblFile.dteCreatedDate) = @MonthNo)
      AND (DATEPART(yyyy, tblFile.dteCreatedDate) = @YearNo)



Insert #Totals (Title,Qty)
select 'Total Records Processed' as Title,@np + @supp + @retE + @retP as Qty

/* Email bouncebacks */

Insert #Totals (Title,Qty)
SELECT     'Total Email Bouncebacks' as Title,COUNT(tblFileStructure.intURN) AS qty
FROM         tblFile INNER JOIN
                      tblFileStructure ON tblFile.intURN = tblFileStructure.intFileURN AND
                      tblFile.intURN = tblFileStructure.intFileURN
WHERE     (tblFile.intFileTypeURN = 5)
      AND (DATEPART(m, tblFile.dteCreatedDate) = @MonthNo)
      AND (DATEPART(yyyy, tblFile.dteCreatedDate) = @YearNo)
      AND (tblFileStructure.txtReturnFairfax IS NULL)
      AND (tblFile.txtFileName LIKE '%999999%')



select * from #totals





GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO


Call from MS Access ( although I actually want to do it from vbscript using a DTS to schedule it.)

Dim con As New ADODB.Connection
Dim rs As ADODB.Recordset
con.ConnectionString = conConnection & "User ID =" & ReadGV("UserID") & ";Password=" & ReadGV("Password")
' which end up in the shape "Provider=SQLOLEDB;Server=xxxxxx;Initial Catalog=XXXX;User id=XXXX;Password=xxxx"

con.Open
    Set rs = New ADODB.Recordset
    With rs
        .ActiveConnection = con
        .CursorLocation = adUseClient
        .CursorType = rrOpenDynamic
        .Open "Exec Rep_Totals 9,2011"
        If .EOF And .BOF Then ' gets error 'Operation not allowed when the object is closed'

            NoRecords = True

            Exit Function

        End If
   End With

' Intend to shorten the above With block to con.Rep_Totals rs

If Not rs Is Nothing Then
    rs.MoveLast
    rs.MoveFirst

    MsgBox rs.Fields(0)
   
   
End If

While we're at it, I have used a rather ham-fisted approach to summing some of the values by re-running the queries.  It seems to me that I should be able to go thru the #totals table at the end and get the qty results i want but I don't know how.

Thanks for your help.

0
 
mimpactAuthor Commented:
Cool!
Problem 1 solved -
What's the next most important snaffu to fix?
0
 
mimpactAuthor Commented:
I changed the way the subtotals are collated as follows

set @np = (SELECT Qty from #Totals where Title = 'Total Errors/Not processed')
set @supp = (SELECT Qty from #Totals where Title = 'Total Suppressed')
set @retE = (SELECT Qty from #Totals where Title = 'Total Returned via Print'    )
set @retP = (SELECT Qty from #Totals where Title = 'Total Returned Via Email'   )

Insert #Totals (Title,Qty)
select 'Total Records Processed' as Title,@np + @supp + @retE + @retP as Qty

which gets rid of all the spare re-queries.
Is there a better way to do that?

What other areas should be addressed as a matter of importance ?

cheers
0
 
mimpactAuthor Commented:
I've trolled around a bit and have yet to find out why NoCount stops the recordset from returning in this project?
Would you mind explaining that to me?
0
 
Anthony PerkinsCommented:
>>which gets rid of all the spare re-queries.<<
You are still querying the table 4 times.  At the very least do something like this:

DECLARE @qty int

SELECT SUM(Qty)
FROM #Totals
WHERE Title IN ('Total Errors/Not processed', 'Total Suppressed', 'Total Returned via Print', 'Total Returned Via Email')

Insert #Totals (Title,Qty)
select 'Total Records Processed' as Title, @Qty
0
 
Anthony PerkinsCommented:
>>why NoCount stops the recordset from returning in this project?<<
If you do not have SET NOCOUNT ON than the recordset picks up the message "(x row(s) affected)"
If you want to prove this to yourself, don't include SET NOCOUNT ON, but change your VB code as follows:

.Open "Exec Rep_Totals 9,2011"
Set rs = rs.NextRecordset          ' Add this line
If .EOF And .BOF Then ' gets error 'Operation not allowed when the object is closed'


On a separate subject instead of using the Recordset's Open method consider using the Connection or Command object's Execute method.  This produces a forward-only, read-only cursor which should be faster.  It will also preclude you from using the following unnecessary code:
    rs.MoveLast
    rs.MoveFirst
0
 
mimpactAuthor Commented:
Thanks for that.

How does
SELECT SUM(Qty)
FROM #Totals
WHERE Title IN ('Total Errors/Not processed', 'Total Suppressed', 'Total Returned via Print', 'Total Returned Via Email')
get into @qty?

0
 
mimpactAuthor Commented:
Sorry - obvious question:
select @Qty = Sum(qty) .....

Thanks for all your help with this.
0
 
Anthony PerkinsCommented:
>>How does
SELECT SUM(Qty)<<
Oops, sorry about that.  Glad you got it working.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.