Checklinks taking too long (or its time to truncate history)

60GB database, works fine operationally except for the time checklinks and reconcile take to complete. They both take too long for the time available to run them. It is a pity that these can’t be ran over ranges or be more efficient/quick.

Sales history has 7.2 million records alone. SQL server is very capable when configured correctly at handling this size of  database with good performance. However the check links process is so slow and makes it necessary to remove history in order to get it complete in a timely fashion. In this day and age is seems a pity to be forced to ditch the data when we have SANs and RAID arrays with multi terabyte disks and the hardware can otherwise handle it.

There are products that help with this situation, professional advantage have the leading solution, Professional Advantage Company Data Archivedatasheetthis is neat in that it gives vision of the archived records from GP client. Unfortunately our bespoke reporting would need rewriting to span the companies. This is also true if we were to copy the live company and then truncate history, this is a technique that is also a common practice.


Purchasing history has 7 million records. Lots to plough through.


The advice you see on forums claiming it is possible for Checklinks to run as users are using the system is UNTRUE. If users are using the system the following report will print and no check links will occur. As this is not possible it leaves evenings and weekends and crossing fingers that it has completed by the time the users come back to work on Monday morning. It IS however possible to run reconciles while users are using the system.


eConnect Requester Documents sourced from Custom table or Views

The eConnect Requester not only allows you to fetch GP native records, it can be configured to fetch data from custom user tables. Even more interesting is I have just discovered it works for SQL views too! This is useful for joins to other tables to bring back just what I need rather than a big unwieldy eConnect XML document or multiple queries to get back a related set of data. It is even possible to query though to other databases or even other database servers through a view, if permissions are correct. Overall it is just a handy trick to have in the toolbox.

eConnect Requester sourced from a SQL View

By exposing data required fo my application as a SQL view in the dbo schema, note this does not not work on custom schemas, it is possible to fetch data from a table, or selection of tables or other views, for consumption by some service interfaced through eConnect using the eConnect Requester to query the database.

I’m in a situation where there is no direct access to the Dynamics GP database, the only access is through a web services proxy to eConnect, this was my way of making my own version of GP webservices, pre-GP web services existing.

To query a SQL view with eConnect, treat the view as a table when configuring the eConnect_Out_Setup record. Do not set up the record for triggers, as that is not relevant nor appropriate for views, neither are we seeking to drive changes into the output table. Setting a value in the triggers column of the setup generates triggers to push changes into the eConenct output table, so leave this as zero.

It is worth noting that when naming your views (or tables), the eConnect setup only accepts table names up to thirty characters, so the view can’t have a long name (#failed).


For details on setting up the requester, follow the MSDN/MS guidance, search for “extend eConnect requester” on your search engine of choice. In essence, go to edit top 200 rows by righ clicking the eConnect_Out_Setup table, edit the table adding in a new row for the SQL view you need to query.

In my case for my customer aux table this looks like this where other cols are zero or blank and I’ve named the document Aux (in production this would have a better name).

FROM eConnect_Out_Setup


To get the result send eConnect the following:

<eConnect xmlns:dt="urn:schemas-microsoft-com:datatypes">


BANG!: the following error occurred in my testing…

System.Data.SqlClient.SqlException was unhandled
Message=Invalid column name 'DEX_ROW_ID'.
Source=.Net SqlClient Data Provider
at Microsoft.Dynamics.GP.eConnect.ServiceProxy.HandleSqlException(FaultException`1 ex)
at Microsoft.Dynamics.GP.eConnect.ServiceProxy.RequestEntity(String connectionString, String xml)
at Microsoft.Dynamics.GP.eConnect.eConnectMethods.ProcessEntityRequest(String connectionString, String sXML)
at Microsoft.Dynamics.GP.eConnect.eConnectMethods.GetEntity(String connectionString, String sXML)
at XmlDocumentSender.Form1.Send_Button_Click(Object sender, EventArgs e) in C:\Program Files (x86)\Microsoft Dynamics\eConnect 12.0\eConnect Samples\XMLDocumentSender\Form1.vb:line 358
at System.Windows.Forms.Control.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.ButtonBase.WndProc(Message& m)
at System.Windows.Forms.Button.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.Run(Form mainForm)
at XmlDocumentSender.Form1.Main() in C:\Program Files (x86)\Microsoft Dynamics\eConnect 12.0\eConnect Samples\XMLDocumentSender\Form1.vb:line 0
at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)
at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()

“Error Invalid column name DEX_ROW_ID”
Looking closer and executing the SQL directly, in passing this is a really useful way to check requesters as the XML is returned in SQL Server Management Studio, clicking on the XML result opens it in a new windows to check over!
Below is example where it works and the result XML is shown as hyperlink in result set, followed by the example we are working on that fails.
However this is it failing…

SET @p40 = 0

EXEC eConnectOut @I_vDOCTYPE = 'aux'
,@I_vFORLOAD = 0
,@I_vFORLIST = 1
,@I_vACTION = 0
,@I_vREMOVE = 0
,@I_vWhereClause = DEFAULT
,@O_iErrorState = @p40 OUTPUT


Msg 207, Level 16, State 1, Line 2
Invalid column name 'DEX_ROW_ID'.

(1 row(s) affected)

I can only assume something expects a DEX_ROW_ID column to exist in the source table when the dynamic SQL is built by eConnect. This is a standard column for Dexterity tables but my custom table has no need for it, hence it does not come though to the view as its not there.

I introduced a dummy DEX_ROW_ID column and the error went away. As there are no data manipulation going on with my use case, I am simply querying the database remotely via eConnect, I keep this as single fixed value. Beware, that this may be used by updates as a unique identifier, proving this would require some more investigation. Perhaps it is required as it may be just used for row locking reasons to ensure econnect does not edit a document that a user is editing- an investigation for another day.

So here I fake the column (I have specific reasons for not listing the column names in the view definition).


Having faked this column, now the query of my custom user table, brings back whichever record I desire.

Document is being edited by a coworker and can’t be printed

The American language in Dynamics GP makes us chuckle. This message box is a wonderful variation on the message box “This transaction is being edited by another user”. We know of the “another user” table, but have never encountered the “coworker” table. The dialog box pops up when trying to print a purchase order that is locked, in this case by a dead GP session.



Find the purchase order in the table and then delete it:

SELECT * FROM  DYNAMICS..SY00801 where rsrcid='{put your PO Number here}'
DELETE FROM DYNAMICS..SY00801 where rsrcid='{put your PO Number here}'

Of course you should ensure this coworker is not actually editing the record before going to these measures!

Good idea

I found a great utility by Michael Johnson that replaces these messages with ones that display a more meaningful name, simple but so good!

Record lock trace addon for Dynamics GP – By Michael Johnson

soptypedatabase vs soptype in Dynamics GP SOPEntry Window

I was asked today why I’d just typed SOPTypeDatabase rather than SOPType when we were code reviewing a bit of code. In the SopEntry Window the there is a field SOPType, this shares its name with the database field soptype that defines what type an order is:

Sales Document


GUI Drop Down Field
Index position
Quote 1 1
Order 2 2
Invoice 3 4
Return 4 5
BackOrder 5 6
Fulfilment Order 6 3