Skip to main content

Notifications

Announcements

No record found.

Dynamics 365 Community / Forums / Commerce forum / A database error has o...
Commerce forum
Suggested answer

A database error has occurred when trying to log into CloudPOS after a DB refresh

Posted on by 1,423

The user testing environment on a Microsoft-managed tier-2 environment was recently upgraded to 10.0.12 (10.0.507.30031), Same version as 1 of my cloud-hosted development environments. The backup of UAT that I had before on my Dev environment works fine. But to get more up to date data for debugging I exported the database from UAT and imported it into my Dev environment.

I ran the provisioning tool against the Dev environment..
I ran the initialize commerce scheduler with YES to remove setup.
I went to the Commerce Shared Parameters and created an entry for my Dev environment  retail server under the Identity providers.
I then made sure the 'DefaultChannelDatabase batch' has the status of Waiting and then  I ran the distribution scheduler for all jobs (job 9999). I also ran the Full db Sync on the channel database for job '9999'. I waited until the jobs under the Download Sessions showed status applied.
I then opened the CloudPOS solution in VS 2017 and built it and then ran it. It needs to activate my device and I get a Database error has occurred. It indicated that step 1 of 8 failed due to a number sequence.
I then looked in the eventViewer and see the following error:
"at Microsoft.Dynamics.Commerce.Runtime.DataServices.SqlServer.ChannelSqlServerDataService.GetReceiptNumberResetInfoByFunctionalityProfileId(GetReceiptNumberResetInfoRequest request)
"
The eventViewer also tells me that  crt.RECEIPTNUMBERRESETINFOVIEW is an unknown object. 
I then looked in SSMS and I could not find the view crt.RECEIPTNUMBERRESETINFOVIEW. I looked in another Dev box that we have that is still on Version: 10.0.12 (10.0.507.20026) and the view is in SSMS under AxDB. I then logged onto the RDP of UAT and looked in SSMS and the view doesn't exist there. Now it can either be because on the tier-2 environment the channel DB is on another server because we make use of cloud hosted scale units. But on the Dev box the channel DB is the "default" DB which is in SSMS together with D365.
I have done everything I usually do when  I refresh the database on my Dev environment and never had this problem.  I did ran a synchronize database in VS 2017 and I build my custom model under the Dynamics 365 tab in VS. 
I even ran the Commerce channel schema for AX7 this time and choose to generate the queries and it still didn't put the missing view in AxDB.
I then applied the provision tool again against my Dev environment and this time when I want to run CloudPOS from within VS 2017, I get the following error in the eventViewer:

"

Exception: Microsoft.Dynamics.Commerce.Runtime.StorageException: Failed to read from the database. See inner exception for details
DatabaseErrorCode: 0 ---> Microsoft.Dynamics.Commerce.Runtime.Data.DatabaseException: Database error 'Unknown' occurred for 'execute stored procedure '[crt].GETVISUALPROFILE''. Could not find stored procedure 'crt.GETVISUALPROFILE'. ---> System.Data.SqlClient.SqlException: Could not find stored procedure 'crt.GETVISUALPROFILE'.

"

So I ran the initialize commerce scheduler and the channel database full DB sync again. After the download sessions showed applied, I built the CloudPOS solution in Vs 2017 again and ran it again.. I still get the databsae error occured and looking in eventViewer, it shows again the error that the stored procedure crt.GETVISUALPROFILE is missing.

There is 2 things that I did notice with this refresh is that 'DefaultChannelDatabase batch' used to be under batch jobs for company DAT only. Now there shows 2, one for DAT and one for our company. I changed both to have status "Waiting" instead of Withhold.

Also, the channel database group used to have only the option "Default". It now has the option "Default" and "Legacy". It defaulted "Legacy" and I changed it to "Default" .  Could this be the problem, should I have left it as ""Legacy". Is it controlled by some feature that was turned on in UAT and now is turned on in my Dev environment too but because I switched the channel database group to "Default" instead of leaving it on "Legacy", maybe that is the reason the view is missing?

