Confessions article first published 9/24/2018 in GPUG magazine

Confessions of a GP SQL guy

Brent Olsen CPA, CMA, CITP

Senior ERP Developer, ZAGG Inc.

Part One, Visual Database Tools vs. SQL.

In my experience working with Dynamics GP, I have developed several ways to expedite development and address the inevitable data corruption by employing a rarely used feature inside SQL, the Visual Database Tools (VDT).  In this article I will share how I use this tool to manipulate data inside Microsoft’s SQL Server Management Studio (SSMS). I will also point some other VDT benefits along the journey, and in the next article, tips to make your SQL code less dangerous if you prefer that method.  I believe that the VDT is useful for both new and experienced SQL users in a variety of situations. This article assumes a basic working knowledge of SSMS.

Visual Database Tools

VDT is an artifact from an early attempt by IBM to develop a graphical query language to compete with SQL.  Like the Query By Example (QBE) interface MS Access offers, the Visual Database Tools add a graphical interface inside SSMS which is translated to, and executed as SQL code.  For you noobs out there, this means that you can create a graphical request and then see how Microsoft thinks is should be written in SQL, a great way to learn how to code. For you old timers, it is a quick and easy way to update/fix data inside GP without the tedium and dangers of SQL update and delete statements.  

Let me show you a quick example of this.  While posting a PM check batch, the A/P Specialist picks up her laptop and runs to a meeting- aborting the remaining posting process.  After running batch recovery she determines that while the checks printed properly she still shows invoice 115000 of voucher 00000000000000276 for vendor ADVANCED0001 as an open voucher still waiting to be paid.  She finds a solution online which suggests she run the following SQL statement along with Checklinks to clear this up:

UPDATE PM20000

Set CURTRXAM = 0.00000

Where VCHRNMBR = ‘00000000000000276’

This sets the current transaction amount back to 0 which tells the system that the voucher is fully paid and ready to be sent to history.  The Checklinks routine then dutifully moves the paid voucher to history. Life is good.

What happens if you highlight and execute only the first two lines?  Without the WHERE clause, SQL will update all rows setting each one to 0 and Checklinks will send all open transactions to history.  Life is not good (ask me how I know).

This is how you perform the same task using the VDT:

In SSMS Expand the Company database (TWO in this case), then expand Tables and scroll down to find the PM20000 table.  Right click on the PM20000 table and select “Edit All Rows”, this will open the table with all rows:

Your first impression might be that this is a spreadsheet you can manipulate, and in many ways it is but at a very simplistic level.  For example, you can double click the column to adjust to the widest value, but you can’t sort the column on the fly or apply column attributes like Excel.  In our example we are looking to update the CURTRXAM to 0.00000 for the voucher. Once you scroll down to find the voucher, enter the value in the row and column then press Enter or step off the row to commit (move up or down).  The benefit here is that it’s quicker and you reduce the risk of modifying more data than you intended to.

I also use this for testing when developing table triggers.  Changing the values in existing rows will test update triggers.  If you highlight the row on the left you can copy the entire row of data, scroll down to the bottom and insert it back in on the first empty row which has an asterisk (altering the key field(s) making it unique) to simulate a table row insert.  Stepping off the inserted row you will receive a SQL error immediately if there is a problem with the trigger; if you test the trigger by entering or updating a transaction inside GP the actual SQL error might be suppressed by the UI and GP can hang with no elegant way to recover.  On the other hand, pressing the Esc key in SSMS will reset the value to escape the errors.

Also like in Excel, the entire contents of the table can be highlighted by clicking on the upper left-hand column/row cell.  The contents can be copied and pasted into Excel or text editors like any other text data. Copy paste operations are memory intensive so I don’t do this with thousands of rows in a table but it is very handy for copying data into a temporary table that I use to integrate to GP with an integration tool or directly with an update SQL script.  Inventory Items seem to need a lot of regular updating. I keep a simple Item temporary table available in a Sandbox database outside of the GP company databases for the inevitable updates.

Tables can hold millions of rows of data, how do you find the one you are looking for?  This is where we get into the actual query aspect of VDT, there are 3 panes in the SSMS toolbar which are helpful during queries:

The Criteria Pane lets you enter criteria to build queries, sort and filter the results.  The SQL pane will display the underlying SQL script or query that will run based on the criteria, sorts, and filters entered.  While the SQL in this example shows a simple select statement you can still update data which I find to be one of the most compelling reasons to use VDT.  The Results pane, you guessed it, will display the results of your query.

A fourth Diagram pane allows you to create relationships between tables (joins) but is beyond the scope of this article.  If I need to join tables I prefer to write it in SQL.

If we enter the voucher number 00000000000000276 into the filter column and click Execute SQL you will see only the one document in the results below.  The CURTRXAM amount can be updated and will be committed when you step off the row.

Be careful with your VDT data changes, editing a table directly is open heart surgery on your data but then so is an update statement so code responsibly!  

Bullet proof SQL update and delete statements.

Not sold on VDT?  No problem, in our next article we will look at how you can create more secure update and delete statements in SQL scripts to produce the same results we reviewed in the Visual Database Tools.

The VDT is documented here:   https://docs.microsoft.com/en-us/sql/ssms/visual-db-tools/visual-database-tools?view=sql-server-2017