You've developed a new type in VB or C#, loaded it into SQL using CREATE ASSEMBLY and then tried to use the funky new ability of calling methods on your variables.
declare @s as mytype
select @s.Method1()
However when you try this SQL sqawks with error 6506 saying the method doesn't exist, you've checked your code and it exists, so whats wrong.
Well irrespective of the case sensitivity setting on the server ALL methods and properties of CLR types are case sensitive. I guess this is because at least in C# you can have 2 things named the same but with different case.
This also applies to when you are registering your types and methods. What is interesting though is that once there is a SQL server wrapper round your function, type it is no longer case sensitive.
So for an assembly with a function myFunction, type myType and property x on myType, when creating the function using CREATE FUNCTION you must use the correct type, however calling that SQL function can be any case. Similarly when creating the type using CREATE TYPE you must use the correct case but not when using that type in your TSQL code. Finally the call to the property x on a variable of type myType will always need to be the correct case because there is no SQL wrapper for this.
So the following is a summary,
| SQL |
Valid or Invalid |
CREATE FUNCTION myFunction RETURNS int
AS EXTERNAL NAME myAssembly.myFunction |
Valid |
CREATE FUNCTION myFunction RETURNS int
AS EXTERNAL NAME myAssembly.MYFUNCTION |
InValid |
| SELECT dbo.myFunction() |
Valid |
| SELECT dbo.myFUNCTION() |
Valid |
CREATE TYPE myType RETURNS int
AS EXTERNAL NAME myAssembly.myType |
Valid |
CREATE TYPE myType RETURNS int
AS EXTERNAL NAME myAssembly.MYTYPE |
InValid |
| DECLARE @i myType |
Valid |
| DECLARE @i MYTypE |
Valid |
DECLARE @i myType
SELECT @i.x |
Valid |
DECLARE @i myType
SELECT @i.X |
InValid |
Be aware, good coding practice is to have a standard for naming so I am sure this isn't an issue for anyone.