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.

Business Portal missing spaces on Requisition Purchase screen.

When I updated a client from Business Portal for GP 2010 to GP 2013, a strange side-effect showed up. Users that transferred requisitions to PO's in the Purchasing Center > Requisitions page were seeing columns with no spacing between them. This made it difficult to view the information on the screen. What we found after much trouble-shooting, testing and no help from a charged Microsoft incident, was that the new GP 2013 portal used the old GP 2010 portal ActiveX control. We were able to prove this because every computer that had run the GP 2010 portal showed the issue, whereas any new workstations that had never run the GP 2010 portal showed with proper spacing. The problem is resolved by replacing the MBFWebBehaviors.dll file on the local workstation with the GP 2013 version. A Windows search can locate the local dll file, in the test case I saw it was in the Program Files\Microsoft Dynamics\Business Portal folder on the old workstation. In GP 2013 it was in the Windows\Downloaded Program Files folder. I hope that saves someone a bunch of time somewhere.

Run a stored procedure from a button in SL

If you are familiar with Dynamics SL Customization Mode you may know how to add a button to a screen.  But do you know how to run a stored procedure from that button?

In Customize Mode go to the Visual Basic Editor.  You need to add code to run the stored procedure.

Here is the code to do this.  It calls a stored procedure in SQL on the ODBC connection listed and passes three parameters from the User fields located on the screen.

Private Sub Button1_Click()
Set pConnection = CreateObject("ADODB.Connection")
Call pConnection.open("MyODBC", "SQL_User", "mypwd")
'Passes company ID
sSQL = "EXEC mystoredproc '" + GetObjectValue("xuser1") + "', '" + _
   GetObjectValue("xUser2") + "', '" + GetObjectValue("xUser3") + "'"
MsgBox sSQL
Call pConnection.Execute(sSQL)
Call pConnection.Close
MsgBox "Your stored procedure is done!"
End Sub

There's a lot more you can do here, like error trapping and the like, but this should get you started.

CRM reports - run counts

In my previous blog post I reported a means by which you could obtain SSRS report run times.  I have a variation on that theme that I used to determine what Microsoft CRM reports were being run at this client.


SELECT b.Name, COUNT(*)
FROM ReportServer.dbo.ExecutionLog3 a
INNER JOIN CRMDb..report b  -- this is the Report view in the CRM database
ON SUBSTRING(
a.ItemPath,  -- This is the report with the CRM database prefix
PATINDEX('%{%',a.ItemPath)+1,   -- parse the start of the report ID
LEN(a.ItemPath)-PATINDEX('%{%',a.ItemPath)-1) -- drop the end bracket from the report ID
= b.ReportID
GROUP BY b.Name


The above lists the CRM report name and the number of times it's been run.  You could query the TimeStart column from the ExecutionLog3 table for further information.

SQL Reporting Services report run times

I went looking for this and did not see a blog post so here it is.
If you need to see how long SSRS reports are taking to run use the following code on your SQL Report server:
SELECT ItemPath, UserName, TimeStart,
(timedataretrieval+timeprocessing+timerendering)/1000 RunTimeSeconds
FROM ReportServer.dbo.ExecutionLog3
Repeat as needed!  Better yet wire it to a view or SSRS report.

Testing disk speed

Just ran into this post that explains the use of a Microsoft tool called DiskSpd that can test disk IO.


I look forward to putting this into use and reporting back results.

Business Portal Requisition accounts not showing correct description

Client called with a problem in Business Portal - when the Requisition Creators are adding Items to their Requisitions, the account list shows the same account description for any account listed.  This client uses Account Level Security in GP to limit user account selections in Portal.


As MS Support pointed out this was due to the account description in the GL00100F1 table not correlating to the actual account description in GL00100.  No way of telling how this happened but an easy fix if you need it.


Execute the following script against the GP company database:


update a
set a.ACTDESCR = b.ACTDESCR
from GL00100F1 a
join GL00100 b
on a.ACTINDX = b.ACTINDX


If you have sub-levels of account security, our client does not, you may need to execute the same against GL00100F2.

Primary Key columns in CRM

I developed the following query today to examine the primary key columns in tables in CRM.  I hope this helps you in some way.

select distinct a.name as KeyColumn, b.name as TableName

from metadataschema.attribute a

inner join metadataschema.entity b

on a.entityid = b.entityid

where a.ispkattribute = 1

order by b.name

Unexpected Error updating GP 2015 R2

I received the following error updating a client to the latest build of GP 2015 R2 (KB3080334):

“An unexpected error was encountered trying to register the trigger: AFTER – Credit_Check of form SOP_Entry.  Error 2.  This trigger will be disabled.

Indications were this was the result of an add-in product in GP.  In the process of elimination I found that my version (2015b10) of Ethotech Commission Plan was the culprit.  I downloaded the latest update (2015b102) to Commission Plan and the error went away.

ANSI NULL or ANSI WARNINGS error from Dex app

If your Dex app throws the ANSI_NULL or ANSI_WARNING error then you need to include SET ANSI_WARNINGS ON in your stored procedure that your Dex app calls.


It should be the first line in that stored procedure.


The message your users would see from GP is (like):


[Microsoft][SQL Server Native Client 10.0][SQL Server]Heterogenous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for connection.  This ensures consistent query semantics.  Enable these options and then reissue your query.

Option Sets in MS CRM

If you use Microsoft CRM and want to explore the data from the database you will eventually run into data from Option Sets.  These are pre-defined values you can store for a field.  When you create an Option Set CRM stores the values in a special table called StringMapBase (you can also reference the view StringMap for these values.)


