Datetime type mismatch trying to publish a SQL CLR database

Viewed 384

I'm trying to stand up an old solution that's publishing a number of .net functions to a SQL Server database. But the attempt to publish to a new database is failing on a function that manipulates dates.

The function that's failing is:

[SqlFunction(TableDefinition="localtime datetime2", IsDeterministic=true, IsPrecise=true,
             DataAccess=DataAccessKind.None,
             SystemDataAccess=SystemDataAccessKind.None)]
public static DateTime ConvertFromUTC(DateTime utctime, string timezoneid)
{
    if (utctime.Kind == DateTimeKind.Unspecified)
        utctime = DateTime.SpecifyKind( utctime, DateTimeKind.Utc );

    utctime = utctime.ToUniversalTime();

    return TimeZoneInfo.ConvertTimeBySystemTimeZoneId( utctime, timezoneid );
}

The error message I get when attempting to publish is:

Creating [dbo].[ConvertFromUTC]...

(268,1): SQL72014: .Net SqlClient Data Provider:

Msg 6551, Level 16, State 2, Procedure ConvertFromUTC, Line 1
CREATE FUNCTION for "ConvertFromUTC" failed because T-SQL and CLR types for return value do not match.

(268,0): SQL72045: Script execution error

SQL generated from the .net in an attempt to add the function:

CREATE FUNCTION [dbo].[ConvertFromUTC]
    (@utctime DATETIME, @timezoneid NVARCHAR (MAX))
RETURNS TABLE ([localtime] DATETIME2 (7) NULL)
AS EXTERNAL NAME [database].[IntelligentTutor.Database.Functions].[ConvertFromUTC]

SQL definition for the version of the function in the existing database (which confirms that @MattJohnson was right about how it needs fixed):

CREATE FUNCTION [dbo].[ConvertFromUTC]
    (@utctime [datetime], @timezoneid [nvarchar](4000))
RETURNS [datetime] WITH EXECUTE AS CALLER
AS EXTERNAL NAME [database].[IntelligentTutor.Database.Functions].[ConvertFromUTC]
1 Answers
Related