Cannot call scalar-valued CLR UDF from select ... from table statement

Question

I have created a scalar-valued CLR UDF (user defined function). It takes a timezone id and a datetime and returns the datetime converted to that timezone.

I can call it from a simple select without problems: "select dbo.udfConvert('Romance Standard Time', @datetime)" (@datetime is of course a valid datetime variable)

But if I call it passing in a datetime from a table it fails: "select dbo.udfConvert('Romance Standard Time', StartTime) from sometable" (column StartTime is of course a column of type datetime)

The error message is: "Cannot find either column "dbo" or the user-defined function or aggregate "dbo.udfConvert", or the name is ambiguous."

This message is really for beginners that has misspelled something, but as it works in one case and not in the other, I don't think I have done any misspellings.

Any ideas?

Answer 1

I found the problem. If I extracted the hardcoded timezone out to a variable instead it worked fine:

declare @timeZoneID nvarchar(100)

select @timeZoneID='Romance Standard Time'

Select dbo.Convert(@timeZoneID,StartTime) From sometable

Apparently sql-server could not figure out how to convert the hardcoded string to a nvarchar string.

Answer 2

you can also do:

select dbo.udfConvert(N'Romance Standard Time', StartTime) from sometable

If you do not prefix a Unicode string constant with N, SQL Server will convert it to the non-Unicode code page of the current database before it uses the string. That's why i could not find the function with the nvarchar signature.