x

T-SQL to CLR type mapping issue

I have a simple function, written in VB.NET and have added the assembly to SQL Server 2008 R2 and am trying to create a user-defined function out of it. The error I am getting is:

CREATE FUNCTION for "ufn_Factorial2" failed because T-SQL and CLR types for return value do not match.

I have done a lot of research and found several, different, mappings (which is confusing in itself) and none of the pairings seem to work. Here are the VB code and the T-SQL statement;

VB code

' I did import the System.Data.SQLTypes namespace.
Public Shared Function Factorial(ByVal num As SqlInt16) As SqlInt16

T-SQL code

create function dbo.ufn_Factorial2 (@num smallint) returns smallint
external name dadFunctions.[myFunctions.clsFunctions].Factorial;

Does anybody have suggestions on how I can match up the data types correctly?

Thanks, Dennis

more ▼

asked Oct 20, 2011 at 11:32 AM in Default

DennisDA2003 gravatar image

DennisDA2003
1 1 1 1

(comments are locked)
10|1200 characters needed characters left

1 answer: sort newest

Are you sure, you are referencing correct assembly and correct function in the external name? I mean function with right data types? As from your code, the data types are correct. I mean SqlInt16 is equivalent for smallint which is 2 bytes signed integer.

Remember, that if you have changed the data types in the assembly and recompiled it, you have to redeploy the assembly into the database as the binary representation of the assembly is physically stored directly in the db.

Anyway, isn't smallint data type a little bit small for the Factorial function? (You will be able to calculate maximum 7! - Factorial of 7 as Factorial of 8 is 40320 which is out of smallint range.
more ▼

answered Oct 20, 2011 at 11:58 AM

Pavel Pawlowski gravatar image

Pavel Pawlowski
22.3k 9 11 21

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x985
x19
x7

asked: Oct 20, 2011 at 11:32 AM

Seen: 1371 times

Last Updated: Oct 20, 2011 at 11:32 AM