How can I modify the SQLQueryString at runtime? (VB6 Application, CR XI)

[Former Member]

I have created a report in Crystal Reports XI which uses a stored procedure to retrieve data. The report was created using our test database (AS/400 DB2) as its datasource. In our Visual Basic 6 application we want to be able to run against the production database. In the code a CRAXDRT.Report object is instantiated and the .rpt file is specified in the OpenReport command. I can see that the SQLQueryString property is {CALL "TESTSERV"."ABCLIB"."RPT_ONE_SP"}, where TESTSERV is the test machine, ABCLIB is the library (Default Collection property value in the connection properties which can be modified) and "RPT_ONE_SP" is the stored procedure name. If a change to the SQLQueryString is attempted an error is raised. Even if all connection properties are changed the stored procedure call still defers to what is specified in the call (from the .rpt file). Is there a workaround for this situation?

[Former Member · Accepted solution]

Hello Dan,

If you're trying to change databases at runtime in your VB6 application using the RDC you shouldn't try to edit the SQL query in the report. It won't work.

You can change databases at runtime using the ConnectionProperties method. There's a Connection Properties document on the SAP website that explains how to change database connections at runtime.

Here's the link: [Connection Properties|https://www.sdn.sap.com/irj/sdn/go/portal/prtroot/docs/library/uuid/00635998-751e-2b10-9cba-f50ee1e4ef81] with the RDC.

The document was written for CR9, but the ConnectionProperties method is the same for CR 10, CR XI (v11.0), and CR XI R2 (v11.5).

Sincerely,

Dan Kelleher

[Former Member · Accepted solution]

Thanks for responding Daniel. I am already changing the Connection Properties based on the properties of the ADO connection object created when the user logs in to the application. This works (points to the correct database) for the reports which use a SQL statement to retrieve data (because I have a loop changing the properties for each table, CRXTable in CRXTables) but it does not work if the report is created using a stored procedure as its method of data retrieval. That is why I want to modify the "CALL" statement in SQLQueryString. From what I have read in the forums this is not possible but I would like to hear it from someone at SAP/Business Objects. I don't think this should be so hard to do. An alternative might be to change the DataSource of the report and provide an ADO Recordset.

[former_member184995 · Accepted solution]

Hello Daniel,

Daniel Kelleher is an SAP employee and his answer is correct. You can not change the SQL query on the report.

The connection property should be working for a stored proc as well and I am not sure why it isn't.

You will want to create a new thread about the connection property not working for stored procs to seperate it from the modify sql question. This will help you get more focused help.

Jason

[former_member11696 · Accepted solution]

Hi Daniel,

Why are you making changes to the SQL for a Stored Procedure? It doesn't make sense, you use a Stored Procedure to pass just a data set with the final results to Crystal to process. The onus is on you to make the changes in the Stored Procedure SQL or use parameters to alter the results.

When a report is based on a Stored Procedure CR simply calls/executes the SP and waits for the result set. If you have Parameters in the SP then use Crystals Parameter API's to set/update them. Don't try to modify the SQL directly to change the values or name etc. We need to keep track of the values passed to update any formula or other Report objects that may use your parameter name. Making the change manually does not update the report objects and will likely result in invalid data or a SQL error to be generated.

Thank you

Don

[Former Member · Accepted solution]

I'm not sure which objects you're using but if you're using the RDC like I am then you can set the sql string. I use the RDC in Foxpro and code similar to the following works fine for me

loRDC = CreateObject("CrystalRuntime.Application.11")

loReport = loRDC.OpenReport("C:\Data\InfoToday\Projects\Womens Hospital\Source\IVFTrack\CrystalReports\datadump.rpt")

loreport.SQLQueryString = loreport.SQLQueryString+" where

Infprot.pocode=2060"

I'm upset that a similar thing is not possible in version 12 of the .Net SDK. Now that the RDC is no longer supported into version 12 I can't find any way to do the same in Crystal Reports 2008.

[former_member183750 · Accepted solution]

To achieve the same or similar results in CR 2008 and .NET, you have to create an ADO .NET dataset off of your SQL statement. Then pass that to the report:

explicitly:

crReportDocument.Database.Tables(0).SetDataSource(dataSet.Tables("NAME_OF_TABLE"))

implicitly:

crReportDocument.SetDataSource(dataSet) or crReportDocument.SetDataSource(dataSe.Tables(0))

Ludek

[Former Member]

I too use the SQLQueryString all the time and I too need to use it like I used to in CR9.

I have a report that is all set and working properly. However some reports need farily complicated extra things added to the SQL before it runs the SQL.

In our application we have about 500 reports in total and about 50 of them have extra info needed for the SQL string.

Why is is this parameter not available any more?

[Former Member]

Hello Mark,

Changing the SQL Query String was never supported by Business Objects / Crystal Decisions. Even though it used to work in earlier versions of Crystal Reports it was never an official part of the product's functionality. With the release of CR XI / XIR2 the ability to edit the SQL Query was finally locked down.

Please see the other posts in this thread for possible work arounds.

Sincerely,

Dan Kelleher

[Former Member]

Thank you all for your responses. Unfortunately I don't have a solution to my problem. First, to Don, I am not trying to alter the SQL of a stored procedure. I am trying to alter the "CALL ..." of the stored procedure that is part of the .rpt file. I want to do this so that I can dictate from my application which database (production or test, for example) the stored procedure being called is part of. At design time the "CALL ..." statement is saved with the database the report was created/saved with. As you can see in my first post this (catalog/library name) is part of the "CALL...". One possible work around is similar to what someone here suggested (just a different environment). I can create an ADO.Recordset and use it as the data source of the report object. The record set would be created by calling the appropriate stored procedure with an ADO.Command object. I have not had time to try this yet.

[former_member11696]

Hi Daniel,

You can't change the info directly in the SQL you have to use the .Location method. This will update the SQL and re-direct from your original server to the new server.

Please read the info in the link Dan attached to is first reply. It will show you how to do this and it does work.

Once you set all the logon info then use the .Verify method or Testconnectivity API to confirm it's going to the new Server. Then export the report to RPT format to confirm the new logon info is saved.

Changing to SQL Server (OLE DB) at Runtime

'Declare a Connection Info Object

Dim ConnectionInfo As CRAXDRT.ConnectionProperties

'Set the Connection Info to Connection Properties of u2018the table object

Set ConnectionInfo = Report.Database.Tables(1).ConnectionProperties

'Set the DLL name

Report.Database.Tables(1).DLLName = u201Ccrdb_ado.dllu201D

'Clear the ConnectionProperties collection

ConnectionInfo.DeleteAll

'Add the OLE DB Provider

ConnectionInfo.Add u201CProvideru201D, u201CSQLOLEDBu201D

'Add the physical server name

ConnectionInfo.Add u201CData Sourceu201D, u201CServer nameu201D

'Add the database name

ConnectionInfo.Add u201CInitial Catalogu201D, u201CDatabase nameu201D

'Add the user name

ConnectionInfo.Add u201CUser IDu201D, u201CUser nameu201D

'Add the password

ConnectionInfo.Add u201CPasswordu201D, u201CPasswordu201D

'Set the fully qualified table name if different from u2018the original data source

Report.Database.Tables(1).Location = u201Cdatabase.owner.tablenameu201D

Thank you

Don