Auto upgrade Data Source on Crystal Reports to SQLNCLI11

[nancy_budd]

We have 500+ reports some containing multiple tables, sub-reports with SQL stored procedures, etc.

We have upgraded to SQL 2012.  We want to update all of these reports (tables, sub-report tables, stored procedures) to connect to the database using the Microsoft SQL Server 2012 Native Client (SQLNCLI11).

Many of these reports connect using SQLOLEDB, some connect using SQLNCLI and SQLNCLI10, but some are old enough where they use ODBC.

Is there a way to programmatically change the data source and update all the associated tables, sub-reports, etc. in these reports to change the datasource to SQLNCLI11 and update the tables to point to their correct table in the SQLNCLI11 database?

We would like to do this in VB.NET using CR for VS. 

Thank you

[DellSC · Accepted solution]

Yes, this is possible.

You would run a query like this to get all of the report templates:

Select * from CI_INFOOBJECTS where SI_KIND = 'CrystalReport' and SI_INSTANCE = 0

The query results will go into an InfoObjects object.  You would then walk through each InfoObject in the InfoObjects, cast it as a Report, and update the data sources.  I don't have VB code for this, but here is some C# code that does this:

private void updateLogon(Report newRpt, string alias)

{

  for (int j = 1; j <= newRpt.ReportLogons.Count; j++)

  {

    //set the database alias, user ID, and password

    newRpt.ReportLogons[j].UseOriginalDataSource = false;

    newRpt.ReportLogons[j].CustomServerName = alias;

    newRpt.ReportLogons[j].CustomUserName = _rptUser;

    newRpt.ReportLogons[j].CustomPassword = _rptPW;

    if (_setPrefix)  //needed for Oracle database but maybe not others

    {

      for (int l = 1; l <= newRpt.ReportLogons[j].TableLocationPrefixes.Count; l++)

      {

        newRpt.ReportLogons[j].TableLocationPrefixes[l].UseMappedTablePrefix = true;

        newRpt.ReportLogons[j].TableLocationPrefixes[l].MappedTablePrefix = _rptPrefix;

      }

    }

  }

}

Because you're working with a Report object and not a ReportDocument object, you'll get all of the tables - even those in the subreports - through the ReportLogons.

I would install the new driver, point your ODBC connections to it, and uninstall the old one before running the program.

-Dell

[nancy_budd]

No, we are not using a BI Platform.  This is a Transact SQL Database.

Most of our current reports connect to this SQL Server Database using Integrated Security with either the SQLOLEDB data source provider or the OLE DB (ADO) SQL Server Native Client 11.0 provider. Some VERY old reports use ODBC. 

We would be using Visual Studio (VB.NET) 2010 to update these report data source and table properties.

We want to be able to change all whatever type Data Source Provider that is currently in the report (regardless of ODBC, SQLOLEDB or SQLNCLI10) to the OLE DB (ADO) SQL Server Native Client 11.0 (The Microsoft SQL Server 2012 Native Client).

How do we do this?  What are all the properties necessary to set for the report data source and tables (both main report and subreport)?

Thank you

[former_member183750]

Hi N B

See the following:

Also, see KBA 1553921 - Is there a utility that would help in writing database logon code?

The KBA has a utility attached to it that will provide the code you will need to use when you are replacing the connections.

And, yes you will need to do the updates on main report as well as subreports. You can walk through the report, find the subreports and replace the connection for those. Basic code on how to walk through a report looking for any object (subreport in this case) would like this:

/I loop through all the sections looking for subreports and set the log on info, same as the main report:


//loop through all the sections to find all the report objects
foreach (CrystalDecisions.CrystalReports.Engine.Section crSection in crSections)
{
    crReportObjects = crSection.ReportObjects;
    //loop through all the report objects to find all the subreports
    foreach (CrystalDecisions.CrystalReports.Engine.ReportObject crReportObject in crReportObjects)
    {

  if (crReportObject.Kind == ReportObjectKind.SubreportObject)
        {
            //you will need to typecast the reportobject to a subreport object once you find it
            crSubreportObject = (CrystalDecisions.CrystalReports.Engine.SubreportObject)crReportObject;
            string mysubname = crSubreportObject.SubreportName.ToString();




            //open the subreport object
            crSubreportDocument = crSubreportObject.OpenSubreport(crSubreportObject.SubreportName);

            //set the database and tables objects to work with the subreport
            crDatabase = crSubreportDocument.Database;
            crTables = crDatabase.Tables;
            tableIndex = 0;
            bool SecureDB;

            //loop through all the tables in the subreport and set up the connection info and apply it to the tables
            foreach (CrystalDecisions.CrystalReports.Engine.Table crTable in crTables)
                    crConnectioninfo.UserID = "sa";
                    crConnectioninfo.Password = "PW";
                    crConnectioninfo.DatabaseName = "QTYLTD";
                }

                    crTableLogOnInfo = crTable.LogOnInfo;
                    crTableLogOnInfo.ConnectionInfo = crConnectioninfo;
                    crTable.ApplyLogOnInfo(crTableLogOnInfo);
            }
        }
    }

crSubreportDocument.RecordSelectionFormula = <selection formula>

}

rpt.VerifyDatabase();

More info.

Sample apps:

NET RAS SDK Samples - Business Intelligence (BusinessObjects) - SCN Wiki

Tutorial:

Programming the RAS Insight 2007 | SCN

And finally Developer Help Files:

Report Application Server .NET SDK Developer Guide

Report Application Server .NET API Guide

- Ludek

[Former Member]

Can you give me some guidelines on this. I want to dynamically replace my default connection to use SLCli11 or preferably System.data.SQLClient. I get the connection string (DatabaseName, ServerName, Logon name, Password and database name) from web.config.

Where do the parameters come from, what does -5 mean, Is "OLE DB Services" a random string or a defined string somewhere ?

eg logonDetails.Add("OLE DB Services", -5);  

Where does the code state to use SQLCli11.?

The link to KBase 155921 utility to write database logon code comes back as 403 forbidden.

I currently use Crystal Reports 2011 and install a Crystal Reports viewer in .NET using C#. We use Crystal Reports for Visual studio 13.0.15

If I use SQLCli10 on a SQL Server2008R2 database there is no problem. If I move to SQL server 2012/2014 I get logon failed. If I install SQLCli10 it works but it has to be manually installed and any SQL2012 new functions are not supported

Can you help.

[former_member183750]

Hi Andrew

The link to the KBA is this:

https://apps.support.sap.com/sap/support/knowledge/public/en/0001553921

Looks like the utility is not attached to it, but I added it to this Discussion. Download the file and unzip. You will get a txt file. Rename that to .zip, extract and that will give you the utility.

- Ludek

CodeBuilder-RasConnectionInfo (NET2010 CRVS2010).txt.zip

[former_member183750]

Hi N B

Dell is assuming that you are using BI Platform and the reports will be retrieved from there. A good assumption with 500+ reports. Nevertheless, if you do not use BI Platform, you can do this with just the Crystal Reports SDK for .NET, but before I can provide more info, it will be good to know what version of VS you are using and what version of CR you may have available to you.

- Ludek

Senior Support Engineer AGS Product Support, Global Support Center Canada

Follow me on Twitter