Skip to Content
0

XML Web Service Data Source Error in SP21 - Empty String

Oct 08, 2017 at 09:21 PM

146

avatar image

Hello,

We recently upgraded to SP21 (on Windows 10 / Windows Server 2012) and since then a number of our reports have stopped working. We are using crdb_xml.dll version 13.0.21.2533. Since upgrading, there seems to be an issue with empty strings not being accepted in fields on a report. The error we get:

Visual Studio Main Report Preview: Failed to retrieve data from the database. Details: XML field value is invalid - empty String

Runtime: Failed to retrieve data from the database.

If I create a test report and add a field where all values are present, the report works. If I add an additional field that may not always contain a value, I receive the above error. If I delete the field and re-run, it works.

Note that in my XSD for the data source every field has the attribute nillable=true. For example:

<xsd:element type="xsd:double" name="SIR_Tot_dose_Cispl" minOccurs="0" nillable="true"/>

None of the reports have changed, but I now consistently get this error after upgrading to SP21 and VS2017.

Any ideas? Has something changed in SP21 where I need to give other attribute information in the XSD response? Is there a registry setting I'm missing when I installed SP21?

Thanks,

Rick

xml
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

5 Answers

Best Answer
Don Williams
Nov 24, 2017 at 03:19 PM
0

Hi Guys,

R&D replied indicating due to W3C Standards this is why it changed, someone else pointed out that we were not following the standards and it broke their xml data sources.

From DEV:

"Yes, We change the logic to treat null values.

In the previous release, all the ‘’ values are treated as null data.

We changed the logic to follow W3C standard -- return empty string for <A></A>, and return null for </A xsi::nil=’TRUE’>

It doesn’t make sense that CR could handle both valid and invalid XML."

So what you need to do is add that tag to the XML file -

xsi:nil="TRUE"

Then your XML will work, we can't change it back now.

Use Google and search for this KBA to get more details:

2567826 - Failed to retrieve data from the database. Details: XML field value is invalid empty string using CR for VS WEB Service XML driver

Thanks again

Don

Share
10 |10000 characters needed characters left characters exceeded
Don Williams
Oct 16, 2017 at 03:36 PM
0

Hi Rick,

I suspect that since the legacy CRDB_XML.dll still relies on the old VC 2005 C++ runtime you will need to add the LegacyMode back into the app.config file in your project. That driver will not be updated, we've deprecated it a long time ago, it's "as is"... It took time for me to get them to release that driver in a separate package, I doubt I'll be able to convince them to update it.

I suggest you move to ADO.NET and use it's XML driver, there are differences but it will be fully supported in the VS 2017 and 3.5 Framework and above.

I'll ping DEV and see if they know about this.

Thanks again

Don

Show 8 Share
10 |10000 characters needed characters left characters exceeded

Thank you Don I'll give those options a try. Best regards, Rick

0

Hi Don, Sorry I need some more help from you.

1) I am encountering the error not only from an application with the runtime, but with the CR designer Report Preview. I'm not sure where I'd set LegacyMode in the designer.

2) I looked into ADO.NET and it seems an XML file is required. My data source is an XML Web Service. Both the XSD and XML data are accessed via endpoints, not files. This work(ed) perfectly fine with CRDB_XML.dll. Is there a way to use the ADO.NET XML approach to connect to endpoints?

Thanks again,

Rick

0

Ah, if it fails in CRD then it's likely environment then.

I'll ask Vitaly to have a look at this post.

What version of CR are you using?

See if this works for CRD:

Create a file called:

crw32.exe.config

In that file paste this in:

<?xml version ="1.0"?>

<configuration>

<startup useLegacyV2RuntimeActivationPolicy="true" >

<supportedRuntime version="v4.0" />

</startup>

</configuration>

What this does it it tells CRD to use legacy mode and start up using 4.0 framework.

Don

0

Hi Don,

For all CrystalDecisions.* components I'm using version 13.0.3500.0.

I created the file crw32.exe.config with the contents you provided. I included this config file in the project containing the reports and restarted VS 2017. The same error happens when I try to view the report using CRD Report preview. Should the config file be placed somewhere else?

Thanks again,

Rick

0

Hi Richard,

That config file is only to possibly make it work in CR Designer. If it does not work in CRD then it won't work in CR for VS.

Put the file in the same location as crw32.exe and test in CR Designer and see if it works.

When I get a chance I'll try it also.

Is it possible to attach the xml/xsd with sample data so I can test?

Don

0

Hi Don,

Here is an example XSD endpoint:

https://ws.etop-eu.org/DatimbiIntegration.svc/GetDataViewXS/4833/en/3/581/16775211561911413519918715246436018850109701131151162241918123710116010443167773134

Here is the corresponding XML feed:

https://ws.etop-eu.org/DatimbiIntegration.svc/GetDataView/4833/en/3/581/16775211561911413519918715246436018850109701131151162241918123710116010443167773134

Neither require a password.

The field "SIR_Tot_dose_Cispl" is the one that throws the error, in that it sometimes contains null values. So if you create a report with the other 2 fields it works; when you add the third field (SIR_Tot_dose_Cispl) it throws the error.

Thanks again,

Rick

0

Hi Richard,

Thanks you for the files.

I tried to create a new report off the xml/xsd using CR Designer and the legacy XML driver and it shows no fields.

I then used the ADO.NET and no xsd file and when doing that it defaults to 65k string fields but it does show data and the 3 fields.

I'll do some more testing.

Where or what created these XML/XSD files?

Don

0

Hi Don,

I am not using files to feed the report, I am using these web services directly.

From CR Designer in VS2017, create a new report. To add the data source, Set Data Source Location, expand Create New Connection, expand "XML and Web Services". On the first step of the popup wizard, select "Use HTTP(s) Data Source" then into the field "HTTP(S) XML URL paste" https://ws.etop-eu.org/DatimbiIntegration.svc/GetDataView/4833/en/3/581/16775211561911413519918715246436018850109701131151162241918123710116010443167773134

Check the box "Specify Schema File" and click Next.

On the Authentication step, leave blank and click Next.

On the "Schema file type" page select Use HTTP(S) Schema and in the HTTP(S) Schema URL paste https://ws.etop-eu.org/DatimbiIntegration.svc/GetDataViewXS/4833/en/3/581/16775211561911413519918715246436018850109701131151162241918123710116010443167773134

Click Next. On the "HTTP Authentication for schema.." step, leave all fields blank and click Next. Then click Finish.

When the next popup appears with the newly defined endpoint under the "XML and Web Services" folder, select the new endpoint you just added and click "Update".

Back to the CR designer, in the Field Explorer panel expand Database Fields > records/record and then you should see all the fields available for the report. If you add the field Internal_ID_0 and run the report, it will work. If you then add the field SIR_Tot_dose_Cispl it will throw the error because the field contains some empty values, even though the XSD permits this, and this was working until the latest upgrade to SP21.

Thanks again for all of your help,

Rick

0
Don Williams
Oct 24, 2017 at 06:20 PM
0

Hi Rich,

Thank you for the info. I can duplicate it now but need to test this in CR Designer and due to Proxy issues I need to find out how to get around it.

It does appear to be an migration issue so by end of day I may escalate to DEV.

With the Framework update it may be the cause.

On the Download page there is a XML file you can put into your app folder that reverts the SDK back to SP 20. See if that works for you?

Don

Show 1 Share
10 |10000 characters needed characters left characters exceeded

Hi Don,

FYI I tried using the SP 20 crdb_XML and it is not compatible with CR SP 21.

Is there any news from DEV about solving this problem?

Thanks,

Rick

0
Don Williams
Nov 20, 2017 at 04:07 PM
0

Hi Rick,

Correct, we do version checking so you can't mix them.

This issue appears to have been reported by one of our Support Customers and it will be fixed in CR Designer 2016 SP 5.

According to the SAP Note the Developer fixed it in SP 21 but I believe this is a typo and they meant to note SP 22.

So I'm going to install SP 22 build 2515 and see if it works, I've asked DEV to verify it was in SP 21 or a typo.

I'll let you know if it is fixed in SP 22, if not I'll get them to test and get it into SP 22 for sure.

Thanks again

Don

Show 2 Share
10 |10000 characters needed characters left characters exceeded

Hi Don,

Thanks so much for the continued support on this matter. Looking forward to see if this is resolved in SP22 - please let me know how it looks.

Best regards,

Rick

0

Hi Rick,

I found an this SAP Note - 2445274 - Empty String interpreted as NULL when reporting off an XML file using the XML and Web Services driver in Crystal Reports

Tested it in latest SP 22 and it still failed, I asked the Developer to test using your link as well and it failed for them also. It fails in SP 21 and CR 2013 SP 11.

It appears it was only fixed for specific versions....

They are looking into it and it should be fixed in SP 22.

New KBA for this issue -

2567826 - Failed to retrieve data from the database. Details: XML field value is invalid empty string using CR for VS WEB Service XML driver

Thanks again

Don

0
Don Williams
Nov 22, 2017 at 02:37 PM
0

Hi Guys,

R&D had a look at it and it could be due to the XML source.

If you change/add the xsi:nil="TRUE" it should start working.

Discussing with Dev more more info on why the change and if the change was due to XML Standards.

<?xml version="1.0"?>

<NewDataSet>

<data>

<numb1>1</numb1><str1 xsi:nil="TRUE" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"/><RecordTruncated>False</RecordTruncated>

</data>

<data>

<numb1>2</numb1><str1></str1><RecordTruncated>False</RecordTruncated>

</data>

<data>

<numb1>3</numb1><RecordTruncated>False</RecordTruncated>

</data>

</NewDataSet>

Show 1 Share
10 |10000 characters needed characters left characters exceeded

Hi Don,

Thanks again for all of your help. We made the changes to our web service and now all seems to be working with SP 21. Hopefully this will remain stable now!

Best regards,

Rick

0