[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How to return ado recordset from temp table in stored procedure

Posted on 2011-10-18
12
Medium Priority
?
1,961 Views
Last Modified: 2012-06-21
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?
0
Comment
Question by:mimpact
  • 6
  • 6
12 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36989963
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36989972
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
 

Author Comment

by:mimpact
ID: 36990097
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 2000 total points
ID: 36990261
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
 

Author Comment

by:mimpact
ID: 36990296
Cool!
Problem 1 solved -
What's the next most important snaffu to fix?
0
 

Author Comment

by:mimpact
ID: 36990401
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
 

Author Comment

by:mimpact
ID: 36990414
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36993808
>>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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36993914
>>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
 

Author Comment

by:mimpact
ID: 36996269
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
 

Author Comment

by:mimpact
ID: 36996331
Sorry - obvious question:
select @Qty = Sum(qty) .....

Thanks for all your help with this.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36996887
>>How does
SELECT SUM(Qty)<<
Oops, sorry about that.  Glad you got it working.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question