Output Parameter Not Being Returned to Access 2013 From SPROC

I'm pretty new to SQL Server so I know I'm doing something wrong on the Access 2013 side.

I'm calling a SPROC that returns a recordset.  The SPROC also has an output parameters that returns the number of records in the recordset.  I test on SQL Server and the SPROC works as planned.

When I'm calling the SP from Access I'm getting zero as the number of recs returned, which I know isn't correct.

Can anyone spot what I'm doing wrong?

Here is the Access 2013 code:
' Begin Sproc Stuff ////////////////////////////////////////////////////////////
'
Dim rsIn2 As ADODB.Recordset
Dim cmd As ADODB.Command
Set cmd = New ADODB.Command

With cmd
    .CommandText = "aConvertspProdTaxAuthority"
    .CommandType = adCmdStoredProc
    setSQLConnection
    .ActiveConnection = gConnection    '
    .CommandTimeout = cSQLSP_5MinTimeout  ' set timeout to 5 minutes (300 second)
    '
    ' Output Parameter
    '
    .Parameters.Append .CreateParameter("returnTotalRecCount", adBigInt, adParamOutput)
         
    Set rsIn2 = New ADODB.Recordset
        With rsIn2
            .CursorLocation = adUseServer
            .CursorType = adOpenKeyset
            .LockType = adLockOptimistic
            .Open cmd
        End With
        '
        totRecs = Nz(cmd.Parameters("returnTotalRecCount"))
        '////////////////// From Here on down SPROC is the SAME ///////////////////////////

Open in new window

