Prompting for values in SQL script

This is something that I forget 2 seconds after I’ve used it so now I’ll put it here as a note to self.

I have a series of TSQL scripts that I use once in different circumstances and often it’s a while between each go. In some of the I need to add some environment specific information and instead of having to scroll down through the text the query editor offers a way of prompting for parameters through a tagging of text like this:

select <Parameter name, Data type, Default value>

Before executing the script you press CTRL+SHIFT+M (this is the part I usually forget) and that makes the editor prompt you like this:

Replace the Default value with the appropriate text and click OK. This will update your SQL script to this:

select 1234

Not the most mind-blowing thing in the world, but a great help.

 

Advertisements

Enable developer mode in the MPOS without rebuilding

In the Dynamics 365 POS it’s possible to enable a developer mode that gives you some additional options like seeing the id of the strings, grid views, coloring aids and so on. To enable these features you need to rebuild the POS with the value config.isDebugMode to true in the pos.js file. That’s easy in the CPOS but requires some build/deploy in MPOS.

Our POS developer gave me a tip to shortcut that … a lot. It’s a few easy steps and they go like this:

Enable Developer mode in Windows. That’s done in settings on the box running the POS

Start the POS and hit F12 to enable the developer aid

In the bottom of the console type in Commerce.Config.isDebugMode=true and press Enter

Go to your POS settings to enable the Developer mode flag in here.

This will make some things way easier. Notice, that when done like this it is only for the current session. Doing it in the pos.js file-way is permanent.

Changing Configuration keys in Dynamics 365 – Maintenance mode

Going into License configuration to enable or disable a configuration key you’ll probably see the warning:

This form is read-only unless the system is in the maintenance mode. Maintenance mode can be enabled in this environment by running maintenance job from LCS, or using Deployment.Setup tool locally

Here’s how to work around that in all but production environments:

• Tell the other users working on that environment, since you’ll be restarting the IIS during the process.
• Log on to the server running the AOS service and start up a command prompt in Administrator mode
• Run the following statement where you change K to the correct drive for your AOS service:

K:\AosService\PackagesLocalDirectory\Bin\Microsoft.Dynamics.AX.Deployment.Setup.exe –metadatadir K:\AosService\PackagesLocalDirectory –bindir K:\AosService\PackagesLocalDirectory\Bin –sqlserver . –sqldatabase axdb –sqluser <SQL admin user id> –sqlpwd <SQL users password> –setupmode maintenancemode –isinmaintenancemode true

• Restart the IIS with the iisreset command

This leaves the environment in maintenance mode. This doesn’t mean non-functional; but you probably would like to leave the maintenance mode as soon as possible.

When you are done you run this script from a command prompt in Administration mode (again replace K with the appropriate drive letter):

K:\AosService\PackagesLocalDirectory\Bin\Microsoft.Dynamics.AX.Deployment.Setup.exe –metadatadir K:\AosService\PackagesLocalDirectory –bindir K:\AosService\PackagesLocalDirectory\Bin –sqlserver . –sqldatabase axdb –sqluser <SQL admin user id> –sqlpwd <SQL users password> –setupmode maintenancemode –isinmaintenancemode false

Restart you IIS once more and you’re back on track.

Company size report in Dynamics 365

In older versions of Dynamics AX we had a report showing us the size of a company. It wasn’t pretty and it wasn’t fast. But it was nice to have though.

In Dynamics 365 for Operations it’s no longer in the menus; but don’t worry, it’s still there. All you need to do is to utilize how you can access menu items by building your own URLs:

https://<your-environment>aos.cloudax.dynamics.com/?cmp=<company-id>&mi=Output%3ASysCompanySize

That should give you the report:

 

Table browser in Dynamics 365

Normally when I need the table browser in Dynamics 365 I use a Chrome extension called “Table Browser Caller for D365FO“. Often you end up working in Incognito mode when having to access environments with a different AAD account. That requires you to make it available in Incognito mode:

BUT … what the extension does isn’t magic. What it does however is offering a very easy approach to building an URL that I never can remember. And when working in a browser without that extension you can write the URL change yourselve. So I figured that if I wrote it here I might remember it better or at least help somebody else remember it.

Add this to the URL to get the table browser: &mi=SysTableBrowser&TableName=. This will give you something like this:

Notice, that the table browser is being changed in a couple of areas compared to AX 2012. This means that changing data through the table browser is not allowed in production environments for example. Nice in regards to data consistency, but missed when having to dirty-fix data. 🙂

Running the CRT samples on AX7

The RetailSDK delivered with AX7 includes a lot of great sample code making it easier to get things rolling when learning how to customise the solution.

One of the sample projects is the CommerceRuntimeSamples. Before you are able to run it you need to do a couple of tweaks though. If not it will crash and burn with error messages that do point you in the right direction on how to fix it. This blog post is made to safe you some time and getting it fixed without having to go troubleshooting.

The first error that pops up is this one:

A configuration error in the Microsoft.Dynamics.Commerce.Runtime.dll. The reason is that it cannot find the right Operation Unit Id. It is specified in the commerceruntime.config and all you have to do is put in the operating id of the channel you would like to work with. In this case I use Houston, which has the id 039:

Next is this error:

A storage exception in the same dll and same piece of code. This time we have to look in the app.config file. In here we have a list of connection strings that all point at database names as they appeared in AX 2012 Retail. In AX7 demo servers all data for all channels is in the AxDB database so the fix is to change the database names in the connection strings to AxDB:

Now you are able to run the samples and start debugging to learn.

 

 

