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,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
LVL 4
Michael KrumpeSolutions ArchitectAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mnyeCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mlmccCommented:
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
Michael KrumpeSolutions ArchitectAuthor Commented:
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
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

Michael KrumpeSolutions ArchitectAuthor Commented:
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
chongwhCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.