T-SQL and CLR types for return value do not match

Question

I am trying to create a custom SQL function using CLR integration, but I have a T-SQL and CLR type mismatch error.

DLL:

Imports System Imports System.Data Imports Microsoft.SqlServer.Server Public Class Encrypter_Decrypter <SqlFunction(DataAccess:=DataAccessKind.Read)> _ Public Shared Function EncryptString(ByVal strItem As String) Dim strPassPhrase As String = "***********" Dim strInitVector As String = "***********" Dim objEncryption = New PCI.Encryption() objEncryption.Initialise(strPassPhrase, strInitVector, -1, -1, -1, "", "", 1) Dim EncryptedString As String = objEncryption.Encrypt(strItem) objEncryption = Nothing EncryptString = EncryptedString End Function End Class 

Creating an SQL Function:

 CREATE ASSEMBLY EncrypterDecrypter FROM 'c:\dll\Encrypter_Decrypter\Encrypter_Decrypter.dll' WITH PERMISSION_SET = SAFE GO CREATE FUNCTION EncryptString(@strItem NVARCHAR(MAX)) RETURNS NVARCHAR(MAX) AS EXTERNAL NAME EncrypterDecrypter.Encrypter_Decrypter.EncryptString; GO 

Error:

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

Does anyone know what I am missing?

Thanks:)

Answer 1

You are missing As String from the end of your function definition:

 Public Shared Function EncryptString(ByVal strItem As String) As String 

And, obviously, Option Explicit or Option Strict are not included (any of which will warn you about this problem). By default, the return type of an Object function that does not have a mapping to the SQL Server data type.

---

Not knowing what your encryption package does, usually also a bad plan for storing the encrypted version as a string (if your package does not automatically perform, for example, Base64 encoding?)

Answer 2

Are you missing the return type for the function?