Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Dynamic Reports working with tables but not with Stored Proceedures - URGENT

Posted on 2003-11-20
6
Medium Priority
?
1,437 Views
Last Modified: 2008-03-10
We are using Crystal Reports 9

We are able to overide the connection to the tables with the following:
===================================================

Dim objectFactory,ConInfos,ConInfo,DBName
Set objectFactory = CreateObject("CrystalReports.ObjectFactory.2")

Response.ExpiresAbsolute = Now() - 1

Set ConInfos = ObjectFactory.CreateObject("CrystalReports.ConnectionInfos")
Set ConInfo = Server.CreateObject("CrystalReports.ConnectionInfo")

DBName = Trim(Request("DBName"))

Dim ConnAtts : Set ConnAtts = Server.CreateObject("CrystalReports.PropertyBag")
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("CrystalClientDoc.ReportClientDocument")
Dim tableCollection, table
clientDoc.Open theReportName

Dim tables, newTable

Set tables = clientDoc.Database.Tables

For Each table In Tables
      Set newTable = Server.CreateObject("CrystalReports.Table")
      Set newTable = table.Clone(True)
      newTable.ConnectionInfo = ConInfo
      clientDoc.DatabaseController.SetTableLocation 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-05D0E8482FCE}.rpt: 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
0
Comment
Question by:Michael Krumpe
6 Comments
 
LVL 7

Accepted Solution

by:
mnye earned 2000 total points
ID: 9790064
heres the basics on SP with Crystal:
http://support.crystaldecisions.com/communityCS/TechnicalPapers/storproc.pdf


but it sounds like you just need to apply a hotfix:

http://support.crystaldecisions.com/library/kbase/articles/c2013836.asp

hth
matt
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 9792871
This question has been asked before and I believe the answer was you cannot switch a report built with tables to use stored procedures through code.

If that is what you are trying to do I'll try to find the reference.

mlmcc
0
 
LVL 4

Author Comment

by:Michael Krumpe
ID: 9797081
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?

0
How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

 
LVL 101

Expert Comment

by:mlmcc
ID: 9797436
0
 
LVL 4

Author Comment

by:Michael Krumpe
ID: 9798202
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
0
 

Expert Comment

by:chongwh
ID: 10559212
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-23D6B5988405}.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
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Suggested Courses

926 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question