Confessions
of a GP SQL guy
Brent Olsen
CPA, CMA, CITP
Senior ERP
Developer, ZAGG Inc.
Part Two, SQL scripting basics.
Lots of great GP solutions on the web out there, 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 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:
Most data manipulation scripts used to fix corrupt GP data
include INSERT, UPDATE, or DELETE commands which are at the heart of SQL’s
power. But how do we wield that power
for good 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 assuring
that the results for the update will be what you are expecting from the select
statement.
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 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. 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. If a user is posting after
the update but before the commit, 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.
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.
Here is another helpful tip when scripting in SSMS for 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 comas required for the where
statement. If I left click to the right
of the value ORD1000001 as the beginning location, then hold the Shift & Alt
keys as I left click at the end of the value ORD1000105 as the
ending location then a thin blue line 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):
Doesn’t 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 I will discuss audit considerations in the administration of your GP
databases.
