Microsoft Dynamics GP

eConnect Requester Documents sourced from Custom table or Views

Tim Wappat

Sep 9, 2015 • 5 min read

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).

SELECT TOP (200) DOCTYPE
    ,TABLENAME
    ,ALIAS
    ,MAIN
    ,PARENTLEVEL
    ,ORDERBY
    ,REQUIRED1
    ,INDEX1
    ,INDEXCNT
    ,TRIGGERCNT
    ,DATACNT
    ,DATA1
FROM eConnect_Out_Setup
WHERE (DOCTYPE = 'Aux')

To get the result send eConnect the following:

<eConnect xmlns:dt="urn:schemas-microsoft-com:datatypes">
     <RQeConnectOutType>
            <eConnectProcessInfo>
               <Outgoing>TRUE</Outgoing>
               <MessageID>Customer</MessageID>
            </eConnectProcessInfo>
            <eConnectOut>
                <DOCTYPE>Aux</DOCTYPE>
                <OUTPUTTYPE>1</OUTPUTTYPE>
                <INDEX1TO>TEST</INDEX1TO>
                <INDEX1FROM>TEST</INDEX1FROM>
                <FORLOAD>0</FORLOAD>
                <FORLIST>1</FORLIST>
                <ACTION>0</ACTION>
                <ROWCOUNT>0</ROWCOUNT>
                <REMOVE>0</REMOVE>
            </eConnectOut>
     </RQeConnectOutType>
</eConnect>

BANG!: the following error occurred in my testing…


  System.Data.SqlClient.SqlException was unhandled
  Class=0
  ErrorCode=-2146232060
  LineNumber=2
  Message=Invalid column name 'DEX_ROW_ID'.
  Number=207
  Procedure=""
  Server=****
  Source=.Net SqlClient Data Provider
  State=1
  StackTrace:
       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()
  InnerException:

“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…

DECLARE @p40 INT

SET @p40 = 0

EXEC eConnectOut @I_vDOCTYPE = 'aux'
    ,@I_vOUTPUTTYPE = 1
    ,@I_vINDEX1TO = 'TEST'
    ,@I_vINDEX2TO = DEFAULT
    ,@I_vINDEX3TO = DEFAULT
    ,@I_vINDEX4TO = DEFAULT
    ,@I_vINDEX5TO = DEFAULT
    ,@I_vINDEX6TO = DEFAULT
    ,@I_vINDEX7TO = DEFAULT
    ,@I_vINDEX8TO = DEFAULT
    ,@I_vINDEX9TO = DEFAULT
    ,@I_vINDEX10TO = DEFAULT
    ,@I_vINDEX11TO = DEFAULT
    ,@I_vINDEX12TO = DEFAULT
    ,@I_vINDEX13TO = DEFAULT
    ,@I_vINDEX14TO = DEFAULT
    ,@I_vINDEX15TO = DEFAULT
    ,@I_vINDEX1FROM = 'TEST'
    ,@I_vINDEX2FROM = DEFAULT
    ,@I_vINDEX3FROM = DEFAULT
    ,@I_vINDEX4FROM = DEFAULT
    ,@I_vINDEX5FROM = DEFAULT
    ,@I_vINDEX6FROM = DEFAULT
    ,@I_vINDEX7FROM = DEFAULT
    ,@I_vINDEX8FROM = DEFAULT
    ,@I_vINDEX9FROM = DEFAULT
    ,@I_vINDEX10FROM = DEFAULT
    ,@I_vINDEX11FROM = DEFAULT
    ,@I_vINDEX12FROM = DEFAULT
    ,@I_vINDEX13FROM = DEFAULT
    ,@I_vINDEX14FROM = DEFAULT
    ,@I_vINDEX15FROM = DEFAULT
    ,@I_vFORLOAD = 0
    ,@I_vFORLIST = 1
    ,@I_vACTION = 0
    ,@I_vROWCOUNT = 0
    ,@I_vREMOVE = 0
    ,@I_vDATE1 = DEFAULT
    ,@I_vWhereClause = DEFAULT
    ,@O_iErrorState = @p40 OUTPUT

SELECT @p40

  

    Msg 207, Level 16, State 1, Line 2Invalid 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).

    SELECT *,1 as DEX_ROW_ID FROM

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