Errors sending command parameter to UDF

[damonkimble]

Crystal 10 connecting to MSSQL 2000 (sp3a) via ODBC (MDAC 2.8)

I am attempting to send command parameters to a UDF but keep getting a rowset error. I start out with hardcoded dates and after confirming the query works in the command, added parameters and replaced the hardcoded dates. The UDF parameters are datetime. Any suggestions would be appreciated.
cheers,
damon

Here is a scaled down version of my command query.

select distinct ap.person_id, ap.last_name, ap.first_name, isNULL(lr.cnt,0) as logs
from udf_activepersonallgroups({?DateFrom}, {?DateTo}) as ap
	left outer join (select parent_id as person_id, count(*) as cnt
			from logon_record 
			where cast(left(logon_date,12) as datetime) between {?DateFrom} and {?DateTo}
			group by parent_id) as lr
		on ap.person_id = lr.person_id

If I replace:

from udf_activepersonallgroups({?DateFrom}, {?DateTo}) as ap

with

from udf_activepersonallgroups('2005-01-01','2005-12-31') as ap

it works. There is no issue with using the second instance of the parameters.



Here is the series of error messages:
1. Failed to open a rowset.

2. Query Engine Error: '42000:[Microsoft][ODBC SQL Server Driver][SQL Server]Line 3: Incorrect syntax near '{'.'

3. Failed to open a rowset.
Details: '42000:[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'as.'

4. Failed to open a rowset.



I tried a few different quote combinations with the following results. The double quote one is really odd.
No Quotes:
Query Engine Error: '42000:[Microsoft][ODBC SQL Server Driver][SQL Server]Line 3: Incorrect syntax near '{'.'

Single Quotes:
Query Engine Error: '42000:[Microsoft][ODBC SQL Server Driver][SQL Server]Line 3: Incorrect syntax near '2005'.'

Pair of Single Quotes:
Query Engine Error: '42000:[Microsoft][ODBC SQL Server Driver][SQL Server]Line 3: Incorrect syntax near '{'.'

Double Quotes:
Query Engine Error: '42000:[Microsoft][ODBC SQL Server Driver][SQL Server]Line 3: Incorrect syntax near '{d '2005-12-31'}'.'



I tried in XI and came up with the same errors with slightly different syntax.

I also tried using OLE DB and came up with:
Failed to open a rowset.
Details: ADO Error Code: 0x80040e14
Source: Microsoft OLE DB Provider for SQL Server
Description: Line 3: Incorrect syntax near '{'.
SQL State: 42000
Native Error: 170

 

[damonkimble]

FYI, it had to do with the fact that UDFs cannot deal with non-deterministic functions (e.g. getdate()). I had to declare a variable, set the variable to the value of the parameter and sent the variable to the UDF.

this works!:

declare @dt datetime
set @dt = {?dt}

select list_date from udf_test(@dt)

this does not:

select list_date from udf_test({?dt})

Hope this helps someone else down the road.

cheers,
damon