This article describes how you can use Visual Database Tools to manipulate data inside Microsoft’s SQL Server Management Studio (SSMS).
Originally published in H2 2018 GPUG Magazine
In my experience working with Microsoft 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 out some other VDT benefits, and in the next article, share tips to make your SQL code less dangerous if you prefer that method. I believe that 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 that Microsoft 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 it 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 Microsoft Dynamics 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 Check Links 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 Check Links 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 Check Links 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 VDT:
In SSMS, expand the company database (two in this case), then expand Tables and scroll down to find the PM20000 table. Rightclick 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 you can in Microsoft 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 Microsoft Dynamics GP, the actual SQL error might be suppressed by the UI, and Microsoft Dynamics 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 Microsoft
Dynamics 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 Microsoft Dynamics 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 three 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!
Bulletproof 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.