Custom eConnect XML Node–Procedure or function expects parameter, which was not supplied

Custom XML nodes error with parameters

It is possible to run your own custom stored procedures from eConnect, by passing in parameters via a custom XML nodes. I have explained this technique in a previous post, there is more information on MSDN too:

When developing a new Custom XML node it may be that the following error is encountered

Procedure or function expects parameter,  which was not supplied

This may be due to misconfiguration of the stored procedure parameters or XML node names.
Remember:

  • Prefix the stored procedure parameter names with I_v (Input Variable), so a parameter named “cardnumber” would become @I_Vcardnumber in the stored procedure
  • Pass in the eConnect XML document node like this (no prefix):
    <cardnumber>4929000000006</cardnumber>

Dynamics GP Integration Service

The Dynamics GP Integration Service, a windows service that provides an econnect end point service, if this is how the documents are being pushed through to GP, if using this then read on.

eConnect for Microsoft Dynamics GP 2013 Integration Service

eConnect integration Service for Dynamics GP

Cause

I have seen this behaviour a few times now. The error stated above occurs, using SQL profiler to view the SQL conversation between eConnect and the stored procedure, it looks like the parameters are being passed with the value of “default” rather than the expected values from XML.

Putting a break point on the application and inspecting the source eConnect XML document, it seems the XML looks ok, with all the data in the relevant nodes of the document. 

-So the nodes are not being extracted by eConnect and passed as the SQL parameters – why?

Solution

If this is being experienced, I expect that the stored procedure has been edited and/or the fields in the document have been edited, perhaps adding an extra field, changing a parameter name? What I have found is that the Integration Service seems to cache the profile of the SQL stored procedure parameters. I can guess this would be a good idea for performance reasons, but it means if a change is made to custom XML nodes or procedures there of, then the service must be restarted.

Restarting the service resets the cache and suddenly with no further changes the custom nodes behave correctly again! This cache behaviour can cause some serious head scratching until it is revealed what is happening.

not your issue?

There is another solution from another cause for this error, the post on Steve Endow’s blog explains:
Mysterious eConnect Error: The stored procedure does not exist. Watch your schemas!

eConnect Customer Requester Document–Missing ShipToName field

A new field for addresses, “Ship to Address Name” was added to GP2013 (much to my delight!). When making an eConnect requester document request through eConnect, this field was missing in the XML returned by eConnect.
 
image
 
Using the stored procedure that generates the output, proves this field is missing from output of the database and hence it is not anything in any layers output that of the data layer of eConnect causing this issue.
 
DECLARE    @return_value int

EXEC @return_value = [dbo].[taRequesterCUSTOMER2]
@I_vDOCTYPE = N'CUSTOMER',
@I_vOUTPUTTYPE = 2,
@I_vINDEX1TO = N'AARONFIT0001',
@I_vINDEX1FROM = N'AARONFIT0001',
@I_vFORLIST = 1

SELECT 'Return Value' = @return_value
 
