Michael Krumpe
asked on
Dynamic Reports working with tables but not with Stored Proceedures - URGENT
We are using Crystal Reports 9
We are able to overide the connection to the tables with the following:
========================== ========== ========== =====
Dim objectFactory,ConInfos,Con Info,DBNam e
Set objectFactory = CreateObject("CrystalRepor ts.ObjectF actory.2")
Response.ExpiresAbsolute = Now() - 1
Set ConInfos = ObjectFactory.CreateObject ("CrystalR eports.Con nectionInf os")
Set ConInfo = Server.CreateObject("Cryst alReports. Connection Info")
DBName = Trim(Request("DBName"))
Dim ConnAtts : Set ConnAtts = Server.CreateObject("Cryst alReports. PropertyBa g")
ConnAtts.EnsureCapacity 3
ConnAtts.Item("Database DLL") = "pdsodbc.dll"
ConnAtts.Item("Database Name") = DBName
ConnAtts.Item("Server Name") = DBName
With ConInfo
.Attributes = ConnAtts
.UserName = Request("UserName")
.Password = Request("Password")
.Kind = 1
End With
===============
etc.
Then recalling the tables with the connection to call the report
===============
Dim clientDoc
Set clientDoc = ObjectFactory.CreateObject ("CrystalC lientDoc.R eportClien tDocument" )
Dim tableCollection, table
clientDoc.Open theReportName
Dim tables, newTable
Set tables = clientDoc.Database.Tables
For Each table In Tables
Set newTable = Server.CreateObject("Cryst alReports. Table")
Set newTable = table.Clone(True)
newTable.ConnectionInfo = ConInfo
clientDoc.DatabaseControll er.SetTabl eLocation table, newTable
Set newTable = Nothing
Next
viewer.ReportSource = clientDoc.ReportSource
========================== ========== ========== ===
The above works beautifully to pull the Report, specifiying the DB Dynamically and view the report.... that has tables....
If the report uses Stored Proceedures... the above loop does not work and gives the following error:
========================== ========== ========== ===
RptControllers.dll error '800002c5'
The table 'p_NonLevel_Report;1' could not be found. Error in File C:\WINDOWS\TEMP\{667260C3- E94B-41C8- 9DF5-05D0E 8482FCE}.r pt: The table could not be found
========================== ========== ========== ===
Being that the name p_NonLevel_Report:1 is actually a stored proc., not a table at all.
We need a solution to fix the loop to call both tables and stored proceedures and the report understand each.
- Michael
We are able to overide the connection to the tables with the following:
==========================
Dim objectFactory,ConInfos,Con
Set objectFactory = CreateObject("CrystalRepor
Response.ExpiresAbsolute = Now() - 1
Set ConInfos = ObjectFactory.CreateObject
Set ConInfo = Server.CreateObject("Cryst
DBName = Trim(Request("DBName"))
Dim ConnAtts : Set ConnAtts = Server.CreateObject("Cryst
ConnAtts.EnsureCapacity 3
ConnAtts.Item("Database DLL") = "pdsodbc.dll"
ConnAtts.Item("Database Name") = DBName
ConnAtts.Item("Server Name") = DBName
With ConInfo
.Attributes = ConnAtts
.UserName = Request("UserName")
.Password = Request("Password")
.Kind = 1
End With
===============
etc.
Then recalling the tables with the connection to call the report
===============
Dim clientDoc
Set clientDoc = ObjectFactory.CreateObject
Dim tableCollection, table
clientDoc.Open theReportName
Dim tables, newTable
Set tables = clientDoc.Database.Tables
For Each table In Tables
Set newTable = Server.CreateObject("Cryst
Set newTable = table.Clone(True)
newTable.ConnectionInfo = ConInfo
clientDoc.DatabaseControll
Set newTable = Nothing
Next
viewer.ReportSource = clientDoc.ReportSource
==========================
The above works beautifully to pull the Report, specifiying the DB Dynamically and view the report.... that has tables....
If the report uses Stored Proceedures... the above loop does not work and gives the following error:
==========================
RptControllers.dll error '800002c5'
The table 'p_NonLevel_Report;1' could not be found. Error in File C:\WINDOWS\TEMP\{667260C3-
==========================
Being that the name p_NonLevel_Report:1 is actually a stored proc., not a table at all.
We need a solution to fix the loop to call both tables and stored proceedures and the report understand each.
- Michael
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
mlmcc, the compiled report itself is referencing stored procs, not tables, thus the error. We are preparing to do the Service Pack update above referenced by Matt... hope that will be the fix.
Question: is there any code as with my loop above that we may reference the collection to identify the stored procs vs. tables, so that the report will work correctly?
Question: is there any code as with my loop above that we may reference the collection to identify the stored procs vs. tables, so that the report will work correctly?
ASKER
We will be applying the service pack soon...
side note... we got all of the reports working, both with tables and with stored procs, on the db, we were not applying the proper security for the CrystalUser to have access to the stored proc itself.
Cause in point: CHECK YOUR DB SECURITY
Thanks for all your help.
- Michael
side note... we got all of the reports working, both with tables and with stored procs, on the db, we were not applying the proper security for the CrystalUser to have access to the stored proc itself.
Cause in point: CHECK YOUR DB SECURITY
Thanks for all your help.
- Michael
Hi MlCC,
After Applying SP also i am getting the same Error as follows:
Error Type:
RptControllers.dll (0x800002C5)
The table 'skwprojects1.dbo.Command' could not be found. Error in File C:\rpttemp\{94BC1513-7F7B- 4303-B77E- 23D6B59884 05}.rpt: The table could not be found.
/skw/ActiveX.asp, line 250
Is there any security level i ahve set in DB, i m using Mysql.
Plz guide me
Thanks a lot
After Applying SP also i am getting the same Error as follows:
Error Type:
RptControllers.dll (0x800002C5)
The table 'skwprojects1.dbo.Command'
/skw/ActiveX.asp, line 250
Is there any security level i ahve set in DB, i m using Mysql.
Plz guide me
Thanks a lot
If that is what you are trying to do I'll try to find the reference.
mlmcc