Connector for Microsoft Dynamics: a little story about debugging

I have been able to resolve most of the issues by using the DynamicsWebServicesExceptionsConsole but this problem was even hosing up the exception logs

“Cannot access the exception service. Verify that the configuration file exists and contains the correct path to the exception service”

So after you go looking in the Config file and realizing this was working two seconds ago…..  http://support.microsoft.com/kb/924548  Resolution 3 was the one I needed.

basically run these two queries if the first one comes back with results you need to run the second one

select * from DYNAMICS..WSExceptionLog where ExceptionMessage like ‘%invalid character%’

delete DYNAMICS..WSExceptionLog where ExceptionMessage like ‘%invalid character%’

At this point I have all my major customers failing to pull over with an error that the handy exception console can’t even help with

“‘’, hexadecimal value 0x02, is an invalid character.”

One of the ways I decided I would start debugging my final conundrum was just building a web service.   Now they give you a handy little sample in the sdk.

\SDK\SampleCode\Adapter\SampleAdapter

Copy the project to a development server and start by building the application.  Well you are missing two assemblies if this is not on the same machine as the one you installed the connector.  Go get them from that server and put them on the machine so you can reference them.

\Program Files\Microsoft Dynamics\Microsoft Dynamics Adapter

Microsoft.Dynamics.Integration.AAL.dll
Microsoft.Dynamics.Integration.Common.dll

Then you get started and build.. ok good.  run… can’t run a non executable project.  OK.

Add an executable project.  Go to file.. add… project… I just did a simple windows application with a button.  Then I added an onclick to my button and this code…

private void button1_Click(object sender, EventArgs e)
        {
            Customer customer;
            CompanyKey companykey;
            Context context;
            CustomerKey customerkey;
            DynamicsGP weDynamicsGP = new DynamicsGP();
            weDynamicsGP.UseDefaultCredentials = true;
            context = new Context();
            companykey = new CompanyKey();
            companykey.Id = (1);
            context.OrganizationKey = (OrganizationKey)companykey;
            customerkey = new CustomerKey();
            customerkey.Id = "somecustomernumber";
            customer = weDynamicsGP.GetCustomerByKey(customerkey, context);
            label1.Text = customer.Name;
        }

Obviously update the somecustomernumber to be a number in your system

This code is pulled directly from http://mbs.microsoft.com/downloads/public/gp90docs/WSProgrammersGuide.pdf

Add using to reference the non executable class.  hit run.. Ok good.. we are good… its working… wait… what? 404.

Oh right that url is probably not going to my web service url.  It’s not on the same server.  Also the url does not like to run under localhost either.  You would want to use the IP or machine name instead.

So I update my web reference and now hit with another 404..

ok! Well I am hitting the url when I add the web reference so it is a valid address, what now?  I did search for localhost in the entire solution and found several places where that needed to be updated as well.

hit run.. Insufficient authorizations… what?

Oh right, I am running this as my nt auth and that wouldn’t have permissions on the web service security console or in GP

Add my credentials as a super user and Microsoft Dynamics Integration for the company I was working with in the dynamics security console.  then hit run.. now I am getting another 404???  what?? seriously???…

So then I just start looking up web service calls and 404 on google.  Yep I do that too.  And everyone was referencing the actual “wsdl”.  That had not been mentioned in the programmer guide.   I removed my web reference and re added it with the wsdl address

http://yourservername:48620/Metadata/Legacy/Full/DynamicsGP.wsdl

Hit run and walla I was back to the condundrum that started this…

” The application encountered an unhandled system exception.  Contact your system administrator for details.”

So  essentially I built the web service in hopes of calling the customeroject and seeing what it was coughing up on, but I was back to watching profiler.  At least I did finally get a simple web service up and running.

Watching profiler I see that same little proc go across exec eConnectOut that I have tried to look at and it is on total lock down.

full proc from profiler:
declare @p40 int
set @p40=0
exec eConnectOut
@I_vDOCTYPE=’WSCustomer’,@I_vOUTPUTTYPE=2,@I_vINDEX1TO=’300956′,@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=’300956′,
@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=default,@I_vFORLIST=1,@I_vACTION=default,@I_vROWCOUNT=1000,@I_vREMOVE=default,
@I_vDATE1=default,@I_vWhereClause=default,@O_iErrorState=@p40
output
select @p40

I ran it… well executed it in sql.  Sure enough it brought back the straight xml before any of the little applications could get hung up on what ever this was… so I scroll through the data, excited.  I finally found it what I have been looking for the  data raw as it is going over.

Then I see the problem.   ‘  yep a single quote in an open text field in gp coming across through the adaptor is causing all of these problems.

So now for the fix I went with.. This is probably completely unsupported, but I need to get this done and after going through all of the Programmer guides, searching all over, it appears this is an issue with the Web Services.   The connector uses WebServices which in turn uses eConnect.   The Notes field in GP allows for special characters and eConnect does not.  Now if I had access to the XML prior to being created (and I am sure I do somewhere) I could just add the

<!--[CDATA[ ]]>

as it states in the eConnect Programmers Guide.  Based on the time constraint I looked in the eConnect_output_Setup table for the doctype that was being passed and it was there “wsCustomer”  I looked for the JOINTABE SY03900 and of course it was not there.  When I installed the GP Web Services which is what the Connector uses it created some Procs, views, and tables in the database.  One of those is ReqRMCustomer.  I modified it by changing  dbo.SY03900.TXTFIELD to  ” AS ‘TXTFIELD’  this will keep the join just in case it is referenced somewhere I don’t know about but will pass a space across for the Notes field.  I do understand that this may cause issues in the future if I want to use Web Services to pull over my Customer with the notes field and I would need to undo the change and come up with another way.

Now after displaying my hack fix, if anyone has an alternative solution please let me know.

About dorothyjarry

Super Dots
This entry was posted in Connector for Microsoft Dynamics, CRM, Dynamics, GP. Bookmark the permalink.

3 Responses to Connector for Microsoft Dynamics: a little story about debugging

  1. Hi, i think that i saw you visited my web site so i
    came to “return the favor”.I’m trying to find things to enhance my web site!I suppose its ok to use a few of your ideas!!

    Like

  2. Hi there, the whole thing is going well here and ofcourse
    every one is sharing information, that’s in fact excellent, keep up writing.

    Like

  3. I saw a lot of web site but I conceive this 1 holds something additional in it. “The real sadness of fifty is not that you simply change a lot but that you simply change so small.” by Max Lerner.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s