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.