Dynamic Code Blocks

Microsoft Dynamics GP & .NET technical Blog by Tim Wappat

Why I find Microsoft One Note handy for blogging UI stuff

There are many instances where there are UI elements on screen that you wish to include in a blog post as text content as well as an image, usually for SEO reasons.

It can be laborious and error prone to retype from the image into the blog post, so I use One Note to do the OCR for me.

Here is an example of a menu in an application I want to talk about in a blog post. So I want to convert to text so use snipping tool to grab the text as an image:

Example menu with many items on it

Paste into One Note and Right click, select “copy as text”

Menu pasted into OneNote with context menu showing "copy text from picture"

Now paste the resulting text into the editor for the blog post.

NotePad application shown with text pasted into it

Easy eh?

This also is handy for quickly pulling the text from error dialogs where there is no cut & paste, quick snip of the message, copy text from OneNote and table names, object names etc are in your paste buffer ready to hit SQL server query window, in following example, the more info button didn’t hold the info I needed and to avoid typos it was easier and quicker to just use OneNote snipping tool into OneNote and copy as text back out of image.

image

Here is the Microsoft article that alerted me to this technique…

Extract text from pictures and file printouts by using OCR in OneNote

Using Visual Studio Database project Dynamics GP & source control

Source control of all aspects of a Dynamics GP solution is essential to deliver a reliable, consistent software product or system to the end users. Getting life cycle control of custom objects in the SQL database can be a challenge, even more so when looking at a typical Dynamics GP SQL server database, that contains many thousands of objects. I have seen many SQL tools struggle when pointed at a typical GP company database. It seems that from my use of SQL Server Data Tools that it has issues too. In this post I go on an adventure exploring the tools in relation to Dynamics GP, the learning can be applied to other databases than GP too.

Visual Studio offers SQL Server Data Tools, managed though the extensions menu of Visual Studio, it adds the concept of a SQL Server Database Project under the new projects menu. This experiment failed in that it took too long for schema comparisons on the current version (14.0.60629.0), but lets hope the comparison tool is working more quickly in future releases.

Setting up SSDT for Dynamics GP

SQL Tools3

Adding this project and importing the GP company database (or any other database) brings all the “create scripts” for the objects of that database into the project. It should be noted that it is worth checking the most recent SSDT has been installed and updated or messages such as that below can occur.

Import

Compatibility level error

The existing database Dynamics GP database can be imported into the project by right clicking the project title in the solution explorer and going into the import window as shown above.

Note that behaviour of the project is governed by some settings in the database project settings page, Project Menu>> Database Project Settings. Where for example the schema name can be embedded into the file names generated. For those SQL nerds out there, as the Database Projects can create the database when used in green field development, database core settings are found behind the Database Settings button also illustrated on screen shot below, this is not really relevant to the majority of users for this discussion.

SQL Data Project Settings Window

SQL Database Settings

SQL CLR Builds can be controlled by the project too, see the SQLCLR Build tab of the settings for more detail, again most users reading this will not be going there.

After import

The organisation of the generated object tree can be changed by using the folder structure drop down list at import, where options as to how the objects are stacked hierarchically in the object tree can be decided. There is also a maximum of one thousand files allowed in any of the generated folders holding the SQL objects on disk. This is a limit imposed by the data tools team and I am unaware of any way to increase this number. I assume this is to keep the application performance acceptable in use. Shown below are the “Procs1..” folders generated, each containing 1000 of the GP stored procedures. There are similar folders generated for Tables and other objects under each node.

SQL Tools Stored Procs

Needless to say, due to the large number of objects in the GP database, there will be many of these folders under each of the nodes. I find this really annoying as I’m used to the view in SQL server management studio that has all the objects listed directly under each node, this makes finding objects much more difficult, but we still have the usual “find tools” in visual studio to help.

SQL Tools Tables

After import the tool status bar will show the following message and the number of operations will keep climbing to a worrying high level, but eventually it works its way through them all and you are ready to go. The number went up to 1.6 million and then dropped back down to nothing, and it took a while (approx 20 mins) to do so on my infrastructure. I have seen this process adversely load a production database so would recommend sticking to development databases where possible.

SQL Tools progress went to 1.6 million

The large number of objects in the GP database makes SQL data tools choke or perform slowly on some operations, just be patient, some processes can seen very sluggish and delayed when running against GP databases.  Operations such as right clicking the project and creating a snapshot cause Visual Studio to hang for prolonged periods of time, with no UI feedback about what is happening, however mostly the operation will eventually complete, in this example after about two minutes.

Once loaded into the project, the create scripts for all the objects in the database become available as .SQL files in a file structure on disk as represented by the solution.  The project can then checked in the TFS source control, to achieve document control over the objects.

Schema Compare

