boomer1946
asked on
Crystal Reports XI Report using Stored Procedure
I have a few reports that I can run locally with no problem using stored procedure.
When I move the application to the server, I get the message:
Cannot find report sp_invID;1
The name is the name that the report wizard give to the stored procedure.
Am I missing something on the server?
Am I missing a merge module?
Any hints?
boomer1946
When I move the application to the server, I get the message:
Cannot find report sp_invID;1
The name is the name that the report wizard give to the stored procedure.
Am I missing something on the server?
Am I missing a merge module?
Any hints?
boomer1946
ASKER
Hi mlmcc,
Yes it is against the same database:
Below is the code followed by the error on the server:
protected CrystalDecisions.Web.Cryst alReportVi ewer CrystalReportViewer1;
protected Admin_WorkStation.CachedFD TDCleared 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.CrystalRe ports.Engi ne.Databas e crDatabase;
CrystalDecisions.CrystalRe ports.Engi ne.Tables crTables;
//CrystalDecisions.Crystal Reports.En gine.Table crTable;
private void Page_Load(object sender, System.EventArgs e)
{
// Put user code to initialize the page here
crConnectionInfo.ServerNam e = "OFACDB";
crConnectionInfo.DatabaseN ame = "OFACDB";
crConnectionInfo.UserID = "ofac";
crConnectionInfo.Password = "ofac";
crDatabase = crReportDocument.Database;
crTables = crDatabase.Tables;
foreach (CrystalDecisions.CrystalR eports.Eng ine.Table crTable in crTables)
{
crTableLogOnInfo = crTable.LogOnInfo;
crTableLogOnInfo.Connectio nInfo = crConnectionInfo;
crTable.ApplyLogOnInfo(crT ableLogOnI nfo);
}
crTableLogOnInfo.TableName = "sp_FD_TD_Cleared;1"; >>>>>stored procedure alias
// Create Parameter
//string prInvId = Session["sInvId"].ToString ();
//crParameterField.Paramet erFieldNam e = "@el_Client_Element_Invest igator_Id" ;
//crParameterDiscreteValue .Value = prInvId.ToString();
//crParameterField.Current Values.Add (crParamet erDiscrete Value);
//crParameterFields.Add(cr ParameterF ield);
string prFDate = Session["sFDate"].ToString ();
DateTime prDateTime = DateTime.Parse(prFDate);
crParameterField1.Paramete rFieldName = "@Submitted_Date";
//crParameterDiscreteValue = new ParameterDiscreteValue();
crParameterDiscreteValue1. Value = prDateTime;
crParameterField1.CurrentV alues.Add( crParamete rDiscreteV alue1);
crParameterFields.Add(crPa rameterFie ld1);
string prTDate = Session["sTDate"].ToString ();
DateTime prTDateTime = DateTime.Parse(prTDate);
crParameterField2.Paramete rFieldName = "@Name_Status_Changed_Date ";
//crParameterDiscreteValue = new ParameterDiscreteValue();
crParameterDiscreteValue2. Value = prTDateTime;
crParameterField2.CurrentV alues.Add( crParamete rDiscreteV alue2);
crParameterFields.Add(crPa rameterFie ld2);
CrystalReportViewer1.Param eterFieldI nfo = crParameterFields;
CrystalReportViewer1.Repor tSource = 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-A10E0 820C7B2}.r pt: 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.InteropServ ices.COMEx ception: The table 'sp_FD_TD_Cleared;1' could not be found. Error in File C:\WINDOWS\TEMP\{66EDFA3D- 6877-4E65- B25F-A10E0 820C7B2}.r pt: 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-A10E0 820C7B2}.r pt:
The table could not be found.]
CrystalDecisions.ReportApp Server.Con trollers.D atabaseCon trollerCla ss.SetTabl eLocation( ISCRTable CurTable, ISCRTable NewTable) +0
CrystalDecisions.CrystalRe ports.Engi ne.Table.s et_Locatio n(String value) +543
CrystalDecisions.CrystalRe ports.Engi ne.Table. (TableLogOnInfo ) +309
CrystalDecisions.CrystalRe ports.Engi ne.Table.A pplyLogOnI nfo(TableL ogOnInfo logonInfo) +628
Admin_WorkStation.FDTDClea red1.Page_ Load(Objec t sender, EventArgs e)
System.Web.UI.Control.OnLo ad(EventAr gs e) +67
System.Web.UI.Control.Load Recursive( ) +35
System.Web.UI.Page.Process RequestMai n() +772
Again, all report run fine on the local machine.
All reports not using stored procedures run on both machines!!!!??????
Thanks
boomer1946
Yes it is against the same database:
Below is the code followed by the error on the server:
protected CrystalDecisions.Web.Cryst
protected Admin_WorkStation.CachedFD
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.CrystalRe
CrystalDecisions.CrystalRe
//CrystalDecisions.Crystal
private void Page_Load(object sender, System.EventArgs e)
{
// Put user code to initialize the page here
crConnectionInfo.ServerNam
crConnectionInfo.DatabaseN
crConnectionInfo.UserID = "ofac";
crConnectionInfo.Password = "ofac";
crDatabase = crReportDocument.Database;
crTables = crDatabase.Tables;
foreach (CrystalDecisions.CrystalR
{
crTableLogOnInfo = crTable.LogOnInfo;
crTableLogOnInfo.Connectio
crTable.ApplyLogOnInfo(crT
}
crTableLogOnInfo.TableName
// Create Parameter
//string prInvId = Session["sInvId"].ToString
//crParameterField.Paramet
//crParameterDiscreteValue
//crParameterField.Current
//crParameterFields.Add(cr
string prFDate = Session["sFDate"].ToString
DateTime prDateTime = DateTime.Parse(prFDate);
crParameterField1.Paramete
//crParameterDiscreteValue
crParameterDiscreteValue1.
crParameterField1.CurrentV
crParameterFields.Add(crPa
string prTDate = Session["sTDate"].ToString
DateTime prTDateTime = DateTime.Parse(prTDate);
crParameterField2.Paramete
//crParameterDiscreteValue
crParameterDiscreteValue2.
crParameterField2.CurrentV
crParameterFields.Add(crPa
CrystalReportViewer1.Param
CrystalReportViewer1.Repor
}
error on server
The table 'sp_FD_TD_Cleared;1' could not be found. Error in File C:\WINDOWS\TEMP\{66EDFA3D-
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.InteropServ
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-
The table could not be found.]
CrystalDecisions.ReportApp
CrystalDecisions.CrystalRe
CrystalDecisions.CrystalRe
CrystalDecisions.CrystalRe
Admin_WorkStation.FDTDClea
System.Web.UI.Control.OnLo
System.Web.UI.Control.Load
System.Web.UI.Page.Process
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
Comment out the log oncode for the tables and try it.
mlmcc
ASKER
Operationing System - Development local - Windows 2000
Operating System - Deployed Server Windows Server 2003
Operating System - Deployed Server Windows Server 2003
ASKER
Database local sql server 2000
Server sql server 2005
Server sql server 2005
ASKER
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.ServerNam e = "ofacServer"; was OFACDB
crConnectionInfo.DatabaseN ame = "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!!!!!
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.ServerNam
crConnectionInfo.DatabaseN
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
mlmcc
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
When you run locally is it against the same database?
mlmcc