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.
- 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):
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
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?
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!
I love the “View Update SQL” button on the SalesPad mobile server installer. This button generates the SQL that is about to be executed against your GP database into a notepad window for inspection. It was reassuring to see and understand what was about to be done to the database.
If you look above you will see the way that SalesPad embrace namespaces for the objects that form their systems. They are up front and in in front of your eyes.
So I started reading the SQL, sadly enjoying it – as you do, knowing that you tick all the boxes of the Facebook nerd test. Here is what the resulting script looked like…
Looking through I recognised much SQL analogous to that I’ve written before, myself for auto fulfilment and handling multiple bins. I did especially like the way they create a virtual table as a view for resolving SOPTYPE. It is a SQL technique I’ve seen elsewhere and I should adopt it more in my coding. I have also seen it used inside stored procedures too, in a similar way to create a virtual table to join against, as a common table expression.
Also note that they put all the db objects into the [spgpm] database schema, so I’m not the only one using schemas for what the creator intended! See my other post: Care naming database objects in Dynamics GP for custom GP Addins.
We have all been there, tired and forget that if the machine is running on 64bit, the WOW64 process is looking after you and the ODBC connection for GP needs setting up in the 32 bit version of the ODBC manager. This catches me out from time to time as we deploy ODBC through the network automatically, these days it is rare I do this by hand.
32 vs 64 bit
“You must directly invoke the 32-bit ODBC Administrator tool from the SysWoW64 folder. You can use the 32-bit ODBC Administrator tool to manage user DSNs and system DSNs that are used by WOW64 processes.” [ref 1]
so for example assuming default locations for things on the machine:
Also note: “Just a note don't use the search box in the start menu to look for odbcad32.exe (the same as if you type odbc only). Even the name is the same it opens the one in C:\Windows\System32 not c:\Windows\SysWOW64\” [ref 2]
ref 1 :https://support.microsoft.com/en-us/kb/942976
ref 2: http://superuser.com/questions/419832/how-can-i-open-the-32-bit-odbc-data-source-administrator-in-windows-7-64-bit
When both the following are true:
- the machine is woken from sleep or the network connection has been interrupted to SQL server
- SQL Server Managment Studio Activity Monitor was open on a tab somewhere in the editor deck in SSMS.
-then It will be no longer be possible to interact with SSMS. It will feel like the SQL Server Management Studio has crashed or has frozen.
SSMS crashing as a result of connection lost or sleep is caused by the modal dialog indicating the loss of connection for the activity monitor popping up in a modal mode behind the application surface somewhere. You cannot see this window by right clicking on the application in the bar, but it IS there. Modal mode prevents interaction with any other windows in the applicaiton until that modal window is closed.
To get up and running again, click ALT+Tab until you find the pesky dialog window, then click OK on that window to dismiss it. SSMS will spring back to life. This is a regular occurrance for me after computer sleeps and I’ve been monitoring the SQL server.