Because ALL Option Set values are stored in this table you need to determine which ones relate to your data.  This is not always easy but the following query can be helpful.

select a.AttributeName AS OptionSet, b.Name AS ObjectName, a.ObjectTypeCode

from StringMapBase a

inner join MetadataSchema.Entity b

on a.ObjectTypeCode = b.ObjectTypeCode

What this query does is join the Option Sets to the Entity table and shows the name of the CRM Entity that the Option Set relates to.


Save that query to a view and you can easily find option values for whatever reports you need them for.  When you find the right option set create a view in your reporting database to encapsulate those values for reference purposes.

User prompted for login in Sharepoint

SharePoint is a pre-requisite for Business Portal, a component of our Dynamics GP application for several clients.  A tough problem I've run into just now and before is SharePoint prompting the user to login.


Normally a user is granted access to SharePoint through SharePoint group membership.  That is typically all that is required.  When that user's network account is added to a SharePoint group - such as Visitors or Members, then SharePoint should recognize them and not prompt for a login if Windows Authentication is configured.


I was stumped because even though Windows Authentication is configured the Web Application configuration was set for Kerberos.  I needed to go into SharePoint Central Administration and change the Web App to NTLM to solve that little problem.

SSIS File Name from ForEach to Column

I use the Foreach loop container to process files in SSIS.  In doing so there are cases where I want to capture the name of the file that the loop is reading.


There are many examples of how to use the Foreach container but I could not readily find how to process the file name into a column of the table.  Here's how.


Assuming you've followed the proper steps to capture the file name in the Variable Mappings of your Foreach container you are ready for the next steps.


1. Assuming you have a Data Flow that moves the contents of your file into a table go to the Data Flow tab and drag Derived Column out of your Toolbox. 
2. Open the Derived Column Transformation Editor and add a Derived Column Name to the grid.  I called mine Source File.
3. In the Expression column drag the Type Cast value (DT_WSTR,<length>) from the function reference or type it in.
4. Locate your variable from the list in the box above and drag it in after the Type Cast.


I found that if you don't Type Cast the variable then I got blanks in my column in the destination table.  I also needed to use the SUBSTRING() function to strip the path from the front of the file name.  My final Expression value looks like this:


(DT_WSTR,20)SUBSTRING(@[User::MyFileRead],46,20)


My file name column is 20 characters and the actual file name starts in column 46.


You map the source file through the Derived Column object and map that to the destination.

Portal Upgrade > GP 2010 to GP 2013

I'm upgrading a client's GP from 2010 R2 to 2013 R2.  They need to stick with Business Portal for now until they complete the process of finding a new requisition system, now that Portal is gone in GP 2015 and beyond.  This is a Portal upgrade from 5.0 to 6.0.  SharePoint (SP) upgrade from 2007 to 2010.


I'm still in the process of installing but here's a quick list.

1. Install GP 2013 and upgrade the databases and register.  We do this to a new SQL Server.
2. Install Web Services and upgrade the Web Service database on the SQL Server.
3. Add the users to Web Services (WS) roles.
1. We use a separate Portal install account so that needs to be a WS Poweruser and BDC Administrator (add to Roles).
2. Verify that WS is reachable from the SP server.
4. Drop any firewalls between the SP server and the GP SQL server.
5. Install SharePoint 2010 Foundation (again new server) and configure the site.
1. Add a top-level web application and site collection (we will install Portal there.)
2. Add a Business Data Catalog (BDC) Service Application (you might have to turn the service on.)
3. Backup your SP site from Central Admin and SQL Server.
6. Install Business Portal.

Because we are moving Portal to a new SP installation we have to recreate the security access in SharePoint.  While tedious it's nothing compared to trying to move the entire SP site.  I'm not that brave.


I can see most of the requisition information in Portal.  But I have a "Server error in '/' Application" on the Hierarchy page.  Working with support to clear that up.

Performing Dexterity Help

I will continue my overview of providing online help in Dexterity. The title of this is a knock-off on the Business Portal installer stage where it tells us it is "Performing Help". Anyone know what that means in an installer?

Anyway in my previous post I spelled out the tools you will need for this.  I suggest using a sub-folder below your Dex development folder to contain the help files you will need.  Here is a list of the basic (text) files required to create and maintain a help system.
 
Note that if you install the Dexterity sample files the one entitled Develop contains an HTML help example.
 
Alias.h - This file contains the Dex internal name for the object along with (=) the HTML file name related to it.
 

 
HTMLFiles.txt - This file contains a list of all the HTML files referenced in the help system.
 

 
<map>.h - This file contains the mapping of the Dex internal name to the internal Dex HelpContextID number.  The format of the records in this file is #define <internal name> <help ID #>.  This file can be named however you'd like - I use a name similar to my enhancement.



The above files are referenced in a series of #include statements in the HTML Help Workshop.



It can be helpful to create help files from your Dex project by using File > Generate Help Files.  This creates files for Windows Help, which is not the same as HTML help.  This will create a MAP file that contains all the objects you created in your Dex project.  You will have to sift through this to find the components you want to reference in your help file but it's all there.

The Dex internal name I mention above takes the following form:

FRM_<form name>_WIN_<window name>

So if you have a form in your Dex project named MAIN and a window in this form named OPTIONS then the Dex internal name would be FRM_MAIN_WIN_OPTIONS.

There is, of course, help available in the HTML Help Workshop.  And you will need to review Chapter 39 of the Dexterity Integration Guide manual for details about how your Dex project needs to be configured to invoke the help file you create.