Here is the SQL SP
USE [JTSConversion]
GO
/****** Object:  StoredProcedure [dbo].[aConvertspProdTaxAuthority]    Script Date: 1/24/2018 10:00:53 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- 
-- =============================================
ALTER PROCEDURE [dbo].[aConvertspProdTaxAuthority] 
	-- Add the parameters for the stored procedure here
--	@LastIDProcessed  int      = 0,
--	@NumToRead        int      = 999999999,
	@NumRecsReturned  int      = 0   Output
AS
BEGIN

--Declare @EndingID as int

--set @EndingID = @LastIDProcessed + @NumToRead

	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT *
	From bView_buildProd_TaxAuthorityRecs_02
	Order By MuniCode, ControlNum, TaxType 
--	WHERE 
--         ID   > @LastIDProcessed and ID < @EndingID
--		  Order By ID 
		  set @NumRecsReturned = @@rowcount



END

Open in new window

LVL 1
mlcktmguyAsked:
Who is Participating?
 
Anthony PerkinsCommented:
It has been a while, and I suspect I have answered this question more than once before (@@ROWCOUNT doesn't  return any value is an example from over 15 years ago and which goes into tremendous detail, obviously far more than I could remember!).  

However, the problem is that you cannot return an output parameter and a resultset at the same time.  

To be perfectly accurate, you can retrieve the output parameter, however you have to close the resultset before can you inspect the value for the parameter.
0
 
Ryan ChongCommented:
quick try:

what if change:

.CursorLocation = adUseServer

Open in new window


to:
.CursorLocation = adUseClient

Open in new window


?
0
 
Anders Ebro (Microsoft MVP)Microsoft DeveloperCommented:
In this line:
 .Parameters.Append .CreateParameter("returnTotalRecCount", adBigInt, adParamOutput)

Open in new window

Your parameter is named "returnTotalRecCount"
yet your sproc is calling it "@NumRecsReturned"  
Maybe if you use the same variable name? :)
1
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Mark WillsTopic AdvisorCommented:
Here is a quick guide for ADO from MS : https://support.microsoft.com/en-us/help/194792/how-to-retrieve-values-in-sql-server-stored-procedures-with-ado

Bit over the top compared to your requirement, but has a very complete solution...

You''ll notice that it isnt matching names, but, always very best practice to do so IMO

If no recordset being returned, you can also add : Execute Options:=adExecuteNoRecords
0
 
mlcktmguyAuthor Commented:
No resolution on this one yet.  After reading the article Mark referenced I realized I hadn't declared it as an output parameter so I revised the statement to:


    .Parameters.Append .CreateParameter("returnTotalRecCount", adBigInt, adParamOutput, returnTotalRecCount)

Still returns zero

Ryan: Mark's article also make it clear that the cursor must be set to server
0
 
Anders Ebro (Microsoft MVP)Microsoft DeveloperCommented:
Did you fix the issue with using two different variable names?
0
 
mlcktmguyAuthor Commented:
Anders:  That is not an issue.
0
 
Mark WillsTopic AdvisorCommented:
Shouldn't there be a .value on line 26 of your Access code ie  totRecs = cmd.Parameters("returnTotalRecCount").value

And noticed line you open cmd on line 23 - probably should be / cant see cmd.execute

Also, do the right thing and make your output param "@NumRecsReturned"

so, change lines 16 and 26 appropriately...

Hard to say considering the access code is kind of obfuscated a bit ;)
0
 
mlcktmguyAuthor Commented:
Neither revision changed the result of totrecs equaling zero

Here is the test proc I setup:
Private Sub testNumReturnedRecords()

Dim returnTotalRecCount As Long
Dim totRecs As Long

Dim rsIn2 As ADODB.Recordset

Dim cmd As ADODB.Command
Set cmd = New ADODB.Command

With cmd
    .CommandText = "aConvertspProdTaxAuthority"
    .CommandType = adCmdStoredProc
    setSQLConnection
    .ActiveConnection = gConnection    '
    .CommandTimeout = cSQLSP_30MinTimeout  ' set timeout to 5 minutes (300 second)
    '
    ' Output Parameter
    '
    .Parameters.Append .CreateParameter("@NumRecsReturned", adBigInt, adParamOutput, returnTotalRecCount)
         
    Set rsIn2 = New ADODB.Recordset
        With rsIn2
            .CursorLocation = adUseServer
            .CursorType = adOpenKeyset
            .LockType = adLockOptimistic
            .Open cmd
        End With
        '
        totRecs = Nz(cmd.Parameters("@NumRecsReturned").Value)

        '
    Set .ActiveConnection = Nothing
    '
End With
'
rsIn2.Close
Set rsIn2 = Nothing


End Sub

Open in new window



I am using the same construct, without a .value and different variable names elsewhere in the application without issue.

Public Function GetTotalPayAmtForATaxAutorityID(passedTaxAuthroityID As Long, _
                                                Optional passedFromDepositDate As Variant = Null, _
                                                Optional passedThruDepositDate As Variant = Null) As Double
'
GetTotalPayAmtForATaxAutorityID = 0
'
Dim cmd As New ADODB.Command
'
Dim param1 As New ADODB.Parameter
Dim param2 As New ADODB.Parameter
Dim param3 As New ADODB.Parameter
Dim param4 As New ADODB.Parameter

With cmd
    .CommandText = "sptblPaymentsTaxAuthority_TAPaymentTotal_Get"
    .CommandType = adCmdStoredProc
    setSQLConnection
    .ActiveConnection = gConnection    '

        '
    .CommandTimeout = cSQLSP_30MinTimeout  ' set timeout to 5 minutes (300 second)

    '
    ' Input Params
    '
    Set param1 = .CreateParameter("passedTaxAuthroityID", adBigInt, adParamInput, , passedTaxAuthroityID)
    .Parameters.Append param1
    '
    Set param2 = .CreateParameter("passedTaxAuthroityID", adDBTimeStamp, adParamInput, , passedFromDepositDate)
    .Parameters.Append param2
    '
    Set param3 = .CreateParameter("passedTaxAuthroityID", adDBTimeStamp, adParamInput, , passedThruDepositDate)
    .Parameters.Append param3
    '
    ' Output Params
    '
    Set param4 = .CreateParameter("returnPayTotal", adDouble, adParamOutput)
    '
    .Parameters.Append param4
    
    .Execute Options:=adExecuteNoRecords
    Set .ActiveConnection = Nothing
    '
    Set param1 = Nothing
    Set param2 = Nothing
    Set param3 = Nothing
End With
'
GetTotalPayAmtForATaxAutorityID = Nz(cmd.Parameters("returnPayTotal"))
'

Set param4 = Nothing
'
Set cmd = Nothing
End Function

Open in new window

0
 
Mark WillsTopic AdvisorCommented:
In the first code snippet I dont see a cmd.execute

I do see that in the second code snippet.

In the first, you are going straight into a recordset and open it

But this is not my area of expertise - programming is such an individual experience :)
0
 
mlcktmguyAuthor Commented:
Thanks for the comment Mark.  I checked the sub with and without the .execute. It made no difference.  totrecs was always zero and all 345K records were read both ways.

The latest try:
Private Sub testNumReturnedRecords()

Dim returnTotalRecCount As Long
Dim totRecs As Long
Dim recsRead As Long

Dim rsIn2 As ADODB.Recordset

Dim cmd As ADODB.Command
Set cmd = New ADODB.Command

With cmd
    .CommandText = "aConvertspProdTaxAuthority"
    .CommandType = adCmdStoredProc
    setSQLConnection
    .ActiveConnection = gConnection    '
    .CommandTimeout = cSQLSP_30MinTimeout  ' set timeout to 5 minutes (300 second)
    '
    ' Output Parameter
    '
    .Parameters.Append .CreateParameter("@NumRecsReturned", adBigInt, adParamOutput, returnTotalRecCount)
    .Execute
         
    Set rsIn2 = New ADODB.Recordset
        With rsIn2
            .CursorLocation = adUseServer
            .CursorType = adOpenKeyset
            .LockType = adLockOptimistic
            .Open cmd
        End With
        '
        totRecs = Nz(cmd.Parameters("@NumRecsReturned").Value)
        
        If rsIn2.EOF Then
            '
        Else
            '
            '
            While Not rsIn2.EOF
            '
               '
                recsRead = recsRead + 1
        
                rsIn2.MoveNext
           Wend
        End If

        '
    Set .ActiveConnection = Nothing
    '
End With
'
rsIn2.Close
Set rsIn2 = Nothing

MsgBox "Read: " & Format(recsRead, cMJOIntFormat) & " records"


End Sub

Open in new window

0
 
Ryan ChongCommented:
I'm connecting to a MS SQL in Access and it's working fine using your latest codes posted in ID: 42450982

SnapShot.png
are you missing something? how's your ConnectionString looks like? are you connecting to correct database?
0
 
Ryan ChongCommented:
Mark's article also make it clear that the cursor must be set to server
it works for me if changing:
.CursorLocation = adUseServer

Open in new window

to:
.CursorLocation = adUseClient

Open in new window

0
 
Mark WillsTopic AdvisorCommented:
@Ryan,

Quite correct, either will work, but depending on resource, it can make a big difference in performance.

https://docs.microsoft.com/en-us/sql/ado/guide/data/the-significance-of-cursor-location
1
 
Ryan ChongCommented:
Tks for providing the info, Mark. It was an interesting read :)
0
 
Anthony PerkinsCommented:
Have you tried using the correct data type as in instead of:
    .Parameters.Append .CreateParameter("@NumRecsReturned", adBigInt, adParamOutput, returnTotalRecCount)
Use:
    .Parameters.Append .CreateParameter("@NumRecsReturned", adInteger, adParamOutput)

I am not sure why you are attempting to pass in a value (returnTotalRecCount), as it is just going to get ignored: You have defined the parameter as adParamOutput not adParamInputOutput, but even if you change that the Stored Procedure will ignore any value you pass in,  Having said that if you are bound and determined to pass it in then then you also need to specify the Size parameter.
0
 
mlcktmguyAuthor Commented:
Anthony:
Changed it to:
    .Parameters.Append .CreateParameter("@NumRecsReturned", adInteger, adParamOutput)
Still returning zero

Ryan: Yes I don't think it's the connection string because it reads all 245k records in the returned recordset, even though it returns zero for the number of records.
0
 
Ryan ChongCommented:
Yes I don't think it's the connection string because it reads all 245k records in the returned recordset, even though it returns zero for the number of records.

but it works for me! so we got 245k vs 7 records, could that be an issue just because of no of records then will return different recordcount, which becomes 0 vs 7?? I can upload the Access sample if necessary, I'm using Access 2010 connecting to MS SQL 2008 R2

to be frank... are you trying to loop through all these 245k records in your program? what operations need to be done here? just wondering why you don't try to do it at Server end (stored procedure) instead?
0
 
Mark WillsTopic AdvisorCommented:
@mlcktmguy,

yes it will return those rows, because you define a new recordset and then open it. so the connection is good.

cmd can also return a recordset after the execute.

once execute then get the output param

then get the resultset

or

dont do noount in the SP, then use parameter(0) for the return result from the open.

@ryan, yes please share your code.
0
 
Ryan ChongCommented:
@Mark

here we go
29080213.accdb
0
 
mlcktmguyAuthor Commented:
Ryan: Thank you for posting your code,  It is line for line what I have in my code yet yours returns the count.  Now I'm even more confused.
These are one time conversion routines Ryan.  That's why reading the entire recordset is required.

Could you post your SPROC also?  Maybe something is different there.

Mark
dont do noount in the SP, then use parameter(0) for the return result from the open.
I understand not doing nocount but don't understand 'parameter(0)'.  Could you exlain?

Thanks to all for your help in resolving this issue.
0
 
Mark WillsTopic AdvisorCommented:
When using command parameters, the interface to the stored procedure is using the stored proc @ names (and that is what it will match on if used.

But there are also ordinal parameters such as the zero parameter as a return value - adParamReturnValue

cmd.parameters.Append cmd.CreateParameter(, adInteger, adParamReturnValue, , NULL)   ' return value  

https://docs.microsoft.com/en-us/sql/ado/reference/ado-api/parameters-collection-ado

As for differences in code,  there will be something ever so subtle, and likely to be one of those headslappring "D'OH" moments.

Just escaping the eyes at the moment....
0
 
Ryan ChongCommented:
@mlcktmguy

for your ref:

-- =============================================
-- 
-- =============================================
ALTER PROCEDURE [dbo].[aConvertspProdTaxAuthority] 
	-- Add the parameters for the stored procedure here
--	@LastIDProcessed  int      = 0,
--	@NumToRead        int      = 999999999,
	@NumRecsReturned  int      = 0   Output
AS
BEGIN

--Declare @EndingID as int

--set @EndingID = @LastIDProcessed + @NumToRead

	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT *
	From bView_buildProd_TaxAuthorityRecs_02
	Order By MuniCode, ControlNum, TaxType 
--	WHERE 
--         ID   > @LastIDProcessed and ID < @EndingID
--		  Order By ID 
		  set @NumRecsReturned = @@rowcount



END

Open in new window


no idea what was bView_buildProd_TaxAuthorityRecs_02. so i created it as a view:

ALTER VIEW [dbo].[bView_buildProd_TaxAuthorityRecs_02]
AS
SELECT     ID, MuniCode, ControlNum, TaxType
FROM         dbo.buildProd_TaxAuthorityRecs

GO

Open in new window


then i have table: buildProd_TaxAuthorityRecs

CREATE TABLE [dbo].[buildProd_TaxAuthorityRecs](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[MuniCode] [nvarchar](50) NULL,
	[ControlNum] [int] NULL,
	[TaxType] [nvarchar](50) NULL,
 CONSTRAINT [PK_buildProd_TaxAuthorityRecs] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

Open in new window

0
 
mlcktmguyAuthor Commented:
Thanks Ryan.  You're assumption is correct, it is a view.
0
 
Ryan ChongCommented:
ok, so is my sample worked for you?
0
 
mlcktmguyAuthor Commented:
Ryan I tired your code line for line and still had an issue.  I suspect Anthony is correct and I'm sure it is somehow related to my configuration but I'm juts not seeing it.

Thanks for all the help and suggestions.  It is much apprecaited
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.