SBX - Search With Button

SBX - Forum Post Title

SSRS Automated E-Mail - Files are returning incorrect data

Microsoft Dynamics GP Forum

Nicole Apolo asked a question on 15 Apr 2019 2:02 PM
My Badges

Question Status

Verified

Hello,

We have been using SSRS Subscriptions for quite some time now.  The last few weeks we are noticing that the email notifications are being sent to users twice. One with correct data and one with incorrect data(without a clear pattern for the incorrect file).

11:00am today, we received an Accounts Due report that has all of the correct open AR and I was able to open the customer record and match every transaction.

11:02am today, Same report "Criteria aka Customer Criteria" yet inaccurate transaction output. 

Out of the three customers in the inaccurate report, one is correct, one customer is completely missing and one is pulling 1 invoice that is accurate, 1 that is missing and another that appears on report, but system shows it was paid last month. 

3/25/2019 the report generated once correctly.

4/1/2019 the report generated correctly and then an incorrect report was also sent 1 minute later.

4/8/2019 the report generated correctly and then an incorrect report was also sent 1 minute later. (Exact same report as the 4/1/19 incorrect rep)

4/15/2019 the report generated correctly and then an incorrect report was also sent 1 minute later. (Exact same report as the 4/1/19 incorrect rep)

4/15/2019 the report generated correctly ( I created a new subscription test) - duplicate issue does not take place.

Last week when I saw this pattern I asked our admin to delete the subscription for me and reenter it.  That did not resolve the automated subscription that takes place every Monday at 11am.   I feel like the automated subscriptions are stuck and for some reason grabbing historical data.  The only thing that took place around this time frame is that we took one of our server backups and restored it in our new server environment for testing. But, live production should not have been affected. 

Any one have an idea what could make this happen?

Looking at the Report Server dbo.Subscriptions the last run time only shows the correct 11:00 am report execution. I do not see the 11:02 record listed in this table.

Not sure where it is coming from or how to stop it.  We have 199 subscriptions in total and staff receive numerous reports. 

Thank you for your help!

Reply
Nicole Apolo responded on 15 Apr 2019 2:27 PM
My Badges

OY - I just figured out the subscriptions were snapped on the new server and picked up the scheduled run time.  As I built the picture to create this question, the answer was right in front of me.

SMH

Reply
Ven Sharma responded on 15 Apr 2019 2:30 PM
My Badges
Verified Answer

Hello Nicole- Are you sure the duplicate emails are firing from your live instance of GP/SSRS? May be it is coming from a QA/sandbox environment.

If you have eliminated that, I would then check with the DBA to narrow down from SQL Agent what job executed at 4/1/2019 a minute later than the correct report.

All the subscriptions are tied to SQL agent jobs that have a really length name for example

459AFD3A-C022-420A-B56B-3F5344A9B185 - also the job properties should have

This job is owned by a report server process. Modifying this job could result in database incompatibilities. Use Report Manager or Management Studio to update this job.

Once that is established you can request the DBA to disable that second job and then see if the duplicate emails fire.

Alternatively by opening Report Manager - in SSRS and navigating to My subscriptions should list all the subscriptions scheduled to run on the SSRS side. You should be able to narrow down the duplicate if any.

Best

Reply
Nicole Apolo responded on 15 Apr 2019 2:35 PM
My Badges

yep, found they were coming from a test environment. That would explain why all of our independent tests were working great and the scheduled subscriptions were duplicating.  

Reply
Ven Sharma responded on 15 Apr 2019 2:30 PM
My Badges
Verified Answer

Hello Nicole- Are you sure the duplicate emails are firing from your live instance of GP/SSRS? May be it is coming from a QA/sandbox environment.

If you have eliminated that, I would then check with the DBA to narrow down from SQL Agent what job executed at 4/1/2019 a minute later than the correct report.

All the subscriptions are tied to SQL agent jobs that have a really length name for example

459AFD3A-C022-420A-B56B-3F5344A9B185 - also the job properties should have

This job is owned by a report server process. Modifying this job could result in database incompatibilities. Use Report Manager or Management Studio to update this job.

Once that is established you can request the DBA to disable that second job and then see if the duplicate emails fire.

Alternatively by opening Report Manager - in SSRS and navigating to My subscriptions should list all the subscriptions scheduled to run on the SSRS side. You should be able to narrow down the duplicate if any.

Best

Reply

SBX - Two Col Forum

SBX - Migrated JS