Below is the example returned XML for The World Online customer AARONFIT0001, there are no ShipToName fields in the XML returned (GP2013R2).
<eConnect ACTION="0" Requester_DOCTYPE="CUSTOMER" DBNAME="TWO" TABLENAME="RM00101" DATE1="1900-01-01T00:00:00" CUSTNMBR="AARONFIT0001">
<Customer>
<CUSTNMBR>AARONFIT0001</CUSTNMBR>
<ADDRESS1>One Microsoft Way</ADDRESS1>
<ADDRESS2></ADDRESS2>
<ADDRESS3></ADDRESS3>
<ADRSCODE>PRIMARY</ADRSCODE>
<CITY>Redmond</CITY>
<CNTCPRSN>Bob Fitz</CNTCPRSN>
<COUNTRY>USA</COUNTRY>
<CPRCSTNM></CPRCSTNM>
<CURNCYID>Z-US$</CURNCYID>
<CUSTCLAS>USA-ILMO-T1</CUSTCLAS>
<CUSTDISC>0</CUSTDISC>
<CUSTNAME>Aaron Fitz Electrical</CUSTNAME>
<PHONE1>42555501010000</PHONE1>
<PHONE2>00000000000000</PHONE2>
<PHONE3></PHONE3>
<FAX>31255501010000</FAX>
<PYMTRMID>Net 30</PYMTRMID>
<SALSTERR>TERRITORY 1</SALSTERR>
<SHIPMTHD>LOCAL DELIVERY</SHIPMTHD>
<SLPRSNID>PAUL W.</SLPRSNID>
<STATE>WA</STATE>
<TAXSCHID>USASTCITY-6*</TAXSCHID>
<TXRGNNUM></TXRGNNUM>
<UPSZONE></UPSZONE>
<ZIP>98052-6399</ZIP>
<STMTNAME>Aaron Fitz Electrical</STMTNAME>
<SHRTNAME>Aaron Fitz Elec</SHRTNAME>
<PRBTADCD>PRIMARY</PRBTADCD>
<PRSTADCD>WAREHOUSE</PRSTADCD>
<STADDRCD>PRIMARY</STADDRCD>
<CHEKBKID>UPTOWN TRUST</CHEKBKID>
<CRLMTTYP>2</CRLMTTYP>
<CRLMTAMT>35000.00000</CRLMTAMT>
<CRLMTPER>0</CRLMTPER>
<CRLMTPAM>0.00000</CRLMTPAM>
<RATETPID></RATETPID>
<PRCLEVEL></PRCLEVEL>
<MINPYTYP>0</MINPYTYP>
<MINPYDLR>0.00000</MINPYDLR>
<MINPYPCT>0</MINPYPCT>
<FNCHATYP>1</FNCHATYP>
<FNCHPCNT>150</FNCHPCNT>
<FINCHDLR>0.00000</FINCHDLR>
<MXWOFTYP>2</MXWOFTYP>
<MXWROFAM>25.00000</MXWROFAM>
<COMMENT1></COMMENT1>
<COMMENT2></COMMENT2>
<USERDEF1>Retail</USERDEF1>
<USERDEF2></USERDEF2>
<TAXEXMT1></TAXEXMT1>
<TAXEXMT2></TAXEXMT2>
<BALNCTYP>0</BALNCTYP>
<STMTCYCL>5</STMTCYCL>
<BANKNAME></BANKNAME>
<BNKBRNCH></BNKBRNCH>
<FRSTINDT>1900-01-01T00:00:00</FRSTINDT>
<INACTIVE>0</INACTIVE>
<HOLD>0</HOLD>
<CRCARDID></CRCARDID>
<CRCRDNUM></CRCRDNUM>
<CCRDXPDT>1900-01-01T00:00:00</CCRDXPDT>
<KPDSTHST>1</KPDSTHST>
<KPCALHST>1</KPCALHST>
<KPERHIST>1</KPERHIST>
<KPTRXHST>1</KPTRXHST>
<CREATDDT>1970-01-01T00:00:00</CREATDDT>
<MODIFDT>2004-01-30T00:00:00</MODIFDT>
<Revalue_Customer>1</Revalue_Customer>
<Post_Results_To>0</Post_Results_To>
<FINCHID></FINCHID>
<GOVCRPID></GOVCRPID>
<GOVINDID></GOVINDID>
<DISGRPER>0</DISGRPER>
<DUEGRPER>0</DUEGRPER>
<DOCFMTID></DOCFMTID>
<Send_Email_Statements>0</Send_Email_Statements>
<GPSFOINTEGRATIONID></GPSFOINTEGRATIONID>
<INTEGRATIONSOURCE>0</INTEGRATIONSOURCE>
<INTEGRATIONID></INTEGRATIONID>
<Address>
<CUSTNMBR>AARONFIT0001</CUSTNMBR>
<ADRSCODE>PRIMARY</ADRSCODE>
<SLPRSNID></SLPRSNID>
<UPSZONE></UPSZONE>
<SHIPMTHD>LOCAL DELIVERY</SHIPMTHD>
<TAXSCHID>USASTCITY-6*</TAXSCHID>
<CNTCPRSN>Bob Fitz</CNTCPRSN>
<ADDRESS1>One Microsoft Way</ADDRESS1>
<ADDRESS2></ADDRESS2>
<ADDRESS3></ADDRESS3>
<COUNTRY>USA</COUNTRY>
<CITY>Redmond</CITY>
<STATE>WA</STATE>
<ZIP>98052-6399</ZIP>
<PHONE1>42555501010000</PHONE1>
<PHONE2>00000000000000</PHONE2>
<PHONE3></PHONE3>
<FAX>31255501010000</FAX>
<GPSFOINTEGRATIONID></GPSFOINTEGRATIONID>
<INTEGRATIONSOURCE>0</INTEGRATIONSOURCE>
<INTEGRATIONID></INTEGRATIONID>
<Internet_Address />
</Address>
<Address>
<CUSTNMBR>AARONFIT0001</CUSTNMBR>
<ADRSCODE>WAREHOUSE</ADRSCODE>
<SLPRSNID></SLPRSNID>
<UPSZONE></UPSZONE>
<SHIPMTHD>LOCAL DELIVERY</SHIPMTHD>
<TAXSCHID>USASTCITY-6*</TAXSCHID>
<CNTCPRSN>Bob Fitz</CNTCPRSN>
<ADDRESS1>11403 45 St. South</ADDRESS1>
<ADDRESS2></ADDRESS2>
<ADDRESS3></ADDRESS3>
<COUNTRY>USA</COUNTRY>
<CITY>Chicago</CITY>
<STATE>IL</STATE>
<ZIP>60603-0776</ZIP>
<PHONE1>31255501020000</PHONE1>
<PHONE2>31255501020000</PHONE2>
<PHONE3></PHONE3>
<FAX>31255501020000</FAX>
<GPSFOINTEGRATIONID></GPSFOINTEGRATIONID>
<INTEGRATIONSOURCE>0</INTEGRATIONSOURCE>
<INTEGRATIONID></INTEGRATIONID>
<Internet_Address />
</Address>
</Customer>
</eConnect>

