We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

'Failed to open a rowset' error when opening report containing subreports on different database

bobbisson
bobbisson asked
on
Medium Priority
875 Views
Last Modified: 2007-12-19
I use CR 9 for MS Visual Studio .NET. I have a report that has several nested subreports, and I use Oracle DB with default ODBC driver as a data source.

I wrote a thin client application using Crystal Reports Viewer component, so I create a report object, set database location for it, and then pass some parameters to the viewer and display the report. It looks like that:

using CrystalDecisions.CrystalReports.Engine;
using CrystalDecisions.Shared;

class MainForm : Form {

  private CrystalDecisions.Windows.Forms.CrystalReportViewer crvViewer;
  private CrystalDecisions.CrystalReports.Engine.ReportDocument rptOptions;

  void btnOK_Click() {
    rptOptions = new ReportDocument();

    ParameterFields paramFields = new ParameterFields();
    // setting the parameters - skipped
    paramFields.AddRange(new ParameterField[] { year,month,prodUnit,exchRate,exchRateFact });
    crvViewer.ParameterFieldInfo = paramFields;

    rptOptions.Load("full\path\to\rpt\file");

    ConnectionInfo connInfo = new ConnectionInfo();
    connInfo.ServerName = "ODBC source name";
    connInfo.DatabaseName = "";
    connInfo.UserID = "user";
    connInfo.Password = "******";

    // cycle through report tables -- what's important is that
    // subreport tables are NOT enlisted here
    foreach(Table tbl in rptOptions.Database.Tables) {
      TableLogOnInfo logOnInfo = (TableLogOnInfo)tbl.LogOnInfo.Clone();
      logOnInfo.ConnectionInfo = connInfo;
      tbl.ApplyLogOnInfo(logOnInfo);

      // this trick is needed to change database location
      // of ORACLE table from user1.table to user2.table
      if(tbl.Location.IndexOf('.') > 0) {
        string str = frmLogon.User.ToUpper() + tbl.Location.Substring(tbl.Location.IndexOf('.'));
        tbl.Location = str;
      }
    }
    rptOptions.Refresh();

    crvViewer.ReportSource = rptOptions;
  }
}

Should work, right? It does, of course, if I use a report which contains no subreports. Otherwise, I get this ugly error message 'Failed to open a rowset'. I managed to diagnose the problem, with the help of Tracing option of ODBC data source administrator. t shows SQL statement that was sent to Oracle server, and there it thies to SELECT from tables from schema for which it was originally created, say:

- if I login as user 'MK'
- if I created subreport logged into DB as 'MST'
- SQL statement shows 'SELECT ... FROM "MST"."TABLE"' and NOT '... FROM "MK"."TABLE"', generating 'Table or view does not exist' Oracle error.

So I'm wondering what the hell else must I do to make this damn think work? I wasted too much time on that, and I'm pretty much frustrated with this behaviour of CR, so please help me out!!
Comment
Watch Question

Senior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview

Author

Commented:
I didn't mention that I tried to log into separate subreports as well, I did it like this:

  foreach(ReportObject obj in rptOptions.ReportDefinition.ReportObjects) {
    if(!(obj is SubreportObject)) continue;
    SubreportObject subReport = (SubreportObject)obj;
    ReportDocument doc = subReport.OpenSubreport(subReport.SubreportName);

    // do the same thing with this subreport
    foreach(Table tbl in doc.Database.Tables) {
      // ...
    }
  }

but this doesn't work since it returns _exactly_ the same Tables collection (and same Database object) and I basically scroll through it as many times as many subreports I have...

I already downloaded the 1st service pack for CR for VS .NET 2002, heard there is a magical function 'SetDatabaseLogon', but it seems to me that the installation of this service pack has screwed up CR on my computer and I'll have to reinstall it (one more reason for frustration).

You said 'trusted connections', can I use this mechanism (and how) to connect to Oracle? Could you tell me more?
Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
I don't know if they can be used with Oracle or not.

When I create the database connection in CR one of the options on the screen where I provide the server name, database name and logon information is trusted connection.  Since I use MS SQL this may be available only with MS SQL.  We have ALL USERS set as a group in MS SQL with READ ONLY permission on the tables and execute permission on the views and sstored procedures.

mlmcc

Author

Commented:
the answer was exactly what mlmcc suggested - there was just a typo in my code so it didn't work as expected. this method works like this:


    private CrystalDecisions.CrystalReports.Engine.ReportDocument rptOptions = null;

    private void Load() {
      rptOptions = new ReportDocument();
      rptOptions.Load("path\\to\\report");

      rptOptions.SetDatabaseLogon("User","Pass","ODBCSource","");

      // construct array of subreports
      ArrayList rpts = new ArrayList();
      rpts.Add(rptOptions);
      foreach(ReportObject obj in rptOptions.ReportDefinition.ReportObjects) {
        if(!(obj is SubreportObject)) continue;
        SubreportObject subReport = (SubreportObject)obj;
        ReportDocument doc = rptOptions.OpenSubreport(subReport.SubreportName);
        doc.SetDatabaseLogon("User","Pass","ODBCSource","");
        rpts.Add(doc);
        Debug.WriteLine("    Subreport found: {0}",subReport.Name);
      }

      ConnectionInfo connInfo = new ConnectionInfo();
      connInfo.ServerName = "Name";
      connInfo.DatabaseName = "";
      connInfo.UserID = "User";
      connInfo.Password = "Pass";

      // npw cycle through all reports in list, including main report and its subreports
      foreach(ReportDocument doc in rpts) {
        Debug.WriteLine("Setting params for " + doc.Name);
        // the next line is where I had error - I went through rptOptions.Database.Tables rather than doc.Database.Tables each time
        foreach(Table tbl in doc.Database.Tables) {
          TableLogOnInfo logOnInfo = (TableLogOnInfo)tbl.LogOnInfo.Clone();
          logOnInfo.ConnectionInfo = connInfo;
          tbl.ApplyLogOnInfo(logOnInfo);
          string str = String.Empty;
          if(tbl.Location.IndexOf('.') > 0) str = tbl.Location.Substring(tbl.Location.IndexOf('.') + 1);
          else str = tbl.Location;
          // set table location - got to do it to make things work
          tbl.Location = str;
          Debug.WriteLine("    {0}@{1}: `{2}`::`{3}`",
            tbl.LogOnInfo.ConnectionInfo.UserID,
            tbl.LogOnInfo.ConnectionInfo.ServerName,
            tbl.LogOnInfo.ConnectionInfo.DatabaseName,
            tbl.Location);
        }
      }

      rptOptions.Refresh();
    }

then select this report in viewer (CrystalDecisions.Windows.Forms.CrystalReportViewer), for instance

      crvViewer.ReportSource = rptOptions;

enjoy.
Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
Glad I could help

mlmcc
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.