So what changed between the v10.0.12 hotfix 20026 and 30031. It clearly doesn't create the channel databse correctly

  • MYGz Profile Picture
    MYGz 1,794 on at
    RE: A database error has occurred when trying to log into CloudPOS after a DB refresh

     Shahil Ram 

    Trying what you suggested. Reapplying the same update because I first updated then restored the UAT DB. Hope it works.

  • Retha Profile Picture
    Retha 1,423 on at
    RE: A database error has occurred when trying to log into CloudPOS after a DB refresh

    I logged a ticket with Microsoft about this and here is there explanation regarding my issue:

    ****

    After reviewing the case, I noticed the UAT environments are using RCSU which is something aside from the core AXDB and also based on the explanation that was provided to me, we no longer patch the "default channel database" which is embedded inside of the AXDB in UAT and Prod. Knowing this, the SQL objects in UAT are not going to match in comparison to the dev environment because the code is outdated. In a summary the code in dev is more recent/updated than UAT and because of that during the import of the database it will fail.

    ****

    So the steps in the refresh document will work for when the tier-2 environment isn't using RCSU. But in our case it is, so Microsoft gave me a script that I needed to run to delete the whole channel database on my Dev environment. The application of the service deployable package was then able to recreate the channel DB.

  • Retha Profile Picture
    Retha 1,423 on at
    RE: A database error has occurred when trying to log into CloudPOS after a DB refresh

    I found the last service update for v10.0.12 and tried to upgrade my development environment it but the upgrade failed on step 57.

    It tried to run  stored procedure that isn't available  in the imported database.

    So the upgrade also doesn't fix the channel database. It expects the channel database to be already created with everything in needs.

    So the 'Post steps for commerce environments' is also not correct.

  • Suggested answer
    Shahil Profile Picture
    Shahil 635 on at
    RE: A database error has occurred when trying to log into CloudPOS after a DB refresh

    That is correct.

    You will need to do the Post steps for commerce environments.

    This will create the required commerce channel functionalities.

    A potential solution you can try is to run the same service update that is currently installed after UAT refresh if you don't want to go to the latest version. This should recreate the channel database.

  • Retha Profile Picture
    Retha 1,423 on at
    RE: A database error has occurred when trying to log into CloudPOS after a DB refresh

    I use the following document when I refresh a Dev box. The steps in this document changed since the last time I did a refresh:

    docs.microsoft.com/.../dbmovement-scenario-exportuat

    First of all, when they are talking about Commerce environments I am assuming they are talking about Retail and not E-Commerce.

    Apparently I don't need to do the reprovisioning tool anymore. However I'm not sure if it applies to only Production to UAT refresh or to also UAT to Dev refresh which is a manual process with export and import of the DB.

    I ran the reprovisioning tool now twice and it is not fixing my error.

    Can somebody please explain the following that is in the document:

    ***

    Post steps for Commerce environments

    If you are using Commerce channels, when importing a database to a developer environment, which was originally exported from a self-service sandbox, the following additional steps must be performed on the destination developer environment. Without completing these steps, Commerce channels will not function.

    To restore Commerce channels functionality, apply the latest Microsoft service update or quality update, which will create the channel database.

    To restore any previously deployed channel database extensions, re-apply the corresponding Retail self-service deployable package.

    ***

    Are they saying that the usual steps of reprovisioning tool and then Initialize of Commerce scheduler and then the run of the distribution scheduler and full DB sync on the channel DB will not create the channel DB correct? Because that is how it looks like currently for me. the Channel DB is not created properly and that is why the CloudPOS errors when it wants to activate the device.  There are views and stored procedures missing.

    I cannot run a latest Microsoft service update  or quality update on my Dev box just to create a channel DB correctly. This specific Dev box needs to be on the standard version that is in production, which in our case is currently on v10.0.12.

    While my other Dev box is already on v10.0.14 because we started working on that to get it ready for UAT before it goes into production. so one will always be a version or 2 behind the other one.  So applying the latest update after a refresh is out of the question.

Helpful resources

Quick Links

Replay now available! Dynamics 365 Community Call (CRM Edition)

Catch up on the first D365 Community Call held on 7/10

Community Spotlight of the Month

Kudos to Saurav Dhyani!

Congratulations to the June Top 10 community leaders!

These stars go above and beyond . . .

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 287,986 Super User

#2
Martin Dráb Profile Picture

Martin Dráb 225,588 Super User

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans