This article discusses the basics of SQL scripting, including tips and tricks for getting more from your SQL scripts.
Originally published in H1 2019 GPUG Magazine
There are lots of great Microsoft Dynamics GP solutions on the web; I love this
community! Once you find a solution that works for you, embed the URL link in your
saved SQL script so that you will not only have the original reference for the solution, but also when you hold CTRL and click the link on line 7 of the code below, it opens the page as a new tab inside SSMS (SQL Server Management Studio). Now you have everything you need to use this code in the future; how cool is that? I used the following script recently from Microsoft Dynamics GP Community Forum and include it here as an example:
How to Update Tables Safely
Most data manipulation scripts used to fix corrupt Microsoft Dynamics GP data
nclude INSERT, UPDATE, or DELETE commands, which are at the heart of SQL’s power. But how do we wield that power for good and not evil? In the above example, the select command on line 11 is benign; no data will be changed when the entire code is executed. The update on line 12 has the power to update every row in the table sy01402. The WHERE statement on line 13 limits which rows will be updated, in this case only those rows with syDefaultType of 96, and the userid JosephBlow will be updated but only if both rows 12 and 13 are highlighted and run together. This syntax allows you to write one query for both the select and update, ensuring that the results for the update will be what you are expecting from the select statement.
SQL Comments
Let’s talk about SQL scripting readability and annotation. I like to use lots of notes so I don’t have to read through code to remember what it does. The double dashes used throughout the script create a comment on one line causing the SQL code to the right of the dashes not to be executed if the dashes are included or highlighted when the code is executed. I like to create reminders or even a checklist of steps to take since many fixes in Microsoft Dynamics GP require you to run reconcile or checklinks routines after adjusting data in the tables. You can also use the characters /* and */, as I did on lines 19 through 24, to encapsulate an entire section of code, which is great for secondary scripts which might be used to ferret out the errors or to test a view or stored procedure created. Bottom line, none of the code in green will execute unless highlighted directly. If the above code is executed in its entirety by clicking the Execute or F5, only the USE DYNAMICS and the select statement on line 11 would run using the where statement on line 13. Also, I like to start a new script by writing the steps I want the code to take as a checklist and then fleshing out the code to do the actual work beneath the notes. Referred to as pseudocode, this leaves your SQL code very readable to you or anyone who may need to execute the script in the future.
Back to the script: The code on line 12 is what does the actual work here. For added safety, it can be a good practice to comment on the UPDATE on line 12 to avoid the possible headache of accidentally running an entire script. For even more safety, embed the UPDATE within a transaction so that you can roll back the results if you don’t like them.
If you highlight the code from the “Begin” to the end of line 13, then the records included in the select statement will be updated, and you should receive a row count which should match the count from the original select statement which was just one row in this example.
This can be confirmed by running the entire script, and you will receive results with the new value updated. If the row count is different, you would execute the rollback tran on line 17 to reverse the update. Once you have confirmed the accuracy of the results by executing the original select on line 12, be sure to run the commit statement on line 15 to finalize the update. If you do not rollback or commit the transaction it will wait indefinitely for your response and cause all other transactions to queue up behind it, and Users can’t make their own updates. If a User is posting after your update but before your commit, Microsoft Dynamics GP will basically stop responding as it waits for the open update transaction to finish.
This code provides additional control over your updates. If you are updating just one row but miss the where statement, the record count would reflect the multiple rows updated. You would see this as you run the select statement to confirm the update, and to fix this, you just select the rollback tran statement on line 17 to start over. This is a lot nicer than a database restore. The primary role of the transaction with commit code discussed here is to automatically abort changes to the database if errors are encountered. This error handling is crucial for production systems but is beyond the scope of this article. We use it here to give the User a second chance before data is committed with no recourse.
SSMS Text Editor Tricks
Here is another helpful tip when scripting in SSMS for Microsoft Dynamics GP updates: using the Shift/Alt keys to modify columns of data. The SSMS is not a bad text editor; in the following screenshot I paste the four SOP numbers sent to me in an email which I would like to use in a where statement to update User-defined data in the SOP10106 table:
ORD1000001
ORD1000005
ORD1000100
ORD1000105
If I copy that column of data into a query, I can highlight columns to add the single quotes and commas required for the where statement. If I left-click to the right of the value ORD1000001 as the beginning location, then hold the Shift and Alt keys as I leftclick at the end of the value ORD1000105 as the ending location, then a thin blue line will show the vertical “highlighted” area:
I then enter the single quote and comma, and presto: All rows are updated. I do the same for the leading single quote, and I have all the data ready for the statement (after deleting the extra comma on the last SOP number):
This may not seem like much, but if you need to update thousands of rows with a column of data, it’s a quick fix.
Make sure to try these tips in a test database. In the next article in the next issue, I will discuss audit considerations in the administration of your Microsoft Dynamics GP databases.