Crystal Reports XI Report using Stored Procedure

What code are you using to call the report?

When you run locally is it against the same database?

mlmcc

Hi mlmcc,

Yes it is against the same database:

Below is the code followed by the error on the server:

protected CrystalDecisions.Web.CrystalReportViewer CrystalReportViewer1;
            protected Admin_WorkStation.CachedFDTDCleared cachedFDTDCleared2;
            FDTDCleared crReportDocument = new FDTDCleared();
            TableLogOnInfo crTableLogOnInfo = new TableLogOnInfo();
            ConnectionInfo crConnectionInfo = new ConnectionInfo();
            ParameterFields  crParameterFields = new ParameterFields();
            ParameterField   crParameterField  = new ParameterField();
            ParameterValues crParameterValues  = new ParameterValues();
            ParameterDiscreteValue crParameterDiscreteValue = new ParameterDiscreteValue();
            ParameterFields  crParameterFields1 = new ParameterFields();
            ParameterField   crParameterField1  = new ParameterField();
            ParameterValues crParameterValues1  = new ParameterValues();
            ParameterDiscreteValue crParameterDiscreteValue1 = new ParameterDiscreteValue();
            ParameterFields  crParameterFields2 = new ParameterFields();
            ParameterField   crParameterField2  = new ParameterField();
            ParameterValues crParameterValues2  = new ParameterValues();
            ParameterDiscreteValue crParameterDiscreteValue2 = new ParameterDiscreteValue();
            CrystalDecisions.CrystalReports.Engine.Database crDatabase;
            CrystalDecisions.CrystalReports.Engine.Tables crTables;
            
            //CrystalDecisions.CrystalReports.Engine.Table crTable;
      
      
            private void Page_Load(object sender, System.EventArgs e)
            {
                  // Put user code to initialize the page here
                  crConnectionInfo.ServerName = "OFACDB";
                  crConnectionInfo.DatabaseName = "OFACDB";
                  crConnectionInfo.UserID = "ofac";
                  crConnectionInfo.Password = "ofac";
                  
                  crDatabase = crReportDocument.Database;

                  crTables = crDatabase.Tables;

                  foreach (CrystalDecisions.CrystalReports.Engine.Table crTable  in crTables)
                  {
                        crTableLogOnInfo = crTable.LogOnInfo;
                        crTableLogOnInfo.ConnectionInfo = crConnectionInfo;
                        crTable.ApplyLogOnInfo(crTableLogOnInfo);
                  }
                  crTableLogOnInfo.TableName = "sp_FD_TD_Cleared;1"; >>>>>stored procedure alias
                  // Create Parameter
                  //string prInvId = Session["sInvId"].ToString();
                  //crParameterField.ParameterFieldName = "@el_Client_Element_Investigator_Id";
                  //crParameterDiscreteValue.Value = prInvId.ToString();
                  //crParameterField.CurrentValues.Add(crParameterDiscreteValue);
                  //crParameterFields.Add(crParameterField);
                  

                  string prFDate = Session["sFDate"].ToString();
                  DateTime prDateTime = DateTime.Parse(prFDate);
                  crParameterField1.ParameterFieldName = "@Submitted_Date";
                  //crParameterDiscreteValue = new ParameterDiscreteValue();
                  crParameterDiscreteValue1.Value = prDateTime;
                  crParameterField1.CurrentValues.Add(crParameterDiscreteValue1);
                  crParameterFields.Add(crParameterField1);

                  string prTDate = Session["sTDate"].ToString();
                  DateTime prTDateTime = DateTime.Parse(prTDate);
                  crParameterField2.ParameterFieldName = "@Name_Status_Changed_Date";
                  //crParameterDiscreteValue = new ParameterDiscreteValue();
                  crParameterDiscreteValue2.Value = prTDateTime;
                  crParameterField2.CurrentValues.Add(crParameterDiscreteValue2);
                  crParameterFields.Add(crParameterField2);

                        
                  
                  
                  CrystalReportViewer1.ParameterFieldInfo = crParameterFields;
                  CrystalReportViewer1.ReportSource = crReportDocument;
                              
            }
error on server

The table 'sp_FD_TD_Cleared;1' could not be found. Error in File C:\WINDOWS\TEMP\{66EDFA3D-6877-4E65-B25F-A10E0820C7B2}.rpt: The table could not be found.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Runtime.InteropServices.COMException: The table 'sp_FD_TD_Cleared;1' could not be found. Error in File C:\WINDOWS\TEMP\{66EDFA3D-6877-4E65-B25F-A10E0820C7B2}.rpt: The table could not be found.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.  

Stack Trace:


[COMException (0x800002c5): The table 'sp_FD_TD_Cleared;1' could not be found.
Error in File C:\WINDOWS\TEMP\{66EDFA3D-6877-4E65-B25F-A10E0820C7B2}.rpt:
The table could not be found.]
   CrystalDecisions.ReportAppServer.Controllers.DatabaseControllerClass.SetTableLocation(ISCRTable CurTable, ISCRTable NewTable) +0
   CrystalDecisions.CrystalReports.Engine.Table.set_Location(String value) +543
   CrystalDecisions.CrystalReports.Engine.Table.
(TableLogOnInfo ) +309
   CrystalDecisions.CrystalReports.Engine.Table.ApplyLogOnInfo(TableLogOnInfo logonInfo) +628
   Admin_WorkStation.FDTDCleared1.Page_Load(Object sender, EventArgs e)
   System.Web.UI.Control.OnLoad(EventArgs e) +67
   System.Web.UI.Control.LoadRecursive() +35
   System.Web.UI.Page.ProcessRequestMain() +772

Again, all report run fine on the local machine.

All reports not using stored procedures run on both machines!!!!??????

Thanks
boomer1946

 

WIth a stored procedure you don't log onto the tables but simply log onto the stored procedure.  For some reason it is different.

Comment out the log oncode for the tables and try it.

mlmcc

Operationing System - Development  local - Windows 2000

Operating System - Deployed Server Windows Server 2003

Database local sql server 2000

Server sql server 2005

Thanks mlmcc,

Removing the logon code worked.

I also discovered an error in referencing the odbc driver.

I changed the servername to the odbc driver name as follows:

 crConnectionInfo.ServerName = "ofacServer";      was OFACDB
               crConnectionInfo.DatabaseName = "OFACDB";
               crConnectionInfo.UserID = "ofac";
               crConnectionInfo.Password = "ofac";

OFACDB on the server was defined as SQL native client

ofacServer is defined as SQL Server.

It works!!!!!

Glad you solved it.  Doubt i would have thought of that.

mlmcc