Thanks to Steve Endow for running against GP 2015 R2 (14.00.0817) to find same thing, no ship to name.

<eConnect ACTION="0" Requester_DOCTYPE="CUSTOMER" DBNAME="TWO" TABLENAME="RM00101" DATE1="1900-01-01T00:00:00" CUSTNMBR="AARONFIT0001">
<Customer>
<CUSTNMBR>AARONFIT0001</CUSTNMBR>
<ADDRESS1>One Microsoft Way</ADDRESS1>
<ADDRESS2></ADDRESS2>
<ADDRESS3></ADDRESS3>
<ADRSCODE>PRIMARY</ADRSCODE>
<CITY>Redmond</CITY>
<CNTCPRSN>Bob Fitz</CNTCPRSN>
<COUNTRY>USA</COUNTRY>
<CPRCSTNM></CPRCSTNM>
<CURNCYID>Z-US$</CURNCYID>
<CUSTCLAS>USA-ILMO-T1</CUSTCLAS>
<CUSTDISC>0</CUSTDISC>
<CUSTNAME>Aaron Fitz Electrical</CUSTNAME>
<PHONE1>42555501010000</PHONE1>
<PHONE2>00000000000000</PHONE2>
<PHONE3></PHONE3>
<FAX>31255501010000</FAX>
<PYMTRMID>Net 30</PYMTRMID>
<SALSTERR>TERRITORY 1</SALSTERR>
<SHIPMTHD>LOCAL DELIVERY</SHIPMTHD>
<SLPRSNID>PAUL W.</SLPRSNID>
<STATE>WA</STATE>
<TAXSCHID>USASTCITY-6*</TAXSCHID>
<TXRGNNUM></TXRGNNUM>
<UPSZONE></UPSZONE>
<ZIP>98052-6399</ZIP>
<STMTNAME>Aaron Fitz Electrical</STMTNAME>
<SHRTNAME>Aaron Fitz Elec</SHRTNAME>
<PRBTADCD>PRIMARY</PRBTADCD>
<PRSTADCD>WAREHOUSE</PRSTADCD>
<STADDRCD>PRIMARY</STADDRCD>
<CHEKBKID>UPTOWN TRUST</CHEKBKID>
<CRLMTTYP>2</CRLMTTYP>
<CRLMTAMT>35000.00000</CRLMTAMT>
<CRLMTPER>0</CRLMTPER>
<CRLMTPAM>0.00000</CRLMTPAM>
<RATETPID></RATETPID>
<PRCLEVEL></PRCLEVEL>
<MINPYTYP>0</MINPYTYP>
<MINPYDLR>0.00000</MINPYDLR>
<MINPYPCT>0</MINPYPCT>
<FNCHATYP>1</FNCHATYP>
<FNCHPCNT>150</FNCHPCNT>
<FINCHDLR>0.00000</FINCHDLR>
<MXWOFTYP>2</MXWOFTYP>
<MXWROFAM>25.00000</MXWROFAM>
<COMMENT1></COMMENT1>
<COMMENT2></COMMENT2>
<USERDEF1>Retail</USERDEF1>
<USERDEF2></USERDEF2>
<TAXEXMT1></TAXEXMT1>
<TAXEXMT2></TAXEXMT2>
<BALNCTYP>0</BALNCTYP>
<STMTCYCL>5</STMTCYCL>
<BANKNAME></BANKNAME>
<BNKBRNCH></BNKBRNCH>
<FRSTINDT>1900-01-01T00:00:00</FRSTINDT>
<INACTIVE>0</INACTIVE>
<HOLD>0</HOLD>
<CRCARDID></CRCARDID>
<CRCRDNUM></CRCRDNUM>
<CCRDXPDT>1900-01-01T00:00:00</CCRDXPDT>
<KPDSTHST>1</KPDSTHST>
<KPCALHST>1</KPCALHST>
<KPERHIST>1</KPERHIST>
<KPTRXHST>1</KPTRXHST>
<CREATDDT>1980-01-01T00:00:00</CREATDDT>
<MODIFDT>2014-01-30T00:00:00</MODIFDT>
<Revalue_Customer>1</Revalue_Customer>
<Post_Results_To>0</Post_Results_To>
<FINCHID></FINCHID>
<GOVCRPID></GOVCRPID>
<GOVINDID></GOVINDID>
<DISGRPER>0</DISGRPER>
<DUEGRPER>0</DUEGRPER>
<DOCFMTID></DOCFMTID>
<Send_Email_Statements>0</Send_Email_Statements>
<GPSFOINTEGRATIONID></GPSFOINTEGRATIONID>
<INTEGRATIONSOURCE>0</INTEGRATIONSOURCE>
<INTEGRATIONID></INTEGRATIONID>
<Address>
<CUSTNMBR>AARONFIT0001</CUSTNMBR>
<ADRSCODE>PRIMARY</ADRSCODE>
<SLPRSNID></SLPRSNID>
<UPSZONE></UPSZONE>
<SHIPMTHD>LOCAL DELIVERY</SHIPMTHD>
<TAXSCHID>USASTCITY-6*</TAXSCHID>
<CNTCPRSN>Bob Fitz</CNTCPRSN>
<ADDRESS1>One Microsoft Way</ADDRESS1>
<ADDRESS2></ADDRESS2>
<ADDRESS3></ADDRESS3>
<COUNTRY>USA</COUNTRY>
<CITY>Redmond</CITY>
<STATE>WA</STATE>
<ZIP>98052-6399</ZIP>
<PHONE1>42555501010000</PHONE1>
<PHONE2>00000000000000</PHONE2>
<PHONE3></PHONE3>
<FAX>31255501010000</FAX>
<GPSFOINTEGRATIONID></GPSFOINTEGRATIONID>
<INTEGRATIONSOURCE>0</INTEGRATIONSOURCE>
<INTEGRATIONID></INTEGRATIONID>
<Internet_Address />
</Address>
<Address>
<CUSTNMBR>AARONFIT0001</CUSTNMBR>
<ADRSCODE>WAREHOUSE</ADRSCODE>
<SLPRSNID></SLPRSNID>
<UPSZONE></UPSZONE>
<SHIPMTHD>LOCAL DELIVERY</SHIPMTHD>
<TAXSCHID>USASTCITY-6*</TAXSCHID>
<CNTCPRSN>Bob Fitz</CNTCPRSN>
<ADDRESS1>11403 45 St. South</ADDRESS1>
<ADDRESS2></ADDRESS2>
<ADDRESS3></ADDRESS3>
<COUNTRY>USA</COUNTRY>
<CITY>Chicago</CITY>
<STATE>IL</STATE>
<ZIP>60603-0776</ZIP>
<PHONE1>31255501020000</PHONE1>
<PHONE2>31255501020000</PHONE2>
<PHONE3></PHONE3>
<FAX>31255501020000</FAX>
<GPSFOINTEGRATIONID></GPSFOINTEGRATIONID>
<INTEGRATIONSOURCE>0</INTEGRATIONSOURCE>
<INTEGRATIONID></INTEGRATIONID>
<Internet_Address />
</Address>
</Customer>
</eConnect>

