cancel
Showing results for 
Search instead for 
Did you mean: 

Setting report from ODBC to OLE DB

Former Member
0 Kudos

This is a follow-up to a question I posted on this thread - http://scn.sap.com/thread/3174326

We have a client that favours creating their Datasource Location connections within a Crystal Report with ODBC. We in turn prefer to use OLE DB and specifically SQL Server Native Client 10.0. To remedy this we are manually modifying the reports and updating the Datasource Location.

I am wondering if there is a way within the ReportDocument to programmatically change the report to use the SQL Server Native Client 10.0 OLE DB connection? If not within the ReportDocument is there another preferred method?

I have included my code below:

using CrystalDecisions.CrystalReports.Engine;

using CrystalDecisions.Shared;

using (ReportDocument reportDocument = new ReportDocument())
{
     reportDocument.Load(filePath);

     //Set up the connection
     TableLogOnInfo dbLogon = new TableLogOnInfo();
     dbLogon.ConnectionInfo.ServerName = Constants.ReportServerName;
     dbLogon.ConnectionInfo.DatabaseName = Constants.ReportDatabaseName;
     dbLogon.ConnectionInfo.UserID = Constants.ReportUserId;
     dbLogon.ConnectionInfo.Password = Constants.ReportPassword;

     //Apply logon Info to all database objects
     reportDocument.Database.Tables[0].ApplyLogOnInfo(dbLogon);

     //Assign Parameters
     reportDocument.SetParameterValue("Date", fiscalDateId);
     reportDocument.SetParameterValue("Display", pageDisplay);
     reportDocument.SetParameterValue("Language", language);

     //Create the report name
     exportfilename = FileUtils.MapPath(string.Format("{0}\\Report_{1:yyyyMMdd}_{1:hhmmss}.csv", Constants.ExportedReportsPath, DateTime.Now));

     //Generate the report and save it
     reportDocument.ExportToDisk(ExportFormatType.CharacterSeparatedValues, exportfilename);
}

Thanks,

Ken

Accepted Solutions (1)

Accepted Solutions (1)

former_member183750
Active Contributor
0 Kudos

I'd use InProc RAS SDK. Then see the blog When to use the 'Replace Connection' method using the Crystal Reports or InProc RAS SDK for .NET.

A KB worth looking at as it has an attached utility that will write out the connection code for you:

1553921 - Is there a utility that would help in writing database logon code?

- Ludek

Follow us on Twitter

Got Enhancement ideas? Try the SAP Idea Place

Share Your Knowledge in SCN Topic Spaces

  

Former Member
0 Kudos

Hi Ludek,

Thanks for your response.

I have seen examples of others setting the connection explicitly without setting the db table information. See the code below for what I mean.

                    DbConnectionAttributes dbAttributes = new DbConnectionAttributes();

                    dbAttributes.Collection.Set("Connect Timeout", "15");

                    dbAttributes.Collection.Set("General Timeout", "0");

                    dbAttributes.Collection.Set("OLE DB Services", "-5");

                    CrystalDecisions.Shared.ConnectionInfo crConnectionInfo = new CrystalDecisions.Shared.ConnectionInfo();

                    crConnectionInfo.LogonProperties.Clear();

                    crConnectionInfo.Attributes.Collection.Clear();

                    crConnectionInfo.DatabaseName = Constants.ReportDatabaseName;

                    crConnectionInfo.ServerName = Constants.ReportServerName;

                    crConnectionInfo.UserID = Constants.ReportUserId;

                    crConnectionInfo.Password = Constants.ReportPassword;

                    crConnectionInfo.LogonProperties = dbAttributes.Collection;

                    TableLogOnInfo dbLogonTest = new TableLogOnInfo();

                    dbLogonTest.ConnectionInfo = crConnectionInfo;

                    reportDocument.Load(FileUtils.MapReportsPath("ES_PayrollStatement.rpt"));

                    reportDocument.Database.Tables[0].ApplyLogOnInfo(dbLogonTest);

When I try this code I get a "Failed to load database information" error. Is this a simpler and more dynamic solution for explicitly setting a connection to OLE DB from ODBC? And if so do you know why this code is not working?

Thanks,

Ken

former_member183750
Active Contributor
0 Kudos

Hello Ken

Honestly, the best thing to do is to follow the links I provided in my post from 30-May-2012. Your way may work - if you're lucky. The issue is that you are changing the actual drivers from crdb_odbc.dll to crdb_adoplus.dll. Without doing this explicitly as described in

When to use the 'Replace Connection' method using the Crystal Reports or InProc RAS SDK for .NET.

you're taking a chance that your approach will work, or not. I am not sure why in some instances your approach will work, but I would not take the chance.

- Ludek

Answers (1)

Answers (1)

Former Member
0 Kudos

Hi Ludek,

I took your advice and I used the utility to create my connection string. After the C# code was generated I then added in my connection info including username, password, server and db names. I then changed the table info to reflect the tables I am using and set the table command text to the same as the query within my report. However, I keep getting a "Log on failed" error. This occurs when I try to run VerfiyDatabase(). If I skip over this method and run to setting the connection on my report using the new connection I get the error there instead.

So, I was wondering the following:

1. Do you know the cause of this error? All of my connection info is correct. I can run the report manually using the same connection info. Is this a permissions issue?

2. I noticed in the example code in the link to http://scn.sap.com/message/13295424 does not do anything with tables. I tried using this code within VS2010 and it seems like some of the classes are deprecated. Is the utility the better option than this code?

3. Does SAP offer a service to developers where I can work with an SAP developer (over the phone or by RDP) to solve this issue if I cannot resolve it myself? Is so can you send me a link as to the cost of this service?

Thanks again for all your help.

Ken

former_member183750
Active Contributor
0 Kudos

Hello Ken:

Trying the suggestions in When to use the 'Replace Connection' method using the Crystal Reports or InProc RAS SDK for .NET.

may be worth while.

To answer your questions;

1. Do you know the cause of this error? All of my connection info is correct. I can run the report manually using the same connection info. Is this a permissions issue?

- Could be a permissions issue -more likely with web than win apps

2. I noticed in the example code in the link to http://scn.sap.com/message/13295424 does not do anything with tables. I tried using this code within VS2010 and it seems like some of the classes are deprecated. Is the utility the better option than this code?

- No clases that I am aware of have been deprecated in the .NET SDK (the link links to this message)

3. Does SAP offer a service to developers where I can work with an SAP developer (over the phone or by RDP) to solve this issue if I cannot resolve it myself? Is so can you send me a link as to the cost of this service?

- There is a phone incident that you can create here. The costs, etc., are on that page.

- Ludek