How to change the database credentials for a Crystal Reports using Business One connection?

[former_member231954]

What I am trying to do: I try to print SAP documents in the background (export them as PDF files). I use the default layouts for the documents.

The problem: If the report only has ADO.NET datasources, I can change the credentials like this.:

private void ApplyDatabaseLoginInfo(ReportDocument reportDocument)
{
    //
    // Define credentials
    //
    var info = new TableLogOnInfo
    {
        ConnectionInfo =
        {
            AllowCustomConnection = true,
            ServerName = p_serverAddress,
            DatabaseName = p_companyDb,
            UserID = ReportUser,
            Password = ReportUserPassword
        }
    };

    // Main Connection
    reportDocument.SetDatabaseLogon(ReportUser, ReportUserPassword, p_serverAddress, p_companyDb, false);

    // Other connections?
    foreach (IConnectionInfo connectionInfo in reportDocument.DataSourceConnections)
    {
        connectionInfo.SetConnection(p_serverAddress, p_companyDb, ReportUser, ReportUserPassword);
        connectionInfo.LogonProperties.Set("Data Source", p_serverAddress);
        connectionInfo.LogonProperties.Set("Initial Catalog", p_companyDb);
    }

    // Only do this to the main report (can't do it to sub reports)
    if (!reportDocument.IsSubreport)
    {
        foreach (ReportDocument subreport in reportDocument.Subreports)
        {
            ApplyDatabaseLoginInfo(subreport);
        }
    }

    // Apply to tables
    foreach (Table databaseTable in reportDocument.Database.Tables)
    {
        var tableLogOnInfo = databaseTable.LogOnInfo;
        tableLogOnInfo.ConnectionInfo = info.ConnectionInfo;
        databaseTable.ApplyLogOnInfo(tableLogOnInfo);
        if (!databaseTable.TestConnectivity())
        {
            throw new Exception("Failed to apply login info for Crystal Reports");
        }
    }

    // verify
    reportDocument.VerifyDatabase();
}

This works only for ADO.NET.

I can't do the same if the report's datasource is set to SAP Business One:

How I am trying to do it: I query the SAP database (RDOC table, template) and save the crystal reports layout file to disk (in a temp file). Like this. This works well, the saved file can be opened by the SAP Crystal Reports (desinger) and works as expected.

Next, I load the RPT file using the CrystalReports.Engine (13.0.3501). It loads the file and I can debug the current values, however I can't figure out how to set the database connection (username, password, company name, MSSQL version, licence server..etc).

Even If I change the credentials using the property bag method, all I get is a "Logon failed for the database" error. (Using the methods mentioned here and here - neither got real answers though)

I enabled CRLogging and the only error I get is "30384 ..\cserrinf.cpp 523 Error 31816 (..\..\src\data\queryenghelp.cpp, 582): Sikertelen bejelentkezés.: 1", meaning "Logon failed".

How to change the datasource for SAP Business One? Is it even possible?

I use:

• Sql Server 2016
• SAP Business One 9.3 PL05
• UI and DI API
• SAP Crystal Reports 2016
• nuget: CrystalReports.Engine 13.0.3501 and its dependencies.

Edit: After checking SAP Note 2966187 I tried the connection string method, but I still receive the same error.

First of all: Viewing a Crystal Reports from SAP Business One works and SQL Native Client is installed. I also tested the connection from the ODBC settings and it works.

Using code however, the same "Logon failed" error.

I made a test crystal report, with a single data source (a table and a command) without subreports and parameters (really simple, only two fields displayed) using the SAP Business One datasource and I load it with this code:

using (var crReport = new ReportDocument())
{
    crReport.Load(crystalReportsFilePath, OpenReportMethod.OpenReportByDefault);
    
    // ...
     var driver = "SQLNCLI11";
    // var driver = "SQL Server Native Client 11.0"; // tried both, none works
    var userId = "ReportUser";
    var password = "....";
    var server = "....";
    var database = "....";
    var connectionString = $"DRIVER={{{driver}}};UID={userId};PWD={password};SERVERNODE={server};DATABASE={database};";

    crReport.DataSourceConnections.Clear();
    var dataSourceConnection = crReport.DataSourceConnections[0];
    var logonProperties = dataSourceConnection.LogonProperties;
    logonProperties.Set("Provider", driver);
    logonProperties.Set("Connection String", connectionString);
    dataSourceConnection.SetConnection(server, database, false);
    dataSourceConnection.SetLogon(userId, password);

    foreach (ReportDocument subReport in crReport.Subreports)
    {
        foreach (IConnectionInfo subReportDataSourceConnection in subReport.DataSourceConnections)
        {
            subReportDataSourceConnection.SetConnection(server, database, false);
            subReportDataSourceConnection.SetLogon(userId, password);
        }   
    }
    
    // ...
    crReport.ExportToDisk(ExportFormatType.PortableDocFormat, "C:\\temp\\CR\\Test.pdf");
}

It does not work.

Also, the CRLogger logs this error too:

"9812 QETable.cpp 407 dbErr == DbErr_NotImplemented"

I tried to format the connection string multiple ways. Using " characters and without, ending with a ; and without but it does not matter, always the same error message.

Edit 2:

I changed the framework references from the nuget to the ones the SDK added to the system:

Now, CRLogger does not log anything and I still get the same error, nothing changed. I tried few obvious changes to the connection string, did not help.

[ANKIT_CHAUHAN]

Dear szab.kel,

Check whether SAP Note 2966187 will help for your cause or not?

Kind regards,

ANKIT CHAUHAN

SAP Business One Support

[former_member231954]

Dear ankit.chauhan1 ,

I read the SAP Note, but I am still getting the same error. I updated my answer with the details.

Regards,
Szabolcs Kelemen

[former_member11696]

Correct, Nuget is a third party source and not up to date.

Use the SDK package from the B1 installer.

[former_member231954]

I tried, but the result is the same. I updated my question. Could you please take a look if I used the references you meant? Thanks.

[PierreBrothier]

Hi,

not sure about it, but the crystal report engine from nuget probably doesn't handle the "business one" driver.

So the only way would be to change the reports to ODBC or ADO. not so easy work.