SQL Transaction Processing

SQL Server is a transactional system, this is one of it's strengths.  Each statement that adds, updates or deletes data is logged as a transactional.  This is a feature that's worth reading up on, if you are not familiar.

To take advantage of this in your Transact-SQL code you need to use the Commit/Rollback statements.  By themselves they work ok but the real power is when you have a set of statements that need to be committed together - if any statement fails all statements fail.  Otherwise SQL will fail the statement that doesn't work and execute the others that do work.  This could make things very difficult depending on what you are doing, and that is where this feature comes into play.

There are a set of statements required to take advantage of this.  Again there are plenty of sources around the internet and from Microsoft that cover this.  I wanted to record these here for reference.


SET XACT_ABORT ON;
SET NOCOUNT ON;

SET LOCK_TIMEOUT 5000
BEGIN TRAN;
BEGIN TRY
-- sql statements in transaction go here
COMMIT TRAN;
END TRY
BEGIN CATCH

 ROLLBACK TRAN;
 -- optional EXEC spRaiseError;
END CATCH;
SET LOCK_TIMEOUT -1;

Server 2008 R2 Task Manager memory

I've been wondering about how to read this for a while.  Here is a really good post about these values.

Dynamics GP Word Template errors

I have a client with fairly extensive Word Template usage in SOP. I've done a number of modifications but they've been sporadic and my knowledge of this is not cemented. Well here's one for the memory banks!

I followed all the steps (I thought) to create and publish the template for the SOP Blank History Return Form. I updated and made pretty the internal (Standard) GP version of this report. Then I used TemplateGenerator to convert the XML output to DOCX. I then added and tried to use the DOCX as a Template in GP.

 FAIL!

What would happen is I'd select to print the form in GP - and nothing would happen! Actually something did but it wasn't good. At the bottom of the main Microsoft Dynamics GP window was a notation "Templates Processing: 1" along with a little yellow triangle with an exclamation point inside.

When I clicked on the little triangle it showed the "Report Processing Status" window with my SOP Blank History Return Form showing with a linked line underneath with another yellow triangle and exclamation mark and a cryptic .docx file name. When I click on that line I get a notation at the bottom of that window along with - you guessed it - another yellow triangle and exclamation mark.

Clicking on THAT yellow triangle yields a cute little window title "Exception Detail" and says:
"The following error occurred while processing this report:
One or more templates could not be processed.  View the exception log for more details."

I tried to locate the named file - not found.  I tried to set the logging settings that Dave Dusek wrote up in this blog.  No log I could find and no output files at all from GP 2015 R2.

Well after much teeth gnashing and methodical testing I finally found the simple cause:  In the Report Template Maintenance window the Report Name you've modified must be assigned to the company with the Assign button at the top.

Viola!  Problem solved.  My template now prints and errors are gone.

Not much on Google for this little issue so hope this saves someone else the time I wasted.