Retail designer – Creating a database role with database access

Using the designer in AX 2012 for designing POS-buttons, receipts and whatever usually works for us eggheads with admin rights to the servers. But when it comes to the real users they might end up with an error like this:

What you need to do is to grant the users that need to work with the designer some additional rights directly to the Dynamics AX database. Scary stuff and something we’d rather not do; but in this case there’s no way around it. The good news is that it is only a few tables.

Here’s what to do:

Log on to the Microsoft SQL Server Management Studio, right click on the Dynamics AX database and select New Query.

Paste in this script and execute it. It will create a database role with the appropriate rights to the tables

• RETAILBUTTONGRID
• RETAILBUTTONGRIDBUTTONS
• RETAILFORMLAYOUT
• RETAILIMAGES
• RETAILTERMINALCUSTOMFIELD
• RETAILTILLLAYOUT
• RETAILOPERATIONS

and select rights on these:

• RETAILSALESTAXOVERRIDE
• RETAILSTORETENDERTYPETABLE
• RETAILSALESTAXOVERRIDEGROUPMEMBER
• INVENTITEMBARCODE
• INVENTTABLE
• RETAILCHANNELTABLE
• ECORESPRODUCTTRANSLATION

Notice, that the names of these tables may vary from AX version to AX version, but you’ll probably be able to locate the right tables.

The script:

CREATE ROLE [AX_POS_Designer]
GO

GRANT DELETE ON [dbo].[RETAILBUTTONGRID] TO [AX_POS_Designer]
GRANT INSERT ON [dbo].[RETAILBUTTONGRID] TO [AX_POS_Designer]
GRANT SELECT ON [dbo].[RETAILBUTTONGRID] TO [AX_POS_Designer]
GRANT UPDATE ON [dbo].[RETAILBUTTONGRID] TO [AX_POS_Designer]
GRANT DELETE ON [dbo].[RETAILBUTTONGRIDBUTTONS] TO [AX_POS_Designer]
GRANT INSERT ON [dbo].[RETAILBUTTONGRIDBUTTONS] TO [AX_POS_Designer]
GRANT SELECT ON [dbo].[RETAILBUTTONGRIDBUTTONS] TO [AX_POS_Designer]
GRANT UPDATE ON [dbo].[RETAILBUTTONGRIDBUTTONS] TO [AX_POS_Designer]
GRANT DELETE ON [dbo].[RETAILFORMLAYOUT] TO [AX_POS_Designer]
GRANT INSERT ON [dbo].[RETAILFORMLAYOUT] TO [AX_POS_Designer]
GRANT SELECT ON [dbo].[RETAILFORMLAYOUT] TO [AX_POS_Designer]
GRANT UPDATE ON [dbo].[RETAILFORMLAYOUT] TO [AX_POS_Designer]
GRANT DELETE ON [dbo].[RETAILTILLLAYOUT] TO [AX_POS_Designer]
GRANT INSERT ON [dbo].[RETAILTILLLAYOUT] TO [AX_POS_Designer]
GRANT SELECT ON [dbo].[RETAILTILLLAYOUT] TO [AX_POS_Designer]
GRANT UPDATE ON [dbo].[RETAILTILLLAYOUT] TO [AX_POS_Designer]
GRANT DELETE ON [dbo].[RETAILIMAGES] TO [AX_POS_Designer]
GRANT INSERT ON [dbo].[RETAILIMAGES] TO [AX_POS_Designer]
GRANT SELECT ON [dbo].[RETAILIMAGES] TO [AX_POS_Designer]
GRANT UPDATE ON [dbo].[RETAILIMAGES] TO [AX_POS_Designer]
GRANT DELETE ON [dbo].[RETAILTERMINALCUSTOMFIELD] TO [AX_POS_Designer]
GRANT INSERT ON [dbo].[RETAILTERMINALCUSTOMFIELD] TO [AX_POS_Designer]
GRANT SELECT ON [dbo].[RETAILTERMINALCUSTOMFIELD] TO [AX_POS_Designer]
GRANT UPDATE ON [dbo].[RETAILTERMINALCUSTOMFIELD] TO [AX_POS_Designer]
GRANT DELETE ON [dbo].[RETAILOPERATIONS] TO [AX_POS_Designer]
GRANT INSERT ON [dbo].[RETAILOPERATIONS] TO [AX_POS_Designer]
GRANT SELECT ON [dbo].[RETAILOPERATIONS] TO [AX_POS_Designer]
GRANT UPDATE ON [dbo].[RETAILOPERATIONS] TO [AX_POS_Designer]
GRANT SELECT ON [dbo].[RETAILSALESTAXOVERRIDE] TO [AX_POS_Designer]
GRANT SELECT ON [dbo].[RETAILSTORETENDERTYPETABLE] TO [AX_POS_Designer]
GRANT SELECT ON [dbo].[RETAILSALESTAXOVERRIDEGROUPMEMBER] TO [AX_POS_Designer]
GRANT SELECT ON [dbo].[INVENTITEMBARCODE] TO [AX_POS_Designer]
GRANT SELECT ON [dbo].[INVENTTABLE] TO [AX_POS_Designer]
GRANT SELECT ON [dbo].[RETAILCHANNELTABLE] TO [AX_POS_Designer]
GRANT SELECT ON [dbo].[ECORESPRODUCTTRANSLATION] TO [AX_POS_Designer]
GO

Now you’ve got the role. Next step is to create the required logins, map them to a database and the newly created role.

Log in to AX and check that the designer starts up successfully.