Solved

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

Posted on 2014-09-06
9
518 Views
Last Modified: 2014-09-07
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"
qdf.Execute

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
WHEN MATCHED THEN 
    UPDATE SET Gas = source.Gas, 
               Oil = source.Oil, 
               Water = source.water
WHEN NOT MATCHED THEN
    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

0
Comment
Question by:Dale Fye (Access MVP)
  • 4
  • 4
9 Comments
 
LVL 4

Expert Comment

by:Jack Leach
ID: 40308148
Hi,

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)

hth
0
 
LVL 65

Accepted Solution

by:
Jim Horn earned 300 total points
ID: 40308188
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...
SELECT
    @inserted = SUM(CASE WHEN action = 'INSERT' THEN 1 ELSE 0 END),
    @updated = SUM(CASE WHEN action = 'UPDATE' THEN 1 ELSE 0 END)
FROM
    @merge

-- such as return them in the SP
SELECT @inserted as inserted_row_count, @updated as updated_row_count
0
 
LVL 47

Author Comment

by:Dale Fye (Access MVP)
ID: 40308468
Jim/Jack,

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:
?currentdb.QueryDefs("qry_SQL_PassThru_Returns_Records").returnsrecords

Open in new window

the response = FALSE

any ideas?
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 4

Expert Comment

by:Jack Leach
ID: 40308471
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)
etc...

Open in new window

0
 
LVL 4

Expert Comment

by:Jack Leach
ID: 40308473
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).
0
 
LVL 47

Author Comment

by:Dale Fye (Access MVP)
ID: 40308490
Jack,

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:
False
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!
0
 
LVL 47

Author Comment

by:Dale Fye (Access MVP)
ID: 40308493
Jack,

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.
0
 
LVL 4

Assisted Solution

by:Jack Leach
Jack Leach earned 200 total points
ID: 40308496
>> 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.
0
 
LVL 47

Author Closing Comment

by:Dale Fye (Access MVP)
ID: 40308568
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.
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

821 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