Go Premium for a chance to win a PS4. Enter to Win

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 560
  • Last Modified:

Return records affected by SQL Stored procedure (MERGE) to Access

I'm running a SQL Server stored procedure by running a pass-thru query from Access using syntax similar to:

Set qdf = currentdb.querydefs("qry1")
qdf.SQL = "EXEC df_MyStoredProcedure NULL, '2014-09-05', NULL"

The stored procedure on the server aggregates data from several tables into a table used for reporting, and is running properly.  As part of this aggregation, it uses a MERGE to simultaneously update and append records from the aggregation to the table used for reporting.

What I need to know is how to get the total number of records updated and appended (combined) back to the qdf.RecordsAffected property.  The final process in the stored procedure looks like:
MERGE tbl_sysProduction AS target
USING zz_Staging_Production AS source
ON target.Entity_ID = Source.Entity_ID
AND Target.docDate = Source.DocDate
    UPDATE SET Gas = source.Gas, 
               Oil = source.Oil, 
               Water = source.water
    INSERT (Entity_ID, docDate, Gas, Oil, Water, DS_Stamp)
    VALUES (source.Entity_ID, source.docDate, source.Gas, source.Oil, source.Water, source.DS_Stamp)

Open in new window

Dale Fye
Dale Fye
  • 4
  • 4
2 Solutions
Jack LeachCommented:

How about using an OUTPUT clause with the MERGE execution, then writing those outputs as a return from the SP?  From there, you set the ReturnsRecords property of the Access Passthrough to True, and open it as you would a regular DAO recordset (eg, Set rs = CurrentDb.OpenRecordset(qdf.Name, dbOpenSnapshot).

http://msdn.microsoft.com/en-us/library/bb510625.aspx (MERGE)
http://msdn.microsoft.com/en-us/library/ms177564.aspx (OUTPUT)

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
I pulled this off in a previous project.   Not sure about qdf.RecordsAffected property, but you could return the two @variables below as the SP's return set, then do what you want..

Declare @inserted int, @updated int
DECLARE @merge TABLE (action varchar(10))

-- Your above MERGE statement goes here, but remove the semi-colon and add the below line
OUTPUT $action into @merge;

-- Now you can dump the row counts into variables, and do what you want with them...
    @inserted = SUM(CASE WHEN action = 'INSERT' THEN 1 ELSE 0 END),
    @updated = SUM(CASE WHEN action = 'UPDATE' THEN 1 ELSE 0 END)

-- such as return them in the SP
SELECT @inserted as inserted_row_count, @updated as updated_row_count
Dale FyeAuthor Commented:

I've made the modifications recommended by Jim to my stored procedure and when I run it from SQL Server, it gives me the record counts I'm looking for.

But for some reason, I cannot seem to get the passthrough query to accept a change in the ReturnsRecords property.  I've tried:
currentdb.QueryDefs("qry_SQL_PassThru_Returns_Records").returnsrecords = True

Open in new window

and that seems to be accepted (no errors), but when I try:

Open in new window

the response = FALSE

any ideas?
Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Jack LeachCommented:
Try setting a dedicated DAO.Database object, then pulling the querydef reference from there and changing it.  I think it's similar to relinking TableDefs, where it doesn't work because CurrentDB() isn't a persistent copy of the database object.

DIm db AS DAO.Database
Dim qd AS DAO.QueryDef

Set db = CurrentDb()
Set qd = db.QueryDefs(yourDef)

Open in new window

Jack LeachCommented:
Also, FWIW, I find it easier to keep a separate saved querydef for each standard PT configuration, so one that returns records and one that doesn't (and maybe a few others, I forget offhand).
Dale FyeAuthor Commented:

Tried that.
Public Sub SetReturnsRecords()

    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Set db = CurrentDb
    Set qdf = db.QueryDefs("qry_SQL_PassThru_Returns_Records")
    qdf.ReturnsRecords = True
    Debug.Print qdf.ReturnsRecords
    Debug.Print qdf.Connect

End Sub

Open in new window

Immediate results:
ODBC;DRIVER=SQL Server Native Client 10.0;SERVER=xxx-xxx;Trusted_Connection=Yes;APP=SSMA;DATABASE=xxx_System_Tables

I wish they would expose the pass-thru querydef properties in a properties window!
Dale FyeAuthor Commented:

re:"Also, FWIW, I find it easier to keep a separate saved querydef for each standard PT configuration"

That is how I created this on.  I copied it from:  qry_SQL_PassThru

Which has ReturnsRecords = False.
Jack LeachCommented:
>> I wish they would expose the pass-thru querydef properties in a properties window!  <<

They do... open the PT in the query designer and press Alt+Enter for the properties window, and it ought to be the 3rd or 4th one down.

2010/2013 anyway, I never did much with 2007 and don't recall, but I'm pretty sure it's standard throughout versions.
Dale FyeAuthor Commented:
Thanks, guys.

Jim, the code for the Merge worked well.

Jack, , after all these years someone finally tells me how to do that.  Very intuitive, NOT.  What ever happened to right-click, Properties?  Changing the ReturnsRecords in the properties window worked.

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now