Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics GP (Archived) / GP 2018 R2: database m...
Microsoft Dynamics GP (Archived)

GP 2018 R2: database maintenance results are blank

(0) ShareShare
ReportReport
Posted on by Kaylasch 249

Hello,

I have upgraded a customer to GP 2018 R2 on a new SQL server. I tried to run SQL maintenance to repoint functions and stored procedures to the new SQL server. I can get the first two databases to finish successfully, but the remaining companies do not show up on the results window. I checked in activity in SQL, and the databases do not have any connections to them. I ran DB Maintenance as an administrator, and I have the same results with windows auth, and sa.

*This post is locked for comments

  • Mariano Gomez Profile Picture
    Mariano Gomez 26,225 on at
    RE: GP 2018 R2: database maintenance results are blank

    Kayla,

    Glad I could help. Please keep us posted if you run into anything else.

  • Kaylasch Profile Picture
    Kaylasch 249 on at
    RE: GP 2018 R2: database maintenance results are blank

    Hello Mariano,

    Thank you for your response! I have been following the KB to transfer GP to a new server when performing upgrades. I have also added additional steps based on various issues I have faced with upgrades. A while back, I had a customer use drillback in Excel, and encountered the error that a connection could not be established after an upgrade. I opened a support case and they had me run database Maintenance to address the issue. I added it to my steps to try to prevent the issue from happening in the future. When I move to a new SQL server, I run a script to delete the Excel and SSRS reports in SY40800 and syDeployedReports, and then I redploy them after I upgrade the databases. If that will take care of the repointing that the db maintenance is performing, then I will go ahead and refrain from doing this step in future upgrades.

    I appreciate your help, and I am glad I learned something new from this post!

    Regards,

    Kayla

  • Verified answer
    Mariano Gomez Profile Picture
    Mariano Gomez 26,225 on at
    RE: GP 2018 R2: database maintenance results are blank

    Let me ask a different question.

    Did you follow the instructions in the KB article I indicated above? This should have been the only upgrade checklist to follow, especially if you were transferring from one server to another and performing an upgrade. If you did follow the KB, then you shouldn't have needed to run any additional steps, much less the ones in the KB article you posted above. Now, if you are getting the errors indicated in the article you posted, the easiest thing *by far* is to redeploy your SSRS and Excel based reports, rather than trying to resolve this issue with yet another complicated procedure. Database Maintenance was really designed for extreme situations requiring to recreate just a few procs here and there.

    If you have custom SSRS and Excel Reports, save those off first (using Report Builder and Excel, respectively), then redeploy the reports and re-add your custom reports next.

    Now, if you did not follow the KB article, then I am not sure how much Database Maintenance is going to help, considering that it only works for core GP and Microsoft add-ons - this means, no 3rd party procs or functions which may need to be repointed as well.

    If you are at a point where you can restore the original databases, I would much suggest you execute the steps in the KB article I posted.

  • Kaylasch Profile Picture
    Kaylasch 249 on at
    RE: GP 2018 R2: database maintenance results are blank

    Hello Mariano,

    Thank you for your response! I have been using the database maintenance utility as part of my upgrade checklist when moving to a new SQL server with a different SQL server/Instance name, because of the issue in article support.microsoft.com/.../a-connection-microsoft-dynamics-gp-could-not-be-established-be-sure-yo

    Is this no longer a valid or necessary step?

  • Suggested answer
    Mariano Gomez Profile Picture
    Mariano Gomez 26,225 on at
    RE: GP 2018 R2: database maintenance results are blank

    If you did not use the following KB article - support.microsoft.com/.../how-to-transfer-an-existing-microsoft-dynamics-gp-microsoft-small-busi - to perform your migration/upgrade you will be in for a long ride. There should be no reason for you to have to repoint anything if you've followed the steps in that KB article.

  • Richard Wheeler Profile Picture
    Richard Wheeler 75,788 Moderator on at
    RE: GP 2018 R2: database maintenance results are blank

    Here are a few ideas:

    1) Are you logged in as 'sa'?

    2) If you do a select * from dynamics..sy01500 do you see these companies listed?

    3) Are these company databases online in SSMS?

  • Kaylasch Profile Picture
    Kaylasch 249 on at
    RE: GP 2018 R2: database maintenance results are blank

    Hello Derek,

    I skip selecting DYNAMICS and try to get the functions and stored procs on the companies first. Weird thing is, it will work for the first two companies, but the rest show blank. There are no connections to the databases when I check activity in SQL, and I make sure to have SSMS closed when trying to run it. I will restart the SQL service and see if that helps.

  • Verified answer
    Derek Albaugh Profile Picture
    Derek Albaugh on at
    RE: GP 2018 R2: database maintenance results are blank

    I'm wondering if you may be running into this similar issue:

    1. Make sure all users are out of Dynamics GP and SQL Server. Stop and restart the SQL Server service.

    2. Run sp_who2 and make sure no SPIDS for GP databases.

    3. Launch Database Maintenance Utility from Dynamics GP 2018 R2 by right-clicking on DBMaintenance file and choosing 'Run As Administrator'

    4. In the Database Maintenance Utility, once I enter the SQL Server and system database, when I run sp_who2 again, I see two SPIDS for my system database that I specified, both show 'AWAITING COMMAND'.

    5. Marking the system and company databases, all features and all objects to re-create in the Database Maintenance Utility, nothing happens and it just goes to the Finish screen with no errors or anything showing at all.

    6. If I repeat the steps 3-5 but only marking the company databases in the Utility, not the system database, everything works fine.

    Work-around steps:

    7. If I launch the Database Maintenance Utility again, put in the SQL connection information, select the system and company databases, all modules and all objects to re-create, but stop there....

    8. Running sp_who2 again, I still see the two records for my system database for Dynamics GP, so I use the KILL command to kill/remove the 2nd SPID, leaving only one record for my system database.

    9. At that point, back in the Database Maintenance Utility, I click to continue and now it will re-create the selected objects for the selected modules, for both the system and company databases for Microsoft Dynamics GP, without errors. The Finish window shows green check-marks on everything.

    I don't know that we've seen where the Database Maintenance Utility shows the system database and/or some company databases but not others, usually if there is a connection to the databases at all, DMU will just thrown an error when you attempt to re-create any of the objects.

    Thanks

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Vahid Ghafarpour – Community Spotlight

We are excited to recognize Vahid Ghafarpour as our February 2025 Community…

Tip: Become a User Group leader!

Join the ranks of valued community UG leaders

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 292,516 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 231,321 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans