32bit and 64bit Apps using Crystal 2010sp1 with a recordset

[Former Member]

Hi

I'm using Crystal 2010sp1 with VS2010. I have some code that I did not write, which obtains a recordset and passes that to the ReportDocument. It then goes on to .Export() the report.

When my app is compiling as 32bit the .DataSourceConnections on the ReportDocument contains 1 item with the following properties:

DataBaseName=""

DBConnHandler=0

IntegratedSecurity=False

LogonProperties = { "ADO.Net DataSet Object", "0,4AFECC" } { "ADO.Net DataSet Object", "0,4AFECC" }

{ "Internal Connection ID", "2e3b2f7f-0c31-472a-9773-f301a7ba9a3e" } { "Internal Connection ID", "2e3b2f7f-0c31-472a-9773-f301a7ba9a3e" }

Password = ""

ServerName = "NewDataSet"

Type = CRQA UserID = Nothing And it exports perfectly!! When compiled as "Any CPU" and running on a 64bit machine, the properties are: DataBaseName="" DBConnHandler=0 IntegratedSecurity=False LogonProperties = { "Field Definition File", "BL_Extras.ttx" } { "Field Definition File", "BL_Extras.ttx" } Password = "" ServerName = "D:\EntaV4\ENTAReports\rpt\BL_Extras.ttx" Type = CRQA

UserID = Nothing

And the export fails with the exception:

"Failed to load database information. Error in File BL_Extras {9D634275-70C4-42E8-B7EE-C2A69C0CDD2B}.rpt: Failed to load database information."

Why under 64bit is Crystal setting different connection details? And where is it getting the path for the ttx file in the ServerName property, my app and report are on my C drive in a totally different location. That folder on D had never existed on my machine.

I do happne to have the ttx file for this report, but putting it next to the rpt or in the above path makes no difference. I don't have the ttx for other reports though anyway and running as 32bit it works even if I delete the ttx though.

I have searched the error and found several pre-SP1 and non-recordset scenarios. I also read a little about create XML files for the reports but that is going to be too much work as there are hundreds of reports. It's not a lack of a 64bit ADO driver is it?

I thought i'd ask here for other solutions before I step back in time 10 years and make this app 32bit only.

I'm pretty sure this all worked on 2008 with CR2008.....

[former_member183750 · Accepted solution]

1) Do not compile as "Any CPU" - compile as 32 bit or 64 bit

2) The TTX file path is retrieved from the report file. The fact that the report is looking for a TTX file tells me it is a legacy report, created way back based on TTX files. TTX file is essentially a description of the data format that tee report should be expecting. The only time the report would ask for a TTX file is if the dataset does not match what the report is expecting - based on the TTX description.

3) Re. XML. This would be a troubleshooting step only to determine if the structure of the dataset matched the TTX. See [this|https://www.sdn.sap.com/irj/scn/weblogs?blog=/pub/wlg/13270] [original link is broken] [original link is broken] [original link is broken]; blog for more details.

Ludek

Follow us on Twitter http://twitter.com/SAPCRNetSup

Got Enhancement ideas? Try the [SAP Idea Place|https://ideas.sap.com/community/products_and_solutions/crystalreports]

[Former Member · Accepted solution]

Thanks for the quick reply...

1) On a 32bit machine if I compiled using Any CPU or x86 then crystal works fine. Compiled using x64 obviously won't run.

On a 64bit machine if I compiled using x86 then again it works but is optimized for 32bit running. If I compile on Any CPU OR x64 then it trys to run as x64 and crystal throws the error as above.

i.e. my only option in terms of compilation at the moment is to compile in 32bit. If I force it to 64bit or let the platform decide (with any cpu) then Crystal does not work.

If this is the only solution then, as we have a lot of shared base projects, I will be forced to change almost all of our software to x86.

2) Yes I think the reports were created in Crystal 7 or 8. I'm simply toggling my project's platform between any/x64/x86 - it only works when running as x86.

[former_member183750 · Accepted solution]

64 bit should work. As I mentioned, the error is due to the fact that the dataset that is being passed to the report is not consistent what the report is expecting based on the TTX it was built off of. I suspect that since it works when compiled as 32 bit and not when compiled as 64 bit, there is a field that is interpreted \ defined differently in 32 bit as opposed to 64 bit compile. I would suggest the following test:

1) Just before you pass the dataset to the report (64 bit compile) add the following line to your code:

myDataset.WriteXml(xmlPath, XmlWriteMode.WriteSchema)

2) Open the report in the CR designer (stand-alone or CRVS2010)

3) Point the report at the above XML (Set Datasource)

4) Are you able to preview this report or do you get field mapping dialog?

If you are getting a field mapping dialog, then the structure of the dataset does not agree with the TTX and you'll have to resolve that. For details of the above steps, see the blog I referenced earlier on.

- Ludek

[Former Member · Accepted solution]

I exported the data as XML from my datatable like you said. I did it both running as x64 and x86, the output was the same.

I thought i'd eliminate any bad code that might be in my main app by writing a little test app that loads that xml and drops it in a viewer control. I started a new project in VS, added a form with the Crystal Report viewer. I then wrote code to load the rpt file, and read the xml into a dataset, and then setdatasource on a report object to the first table of that dataset (which is what my main app is doing).

Dim crReportDocument As New ReportDocument
Dim filePath As String
Dim ds As DataSet

filePath = "c:\64crystal.xml"
ds = New DataSet()
ds.ReadXml(filePath)

crReportDocument.Load("C:\inetpub\wwwroot\SchedulerManager\rpt\BL_Extras.rpt")
crReportDocument.SetDataSource(ds.Tables(0))
CrystalReportViewer1.ReportSource = crReportDocument

Running it in x86 I am prompted for some parameters that the report required, and then the report renders in the viewer perfectly, no problems.

Running this app in x64 prompted me for the parameters, and then I get a msgbox saying:

Failed to load database information

Error in File BL_Extras {CD6C5C52-744C-433E-9297-87ECC3834262}.rpt:

Failed to load database information.

So then going back to what you asked, I went to the viewer control and click 'choose a crystal report' I get a 'Bad forumula result' error. If I select 'Create a new Crystal Report' and then 'From and existing report' and select the rpt file i'm working with. It then shows the rpt file in the VS designer with all the fields and sections and looks fine, though returning to the viewer gives the 'Bad Formula result' again. Seem to get this on several reports so am assuming its just because there is no data yet.

On this report now in my project I went to set datasource location and selected the XML it then shows NewDataSet with Table under it in the treeview. The update button is disabled if i select the table. If I select the dataset and click update it tells me:

"Some tables could not be replaced, as no match was found in the new data source. Please specify the table required for any unmodifeid tables". The top pane of this window is showing the file details of the ttx that it thinks is located on the D drive.

If I go to database expert and move the table over it then prompts me to find the ttx file. It doesn't let me skip this so I give in and tell it where it is. I then get the link tab. It shows the ttx with 23 fields and my xml table with 27 fields. It has 11 fields linked automatically,

A comparrison of the ttx and XML in notepad shows the following, the bold fields are the ones that did not auto link.

DateOption, ttx:Number, xml:unsignedByte

StartDate, ttx:dateTime, xml:dateTime

EndDate, ttx:dateTime, xml:dateTime

VenueGroups, ttx:Memo, xml:string

Profiles, ttx:Memo, xml:string

Users, ttx:Memo, xml:string

Extras, ttx:Memo, xml:string

RepGrouping: ttx:Number, xml:unsignedByte

IncludeDiscount, ttx:Number, xml:unsignedByte

VgrDescription, ttx:String(50), xml:string

CurSymbol, ttx:String(3), xml:string

CurName, ttx:String(30), xml:string

CurScaleFactor, ttx:Number, xml:short

ProDescription, String(50), xml:string

UseName, String(30), xml:string

TransactionDate, ttx:Datetime, xml:dateTime

StxCode, ttx:Number, xml:int

StxDescription, ttx:String(100), xml:string

MrwID, ttx:String(3), xml:string

MrwDescription, ttx:String(100), xml:string

Sales, ttx:Number, xml:int

Returns, ttx:Number, xml:int

Reserved, ttx:Number, xml:int

GrossSalesAmount, ttx:Currency, xml:decimal

GrossReservedAmount, ttx:Currency, xml:decimal

SalesVAT, ttx:Currency, xml:decimal

ReservedVAT, ttx:Currency, xml:decimal

Right clicking on all of these bold items on the table side (in the link tab) says that those bold fields are type Number. So not sure why the first 8 don't link as they are looking for Number. As for the last 4, in SQL these fields are type money, once read by vb.net into a datatable they are of type decimal. Not sure what crystal is expecting here.

I can manually link the 8, but it tells me for the 4 currency fields that the "data types are not compatibile"

I still don't get why if running as 32bit and passing the datatable from xml or sql to the rpt, even without the ttx, works without issue.

I think for now that i'm going to have to simply compile my main app for 32bit, but would still like to understand what is going on here. Thanks for helping to look into this.

Tony.

[former_member11696 · Accepted solution]

Hi Tony,

I had a big discussion and tracked a XML issue with our Developers and one thing that you may be running into is the version of XML. If you export a report to XML in CR designer and look at the header you'll see CR uses XML version1:

<?xml version="1.0" encoding="UTF-8" ?>

The issue I tracked was the XML file was using Microsoft's XML format which is not compliant with the World's XML Standards. The argument was we always supported MS but their argument was we have to follow the World Standards to be compliant.

I looked but I don't see where or how you created your XMl file?

As a quick test can you get data into a legacy report based on the ttx fiel and then from the designer use the export option and select XML, don't use the legacy option if it's available. Then use that xml file as your data source to see if it works in both 32 and 64 bit runtime?

Thank you

Don

[former_member208657 · Accepted solution]

This might be occuring because we have no TTX database driver (crdb_fielddef.dll) for the 64 bit runtime of Crystal Reports. My theory in this case is that the Crystal .NET 64 bit runtime is erroring out when it encounters your report that is based on a TTX file.

What I suggest you try is to run your application in 32 bit mode and add some code to export the report to Crystal Reports format. This will give you a new .rpt file with the updated database info as ADO.NET XML. This .rpt file will be your new report going forward for 32 bit and 64 bit apps - you'll then discard or archive your old reports from version 8. This should take some of the pain out of updating the reports without having to re-write them all by hand.

One way you could try to confirm this is to use Process Monitor and see what is going on near the time the application fails. You may see some attempt to find a crdb_fielddef.dll. You could also turn on the logging in the CR .NET SDK to see what might be happening, see kbase below.

1470978 - How to enable/disable crpe logging for the Crystal Reports .NET SDK

[Former Member · Accepted solution]

Hi Don

I exported my XML by adding the following line to my application's code between the point where the data is loaded from sql to a datatable and that datatable is set to the report's datasource. The app is VS2010 VB.Net .Net 4.

dataTable.WriteXml("C:\64crystal.xml", XmlWriteMode.WriteSchema)

The resulting xml file starts:

<?xml version="1.0" standalone="yes"?>
<NewDataSet>
  <xs:schema id="NewDataSet" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
    <xs:element name="NewDataSet" msdata:IsDataSet="true" msdata:MainDataTable="Table" msdata:UseCurrentLocale="true">

I did just realise I can add my existing report straight into my project rather than creating a new one an using 'From and existing report' (doh!!!) not getting the forumula error now, but still has the same linking issues.

So now added the report to my VS project, looking at it in the VS designer, I used the database expert to point it to the SQL SP, it took me to the link screen. The 8 fields that i said didn't link before still don't auto link, but the 4 currency ones are linked!! So I manually linked the 8 and clicked ok. it told me that "more than one database driver has been used in the report..." This is a bit beyond what I know now..

Pointed my form's viewer at it an run... Get warned that the report was saved in a previous version of crystal and asked if i want to upgrade it, i said no, 64 bit failed with the same error i keep getting. 32bit failed asking me to provide logon details for the ttx.... so I think i messed up somewhere? You may need to give me some more basic steps as i'm now confused

If I goto the preview on the report and export from there in xml (not legacy) then it looks like it is exportign the ttx details only the top is very different:

<?xml version="1.0" encoding="UTF-8" ?>
<CrystalReport xmlns="urn:crystal-reports:schemas:report-detail"  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="urn:crystal-reports:schemas:report-detail http://www.businessobjects.com/products/xml/CR2008Schema.xsd">
<Group Level="1">
<GroupHeader>

@ David: Hi

No ttx in 64bit does sound like it would be the cause. I'll see about getting the reports upgrade for the next release. And i'll try and turn that logging on this afternoon.

[former_member11696 · Accepted solution]

Hi Tony,

First, is there a reason why you did not save the report? What happens if you set links again, save the report and then run it through your app do you still get the ttx error?

Actually that logging Dave pointed you to won't help for DB issues.

Find this Kbase on how to enable crlogger which logs just the database info:

1553469 - How to enable Database logging in Crystal Reports for Visual Studio 2010

Next is to try this sample code to set to ADO ( XML )

Imports CrystalDecisions.Shared

Public Function ChangeConnectionInfo() as ReportDocument
Dim boReportDocument as New ReportDocument
'**EDIT** Change the path and report name to the report you want to change.
boReportDocument.Load("c:\reports\yourreport.rpt", OpenReportMethod.OpenReportByTempCopy)

'Create a new Database Table to replace the reports current table.
Dim boTable As New CrystalDecisions.ReportAppServer.DataDefModel.Table
  
'boMainPropertyBag: These hold the attributes of the tables ConnectionInfo object
Dim boMainPropertyBag As New PropertyBag
'boInnerPropertyBag: These hold the attributes for the QE_LogonProperties
'In the main property bag (boMainPropertyBag)
Dim boInnerPropertyBag As New PropertyBag
  
'Set the attributes for the boInnerPropertyBag
boInnerPropertyBag.Add("File Path ", "D:\CPP Net 2010\RASXIR2Printer2K800\2tableexp.xml")
boInnerPropertyBag.Add("Internal Connection ID", "{6a96758e-ca19-4c9b-92b7-1dc7d215f338}")
  
'Set the attributes for the boMainPropertyBag
boMainPropertyBag.Add("Database DLL", "crdb_adoplus.dll")
boMainPropertyBag.Add("QE_DatabaseName", "")
boMainPropertyBag.Add("QE_DatabaseType", "ADO.NET (XML)")
'Add the QE_LogonProperties we set in the boInnerPropertyBag Object
boMainPropertyBag.Add("QE_LogonProperties", boInnerPropertyBag)
boMainPropertyBag.Add("QE_ServerDescription", "CrystalReport")
boMainPropertyBag.Add("QE_SQLDB", "False")
boMainPropertyBag.Add("SSO Enabled", "False")

'Create a new ConnectionInfo object
Dim boConnectionInfo As New CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfo
'Pass the database properties to a connection info object
boConnectionInfo.Attributes = boMainPropertyBag
'Set the connection kind
boConnectionInfo.Kind = CrConnectionInfoKindEnum.crConnectionInfoKindCRQE
'**EDIT** Set the User Name and Password if required.
boConnectionInfo.UserName = "UserName"
boConnectionInfo.Password = "Password"
'Pass the connection information to the table
boTable.ConnectionInfo = boConnectionInfo

'Get the Database Tables Collection for your report
Dim boTables As CrystalDecisions.ReportAppServer.DataDefModel.Tables = _
boReportDocument.ReportClientDocument.DatabaseController.Database.Tables

'For each table in the report:
' - Set the Table Name properties.
' - Set the table location in the report to use the new modified table
boTable.Name = "Details"
boTable.QualifiedName = "Details"
boTable.Alias = "Details"

boReportDocument.ReportClientDocument.DatabaseController.SetTableLocation(boTables(0), boTable)

boTable.Name = "Field"
boTable.QualifiedName = "Field"
boTable.Alias = "Field"

boReportDocument.ReportClientDocument.DatabaseController.SetTableLocation(boTables(1), boTable)

boTable.Name = "ReportFooter"
boTable.QualifiedName = "ReportFooter"
boTable.Alias = "ReportFooter"

boReportDocument.ReportClientDocument.DatabaseController.SetTableLocation(boTables(2), boTable)

boTable.Name = "ReportHeader"
boTable.QualifiedName = "ReportHeader"
boTable.Alias = "ReportHeader"

boReportDocument.ReportClientDocument.DatabaseController.SetTableLocation(boTables(3), boTable)

boTable.Name = "Section"
boTable.QualifiedName = "Section"
boTable.Alias = "Section"

boReportDocument.ReportClientDocument.DatabaseController.SetTableLocation(boTables(4), boTable)

'Verify the database after adding substituting the new table.
'To ensure that the table updates properly when adding Command tables or Stored Procedures.
boReportDocument.VerifyDatabase()

Return boReportDocument
End Function

I'll send more code to try also to replace the connection.

Don

[former_member11696 · Accepted solution]

Replace the Property bag code below with the above info in my last post. This should remove any and all references to the ttx driver, sorry it's in C# but should work when you convert to VB:

private void ReplaceConnection_Click(object sender, EventArgs e)
{
    CrystalDecisions.CrystalReports.Engine.ReportDocument rpt = new CrystalDecisions.CrystalReports.Engine.ReportDocument();
    ISCDReportClientDocument rcd;

    rcd = rptClientDoc;

    //Create the logon propertybag for the connection we wish to use
    CrystalDecisions.ReportAppServer.DataDefModel.PropertyBag logonDetails = new CrystalDecisions.ReportAppServer.DataDefModel.PropertyBag();
    logonDetails.Add("Auto Translate", -1);
    logonDetails.Add("Connect Timeout", 15);
    logonDetails.Add("Data Source", "ServerName");
    logonDetails.Add("General Timeout", 0);
    logonDetails.Add("Initial Catalog", "Orders");
    logonDetails.Add("Integrated Security", "True");
    logonDetails.Add("Locale Identifier", 1033);
    logonDetails.Add("OLE DB Services", -5);
    logonDetails.Add("Provider", "SQLOLEDB");
    logonDetails.Add("Use Encryption for Data", 0);
    logonDetails.Add("Owner", "dbo"); // schema

    //Create the QE (query engine) propertybag with the provider details and logon property bag.
    CrystalDecisions.ReportAppServer.DataDefModel.PropertyBag QE_Details = new CrystalDecisions.ReportAppServer.DataDefModel.PropertyBag();
    QE_Details.Add("Database DLL", "crdb_ado.dll");
    QE_Details.Add("QE_DatabaseName", "Orders");
    QE_Details.Add("QE_DatabaseType", "OLE DB (ADO)");
    QE_Details.Add("QE_LogonProperties", logonDetails);
    QE_Details.Add("QE_ServerDescription", "ServerName");
    QE_Details.Add("QE_SQLDB", "True");
    QE_Details.Add("SSO Enabled", "False");
    QE_Details.Add("Owner", "dbo");

    CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfo newConnInfo = new CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfo();
    CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfo oldConnInfo;
    CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfos oldConnInfos;

    oldConnInfos = rcd.DatabaseController.GetConnectionInfos(null);
    for (int I = 0; I < oldConnInfos.Count; I++)
    {
        oldConnInfo = oldConnInfos<i>;
        newConnInfo.Attributes = QE_Details;
        newConnInfo.Kind = CrystalDecisions.ReportAppServer.DataDefModel.CrConnectionInfoKindEnum.crConnectionInfoKindCRQE;
        try
        {
            rcd.DatabaseController.ReplaceConnection(oldConnInfo, newConnInfo, null, CrystalDecisions.ReportAppServer.DataDefModel.CrDBOptionsEnum.crDBOptionDoNotVerifyDB);
        }
        catch (Exception ex)
        {
            MessageBox.Show("ERROR: " + ex.Message);
            return;
        }
    }
}

Don