CR 2008 SP3 FP 3.4 does not provide SQL Server Native Client 10.0

[timothyromine]

One of my clients is running Crystal Reports 2008 SP3 Fix Pack 3.4 on a Windows 2008 R2 server and is running MS SQL 2008 (not R2) on a separate Windows 2008 R2 server. This was installed to correct printer issues. As expected, they started getting errors on some that use dates in the record selection formula.

I informed them that we needed to change the data connection driver from "Microsoft OLE DB Provider for SQL Server' to "SQL Server Native Client 10.0". When they go in to create a new data connection the OLE DB (ADO) Provider list does not list "SQL Server Native Client 10.0" as an option.

As I understand it from another thread on this site, this OLE DB client version is required for CR 2008 to properly interface with SQL 2008 and that it should be available after installing FP 3.2 or 3.3. I made sure that they installed all fix packs in their released order.

Can anyone shed some light on this for me?

[former_member11696 · Accepted solution]

You are correct, you need at least FP 3.2 to get support. CR does not install the DB client, you get that from Microsoft.

Either by downloading their client tools or running the Client Tools from MS's SQL Server CD.

Thank you

Don

[timothyromine · Accepted solution]

Thanks.

SQL Native Client 10 is now installed on their print server and it has been rebooted.

I have opened the report and changed its data source to use the SQL Native Client 10. The report runs fine from within the report designer.

When we run the report from their web app, we get the same error:

There was a problem printing the form.

A boolean is required here. Details: errorKind Error in File FTW_DSM_1093_Cases_with_no_WD_or_ACH {E70ADB42-220D-4358-86B5-F4C622411C15}.rpt: Error in formula Record Selection: ' ( ( ( ( ( ( {tblCaseData.CloseCodeID} = 2 AND ( {tblCaseData.CaseSplitTrusteeCode} <> "A" ) ) AND ( NOT {%sql_Adj341Date} >= {?Beg_Adj_341_Date} ) ) AND {%sql_Adj341Date} < DateAdd ( 'd', 1, {?End_Adj_341_Date} ) ) AND {%sql_CountWageDir} = 0 ) AND {%sql_CountACH} = 0 ) AND {%sql_CountNameIDParties} = 0 ) ' A boolean is required here. Details: errorKind

Also, there were no errors during any of the installation processes.

[timothyromine · Accepted solution]

Further developments. I have duplicated this issue on my development system using:

CR 2008 SP3 FP3.4

MS SQL 2008 SP2

Windows 2003 R2 SP3

I tried recreating the report using the SQL Server Native Clent 10.0 driver - it runs fine from the developer but it returns the "boolean" error whenever run from the web app.

When I take the date limitations out of the Record Selection Formula the report runs without error - it just returns incorrect data.

[timothyromine · Accepted solution]

Also, we are using .Net 2.0

[former_member11696 · Accepted solution]

Then use Fiddler or some other tool that can trace into the client access. Could be the WEB server can't find the Native 10 drivers.

Compare the runtime difference between the IDE and when running tyhrough IIS.

Thank you

Don

[timothyromine · Accepted solution]

The issue only seems to occur if the record selection formula uses a parameter field to limit a date. If I use a literal date or another database date there is no issue. And this is a Date parameter field - we don't use DateTime parameter fields.

We are looking into the Fiddler program as well.

[former_member11696 · Accepted solution]

Hello, Also look at the formatting of the date parameter. Could be CR or you are not formatting it correctly or CR is interpreting it after you set it.

Thanks again

Don

[timothyromine · Accepted solution]

Tried reformatting the date several different ways - no joy. It is still putting that "NOT" in the middle of the selection criteria that should not be there.

Also tried running the report using the Crystal Viewer via the web app - it returns the same "A boolean is required here" error.

Tried using Fiddler to debug this - I don't see how it will tell me whether IIS sees the SQL Server Client 10 driver.

[timothyromine · Accepted solution]

The record selection formula for the report is:

{tblCaseData.CloseCodeID} = 2

and {tblCaseData.CaseSplitTrusteeCode} <> 'A'

and {%sql_Adj341Date} >= {?pf_BeginDate}

and {%sql_Adj341Date} < DateAdd ( 'd', 1, {?pf_EndDate} )

and {%sql_CountACH} = 0

