Skip to Content
author's profile photo Former Member
Former Member

First time an SQL expression is placed on report a DB SELECT executes -CRXI

The same problem can be created using the normal Crystal Reports XI Release 2 developer environment and/or code (not only a code issue).

Symptom:

We dynamically auto generate SQL Expressions, Tables, Joins, Formula's and anything through C# and place it onto the Crystal Report through code via run-time user input in a web environment. I recieved a notification from one of my DB admins that crystal reports is causing massive memory consumption via unknown SELECT statements that have no joins and/or WHERE criteria.

Some Detective Work/More Info:

After using Microsoft "SQL Profiler" along with Crystal, I understand what is happening but don't know the "work around" to make Crystal stop executing the following SELECT scenario.

Anytime I create a new "SQL Expression" for the first time and then physically "drag and drop" it onto the report then Crystal is executing a "silent" SELECT statement against the database in the design environment. I'm calling it "silent" because I need MS SQL Profiler to see it happen. I'm guessing that this is used for validation or something but this is causing problems when automatically generating a report in a production environment.

Steps to re-create:

1. Open MS "SQL Profiler" and get it running. (will show Crystal Engine silently executes the SELECTS)

2. Add a new "SQL Expression" to your report.

3. Drag and Drop your new "SQL Expression onto your report

4. You will see that Crystal has created an SQL query and executed it against your database in design environment.

The Problem:

I can see under normal circumstances why you might want this funtionality (if it's actually used for the Crystal Engine to validate your SQL Expression). But, What also makes this functionality horrible is that on my behalf Crystal is creating these SELECT statements and using cross joins which is a major problem in a production environment. As a result, huge record sets are being created on our DB Server. I don't believe that this has any purpose in a production evironment when the cross-joins can create huge amounts of results just for the sake of Crystal Internally validating a new SQL Expression placed onto a report. It would be nice to disable this functionality in production. I don't know a "work around" since these SELECT queries are created on my behalf by the Crystal Engine at design time (via code auto generation in production).

Questions:

Can this functionality be disabled to prevent a production server from incurring the execution of these queries?

Anybody else notice this and have a "work around"?

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

1 Answer

  • Posted on Oct 06, 2009 at 01:00 AM

    duplicate - please do not post multiple times

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.