Supply chain | Supply Chain Management, Commerce

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

(0) Share

Report

Posted on by Retha

1,457

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

I have the same question (0)

All responses (5)

Answers (0)

Retha 1,457 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Suggested answer

Shahil 635 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Retha 1,457 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Retha 1,457 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
MYGz 2,162 on at

Like (0)

Report

Shahil Ram

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

Was this reply helpful? Yes No

Join the conversation

Community site session details

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

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Leaderboard > Supply chain | Supply Chain Management, Commerce

Featured topics

Product updates

Community site session details

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

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Subscribe to this forum!

Select categories

Leaderboard > Supply chain | Supply Chain Management, Commerce

Featured topics

Product updates