and {%sql_CountNameIDParties} = 0

and {%sql_CourtWageDir} = 0

Clearly, there is no "NOT" statement here.

[former_member11696 · Accepted solution]

Hello,

Actually there is:

{tblCaseData.CloseCodeID} = 2

and {tblCaseData.CaseSplitTrusteeCode} 'A'

and {%sql_Adj341Date} >= {?pf_BeginDate}

and {%sql_Adj341Date} < DateAdd ( 'd', 1, {?pf_EndDate} )

and {%sql_CountACH} = 0

and {%sql_CountNameIDParties} = 0

and {%sql_CourtWageDir} = 0

{%sql_Adj341Date} >= {?pf_BeginDate} and {%sql_Adj341Date} < DateAdd ( 'd', 1, {?pf_EndDate} ) is the result of NOT statement.

Try something like this:

{tblCaseData.CloseCodeID} = 2

and {tblCaseData.CaseSplitTrusteeCode} 'A'

and ({%sql_Adj341Date} >= {?pf_BeginDate}

and {%sql_Adj341Date} < DateAdd ( 'd', 1, {?pf_EndDate} ))

and {%sql_CountACH} = 0

and {%sql_CountNameIDParties} = 0

and {%sql_CourtWageDir} = 0

I added AND/OR/NOT etc. logic to:

and ({%sql_Adj341Date} >= {?pf_BeginDate}

and {%sql_Adj341Date} < DateAdd ( 'd', 1, {?pf_EndDate} ))

You'll have to figure out what the correct bracketing should be for your data and what you want to get out of it. Possibly moving the date part to the end of the selection formula will "fix" this.

Thank you

Don

[timothyromine · Accepted solution]

Don,

Thanks for responding.

I tried changing bracketing and moving the date comparisons around in the selection formula before I started this thread in an attempt to resolve the issue. The only thing, so far, that "fixes" it is to perform a Crystal DATE command on {%Sql_Adj341eate}. However, this isn't really a fix since it forces SQL to return a MUCH larger dataset to Crystal.

At this point, I have escalated the issue and opened a support call. The team head that I spoke to thought it looked like an issue in the CR .net application since the report works fine in the Report Developer but not via either our web app or the .net Crystal Viewer tool.

[former_member11696 · Accepted solution]

Good to know.... Yes if CR can't convert the date correctly it will ignore and bring it all down.

What is the case number? I'm curious who and how it is going.

Thanks

Don

[timothyromine · Accepted solution]

845244/2011 - Bhushan Hyalij is looking at it.

So far, we have established that if you run the report using Crystal Viewer without modifying the record selection formula, it works fine.

If you extract the record selection formula and display it using

Dim CrystalSelectionCriteria As String = crReport.RecordSelectionFormula

response.write(CrystalSelectionCriteria)

response.end

it has the unnecessary "NOT"s in it and, without having changed anything in the record selection formula string, it does not function properly when it is passed immediately back into the report using

crReport.RecordSelectionFormula = CrystalSelectionCriteria

crReport.Refresh()

I am waiting to hear back from Bhushan to work on it more today.

Thanks,

Tim

[timothyromine · Accepted solution]

Latest word is that Bhushan was able to duplicate the error. His evaluation was:

-

---

It seems that the issue is due to the first evaluative statement with the ">=" comparison. It appears that selection formula where the first evaluation is <= or >=, produces a NOT in front while accessed.

The workaround for this issue is to simply add "true AND" as the first evaluation statement, and it will allow the rest of the selection formula to work without errors. I have tested the workaround and it works well. Formula will be as follows:

Original

{tblTestData.TestDateTime} >= {?pf_StartDate} And {tblTestData.TestDateTime} < {?pf_EndDate} + 1

Workaround

true AND {tblTestData.TestDateTime} >= {?pf_StartDate} And {tblTestData.TestDateTime} < {?pf_EndDate} + 1

This is a workaround to get the application up and running immediately. We will work on getting this resolved in an upcoming fixpack.

-

---

[former_member11696 · Accepted solution]

Thanks for the update Tim,

Also note, I tested this in CR for VS 2010 and it's not an issue so it is something that has been fixed and will likely be in a future Fix Pack for CR 2008 once we find out what the track number is we can branch it for this version also.

Thank you

Don