The schema compare feature is crucial for GP usage as it allow the GP databases to be compared to the project to see what changes there have been since the last compare or import. This is important as not all objects in a enterprise GP database are in the developers control. The tooling allows for the changes to scripted out into SQL change scripts, thus the change scripts can be added to the code project as upgrade scripts for deployment purposes or pushed using continuous integration into a test rig. Continuous integration and GP? – I can dream!

Performing the compare, right click the project title in solution explorer and select schema compare, then set the source and destination, these can be databases or projects.

SQL Data Tools Schema Compare

It is vital to click the gear icon and examine the compare options. The compare options are very powerful and can mean the difference between picking up on a change or not doing so, difference between your application being deployed with a missing object or not!

In this window there are options to ignore changes that you do not think will affect the operation of the application, such as “Ignore semicolon between statements” but others can radically change what objects are checked.

Here is the full list of options available in the compare options window:

Allow drop blocking assemblies
Allow incompatible platform
Backup database before changes
Block on possible data loss
Comment out SetVar declarations
Compare using target collation
Deploy database in single user mode
Disable and reenable DDL triggers
Do not alter Change Data Capture objects
Do not ALTER replicated objects
Drop constraints not in source
Drop DML triggers not in source
Drop extended properties not in source
Drop indexes not in source
Drop objects not in source
Drop permissions not in source
Drop role members not defined in source
Drop statistics not in source
Generate smart defaults, when applicable
Ignore ANSI Nulls
Ignore authorizer
Ignore column collation
Ignore comments
Ignore cryptographic provider file path
Ignore DDL trigger order
Ignore DDL trigger state
Ignore default schema
Ignore DML trigger order
Ignore DML trigger state
gnore file and log file path
Ignore file size
Ignore filegroup placement
Ignore fill factor
Ignore full text catalog file path
Ignore identity seed
Ignore increment
Ignore index options
Ignore index padding
Ignore keyword casing
Ignore lock hints on indexes
Ignore login SIDS
Ignore not for replication
Ignore object placement on partition schemes
Ignore partition schemes
Ignore partition schemes
Ignore quoted identifiers
Ignore route lifetime
Ignore semicolon between statements
Ignore table options
Ignore user settings objects
Ignore whitespace
Include composite objects (database target only)
Include transactional scripts
No alter statements to change Clr types
Populate files on FileGroups
Script file size
Script refresh module
Script state checks
Script validation for new constraints
Treat verification errors as warnings
Unmodifiable object warnings
Verify collation compatibility
Verify deployment

Now click compare, go make a coffee, cocktail or whatever does it for you while the tool does it work. Some time later the results will be returned showing all the objects that differ between databases. In fact it only took a twenty mins to complete on my system with the settings I had selected the first time, with the project un-built.

To get a reliable comparison the project must successfully build. Later I describe how to get the project to build, it was after a successful build I found the next problem.

Running the SSDT Compare tool back against the origin database would cause a hang at "Initializing comparison, Loading...". So find something lengthy to do, like painting the house or going to visit friends as leaving it for 12 hours did help, after which it did return results as shown below.

Results from Dynamics GP Schema Comparison SSDT

It seems that the comparison can't cope with the GP database size. This is a massive issue as without the comparison back to the current database state (or other database states if used for test or dev), maintenance of objects that require check-in due to changes becomes manual in nature, invalidating the whole purpose of the tool in respect of GP projects.

In the screen shot you can see the Update and Generate script tool buttons. With GP we would not want to use the UPDATE button as this would attempt to run the selected scripts against the target database. Instead script out the update, this will create SQL script representing the selected object changes. Check the generated script over, it is auto generated and may contain some undesired SQL. Once verified this change script can be checked into source control itself and used as a record of upgrades to the database or distributed for ops teams to apply to the database that requires syncing.

GenerateScript

The tools could be used in one off reconciles of databases -if only the tooling worked for GP sized databases more quickly! Other tasks could include finding where Dynamics GP upgrades have not updated some database objects correctly, by comparing to a known fresh install the tooling may help identify the problem objects, or now orphaned objects in the database, left by legacy VAR applications, no longer in use.

I have used other SQL comparison tools that did struggle with GP but eventually worked, so this experience with the schema compare tool is not unexpected, a pity this takes so long to run.

Source Control

Although the database projects can be used without adding them to source control (makes sense for one off tasks). My objective is to add the custom GP objects to source control for all the same QA, productivity, backup, documentation, lifetime management reasons that software source code is already held there for.

There is not a lot to say about source control, it works the same as your other projects, add the solution to source control and the changes can be checked in and managed in source control exactly the same as with any other project. This makes it sound unexciting, but the power is the full traceability of the changes to database objects over time, with check in comments, work items associating them with tasks etc. It also gives visibility to the wider team members of objects being edited preventing or bringing awareness of two team members trampling over the same objects at the same time.

It is also useful when working with multiple customers' databases, to keep a track of custom customisations and be able to restore a test and development database with the operational database objects for your customers current setup. Obviously in this case you will not normally have the ability to import the customer database, but your development database can manage your own objects that exist at the customer sites.

