Pervasive 8.70.014 ODBC with SSRS 2005

[mrdjsm1th]

I am trying to convert a SQL command from a Crystal XI report to a SQL Server 2005 Reporting Services (SSRS) dataset. I am using the Pervasive ODBC client interface with the Crystal XI report and the SSRS dataset.

The Crystal XI SQL command successfully uses several parameters, but I can't get any parameters to work within an SSRS dataset. I am getting an error "The data extension ODBC does not support named parameters. Use unnamed parameters instead."

For example, the following Crystal XI command works fine:

 Select * from slbudget where locationid = {?Location}

In SSRS, the following statement does not work:

 Select * from slbudget where locationid = @Location

I get the following error message when running the above statement in SSRS: "Could not generate a list of fields for the query. Check the query syntax, of click Refresh Fields on the query toolbar." Additional Information: ERROR [42000][Pervasive][ODBC Client Interface][LNA][Pervasive][ODBC Engine Interface]Syntax Error: select * from slbudget where locationid = @<<???>>Location (W3ODBCI.DLL)

Is it possible to use parameters within a SSRS dataset that uses the Pervasive 8.70 ODBC interface?

 

[mirtheil]

Parameters in PSQL are marked as "?" so your command would be:

Select * from slbudget where locationid = ?

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com

 

[mrdjsm1th]

Thanks Mirtheil. I hope you can help me through a couple scenarios.

Scenario #1

Some of the reports I am converting have multiple parameters. For example:

 select * from slbudget where locationid = ? and YearNo = ? and TypeID = ?

Currently, all three parameters are displayed as "?", so the user won't be able to determine which parameter accepts which run-time variable.

SSRS sees each ? as a parameter (and calls them Parameter1, Parameter2 and Parameter3) so I tried to assign a meaningful name to each parameter as follows:

Parameter1 "Location"
Parameter2 "YearNo"
Parameter3 "TypeID"

Using the above parameter names causes 3 errors:

[rsParameterReference] The Value expression for the query parameter ‘?’ refers to a non-existing report parameter ‘Parameter1’.
[rsParameterReference] The Value expression for the query parameter ‘?’ refers to a non-existing report parameter ‘Parameter2’.
[rsParameterReference] The Value expression for the query parameter ‘?’ refers to a non-existing report parameter ‘Parameter3’.

However, the above three parameters work as long as I don't give them meaningful names, so I might still be able to work around this issue.

Scenario #2

Some of the Crystal XI reports have SQL commands with multiple union statements that reference one parameter value.

For example:

select sum(Qty) as TotalQty from table1 where date >= ?
union select sum(Qty) as TotalQty from table2 where date >= ?

In the above example the records from two tables would be combined based on a single date parameter (the parameter is named {?Date} in the Crystal XI command). When I execute the above statement in SSRS I get the following error:

"SQL Execution Error. SQLBindParameter has not been called for parameter #2".

Is it possible to use one parameter multiple times within unioned statements?