Plan B

Out the box, the requester does not include the “new” ship to name field in the address table. I switched to “Plan B”, I would create my own custom requester, I already access some none native tables for some other custom modifications where I access them from eConnect via custom requesters. This experience means that I know its a simple config in the eConnect setup.

Custom eConnect requester

To create a custom requester, edit the setup table, eConnect_Out_Setup in the company database. On opening the table, I realised something that has never registered in my head before -the default “out the box” requesters are defined in that table already! Great, that potentially means I can add this missing field to the existing requester through configuration.

To add a field to the requester output insert the column name, “ShipToName” into the next empty data column in the setup table for the DOCTYPE in question. Remember to increment the data count column, otherwise it will not generate the new column in the output XML. In my table the next empty cell was DATA20, so inserted “ShipToName” in there, here is the SQL.

-- Create new field in requester output
-- Increase the output field count by one
UPDATE [dbo].[eConnect_Out_Setup]
SET
DATACNT=DATACNT+1,
DATA20='ShipToName'
WHERE
DOCTYPE='CUSTOMER'
AND TABLENAME='RM00102'
AND DATA20=''
AND DATA19!=''

The document Updating the Transaction Requester for retrieve operations describes how to generate the stored procedures used by eConnect, following that guidance run the following SQL.

 

-- Drop The original output stored proc
drop PROCEDURE taRequesterCUSTOMER2
-- Rebuild the stored proc
exec eConnectOutCreate 'CUSTOMER', 2
 
Now running the test SQL procedure used earlier, I find the XML output now includes the ShipToName field, issue fixed!
 
So my learning today was that the default, out the box, encrypted stored procedures are not shipped with GP and are actually built from definitions in the eConnect setup table. These can be updated to your requirements. This is also perhaps the reason the new field is not included, customers upgrading would not want the existing eConnect definitions changing in the setup table, something the installer would have to do in order to make the new fields available. I guess all is forgiven!

Create your own

In production, it is better to define a new requester document type, than update the default ones so as to avoid conflicts with other, perhaps badly authored integrating applications. They might not expect that extra field in the XML output from the default requester document types and break.

To do this simply duplicate the “CUSTOMER” rows (DocType=CUSTOMER) in the setup table, and rename the Document type field to another name in the copy, this will be your new document type to use in place of CUSTOMER in the above dealings.
 
Please feel free to comment on this post, it motivates me to write more!