SProcs, Crystal Reports and whole bunch of Pain.

[JNeave]

Hi, this is an complex one...

We have many Stored Procedures (SPs) reporting on many tables dependant on many criteria. The criteria for each SP can vary wildly.

So, we gave EVERY SP a parameter called @vcWHEREClause(8000)

This the gets concatonated into the SQL inside the SP.

Crystal 7 reports on these SPs through OLE.

VB6 calls the report doc, passing in a complete WHERE clause as a varchar through a crystal parameter to the SP.

THE PROBLEM

Crystal %-( does not support string parameters longer than 254 characters. Out VB app Dr. Watsons if you pass in a string longer than 267 to Crystal.

Possible solutions we have though of.

1. Have several WHERE clause parameters of type VARCHAR(267) and pass in a chopped up WHERE clause.
2. Have a 2D array of 267x(FLOOR(LEN(<WHERE Clause>)/267)) and concatonate them in a loop inside the SP
3. Hard code every possible criteria into every SP (Pain)
4. Something to do with writing the Criteria to a temp table and then telling the SP where it all is.

Somebody please help me....

tons of cheers,

Jim.

 

[DBCustard]

I have recently had a similar problem, I solved it by initially binding the Crystal report to an ADO active data datasource (pointing a some psudo-temporary table, with the same structure as the output from the stored procedure). Then at runtime, create a recordset from the results, passing in the full 4000 character where clause. Bind the ADO recordset to crystal using this simeple bit of code:

Public Sub Preview(ByVal RecordSource As ADODB.Recordset, ByVal ReportFilename As String)
On Error GoTo procError
Const msPROCNAME = &quot;.Preview&quot;

Dim moCrystal As CRAXDRT.Application
Dim moCryReport As CRAXDRT.Report

Set moCrystal = New CRAXDRT.Application
Set moCryReport = moCrystal.OpenReport(ReportFilename)

'Call moCryReport.DiscardSavedData
Call moCryReport.Database.SetDataSource(RecordSource)
Call moCryReport.Database.Verify

CRViewer1.ReportSource = moCryReport
CRViewer1.ViewReport
Me.Show vbModal

Set moCrystal = Nothing
Set moCryReport = Nothing

Exit Sub
procError:
psErrDesc = TypeName(Me) & msPROCNAME & &quot; (&quot; & Err.Number & &quot &quot; & _
Replace(Err.Description, vbCrLf, &quot; &quot

'Clean up

Call Err.Raise(vbObjectError, TypeName(Me), psErrDesc)
End Sub


That way Crystal never has to worry about passing the parameter onto your SP!

 

[JNeave]

thanks mate,

we have solved the problem in a slightly different manner though.

instead of passing the WHERE clause through the report, the report scheduler app stores the where clause in the ReportLog table in an NTEXT field, one unique ReportLogID per generated report.

Then only the ReportLogID is passed through Crystal, although if this fails to work aswell, we'll try your method next.

cheers,

Jim.

 

[fluteplr]

If you are passing where clauses to your stored procs instead of the normal parameters. Why don't you rewrite your stored procs as views? You can then just pass the where clause thru crystal as a report criteria instead of a parameter and the 254 character limit does not apply.

 

[JNeave]

Because views don't have half the functionality we need. The current solution of passing a pointer to the WHERE clause stored in the database seems to be working just fine (for the moment).

cheers anyway,

Jim.