While we are on the subject, remember to add your SSIS projects and SSRS projects to source control too!

Building the project

If the SQL was our own as part of a green field development project, we would build the project regularly, as building checks the object dependencies and validity of the SQL objects (database).

However we have inherited a very, very large SQL project by importing GP databases, it is not realistic to expect them to build. Although it is possible to control the database without it building, ideally it should be built. The problem is GP has some depreciated (and complicated) TSQL in its database that causes errors (rather than warnings) when a build is attempted. An example from the screen shot is the “ALL” clause for TSQL is depreciated for REVOKE and generates the error shown. Also note we have 2118 errors and 35,810 warnings overall, sorting this out is not possible or reasonable, as those are not objects we can change as they are part of the application.

Dynamics GP Data Tools Project Errors

Objects can, as with c# or vb.net, be excluded from the build. Here we see the smGrantAccessOnAccountMSTR.sql that is causing that previous error regarding the “ALL” clause. By setting the build action to None it will be removed  from the build and hence the error list, however this is going to be a long process for all objects!

SQL Data Settings5

It is possible to exclude ALL objects from the build by editing the .sqlroj file, editing the XML to exclude all. To do this I used Notepad++ to do a search and replace. It is worth checking your file before actually doing the replace to ensure no other nodes get caught up, if they do I’m certain a regular expression search and replace could be used to more accurately target them.

First replace the “<Build Include” tags with <None tags, like so…

SQL Data Settings7

Then deal with the non-self closing tag like so…

SQL Data Settings6

Having done this and reloading the solution, it builds, not a surprise as nothing is actually being built anymore!

For those of your into continuous integration and can get the project to build (indeed even if you are not into CI), the post Continuous integration with SQL Server Data Tools and Team Foundation Server is a good read to see what is possible with publishing the project.

Notes

Although technically I can get the objects controlled by source control, having to wait twelve hours for the comparison to complete is not helpful, and makes it almost impractical to use.

To say I'm disappointed is an understatement. However the principles of this exercise are applicable to other databases, and perhaps a future version or service pack may may make the tooling usable.

 I will keep an eye on this product as I think it could be very useful when it works more quickly.

Dynamics GP Multiple Bins SOP10203 allocation corruption duplication

  1. When a SOP sales order is fulfilled, a record of the bin allocation to the sales order line is made in SOP10203.
  2. When the sales order is subsequently converted to a sales invoice, the bin allocation changed to the SOPNUMBE of the newly created invoice. The Bin allocation is then against the invoice because the invoice, when posted will ultimately take the stock from inventory.
  3. The sales order at goes to history tables.
  4. When the invoice is posted the bin allocation record in SOP10203 is marked as posted by setting the POSTED field =1, effectively marking the bin allocation as historical.
SELECT SOP10203.*
FROM sop30200
JOIN SOP30300 ON sop30200.SOPTYPE = SOP30300.SOPTYPE
AND SOP30200.SOPNUMBE = SOP30300.SOPNUMBE
JOIN SOP10203 ON SOP30300.SOPTYPE = SOP10203.SOPTYPE
AND SOP30300.SOPNUMBE = SOP10203.SOPNUMBE
AND SOP30300.LNITMSEQ = SOP10203.LNITMSEQ
AND SOP30300.CMPNTSEQ = SOP10203.CMPNTSEQ
WHERE POSTED = 0
AND SOP30200.SOPTYPE = 2

Running the above query looks for live allocations to sales order lines for sales orders that are now in history, something that should not occur. The issues is that I’m seeing tens of records returned for this query.

On checking, the records for these allocations to sales orders in history are duplicated.  They are also existing, marked as POSTED for the associated invoice that derived from the order that is now in also in history.

From this evidence, it seems sometimes when an order is invoiced and moves to history, the bin allocation is correctly being made against the resulting invoice, but the original bin allocation against the sales order is not being deleted.

This causes me problems as our custom pick list routine sees that there is not enough available stock in the bin to fulfil the next order that comes along for those items due to this phantom bin allocation.

Please comment if you too get this issue or have tracked its root cause down. I have introduced some monitoring to see if I can identify the circumstances under which this occurs and will update this post if I find a solution myself.

GP2016 new feature - Visual Studio SDK Project Template support for VS2015

I blogged about Visual Studio 2015 & Dynamics GP Addin Project Templates “install” in a previous post, but thankfully GP2016 Visual Studio SDK brings the installer of the Dynamic GP Add-In project templates to Visual Studio 2015 without any of that hard work. Something I was thankful of when I was setting up a new Hyper-V development environment today.

After navigating to the SDK on the GP installation media iso, "[drive]:\Tools\SDK\VS Tools\VSToolsSDK.exe" and then installing the SDK, Visual Studio will then have the GP project templates awaiting you in the new project window. Happy times!

Dynamics GP Visual Studio SDK New Project Templates