CLR function return type mismatch

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

CLR function return type mismatch

Author

Topic

jeffnc
Starting Member

14 Posts

Posted - 2008-11-03 : 15:36:16

Getting error msg "CREATE FUNCTION for "LocalToUTC" failed because T-SQL and CLR types for return value do not match"

This is a first attempt at this, not final code. This was working at one time, then stopped working (which is legitimate, I'm sure I changed something incorrectly.) Now I change it back to the way it was and I can't escape this error.

C# code developed in VisualStudio is

public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString LocalToUTC( )
{
// Put your code here
return new SqlString("Hello");
}
};

It builds (I'm not sure of the role of Deploying - it might be redundant with the following SQL code.)

SQL code is

CREATE ASSEMBLY DateTimeUtilities
FROM 'C:\...\bin\Debug\DateTimeUtilities.dll'

CREATE FUNCTION LocalToUTC
(
@dt DateTime
)
RETURNS varchar(10)
AS EXTERNAL NAME DateTimeUtilities.UserDefinedFunctions.LocalToUTC

This fails with above message. (The @dt DateTime parameter is not important right now, I left it in there simply because it was there the first time I got this to work.)

Again, it worked first time I tried it, then started to fail as I made changes, but I can't get it back to the original working state. It must be some combination of building the DLL correctly in VisualStudio and getting it updated correctly in SQL? I've done DROPs for the assembly and function.

Jatsie
Starting Member

15 Posts

Posted - 2008-11-09 : 02:40:04

I found this:

I had this same problem. The problem was that I had enabled signing the assembly with a .snk key.

For debuging purposes, uncheck this option (in the project properties under 'Signing' tab) and see if you still get the error

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources