Create function from CLR assembly - T-SQL and CLR types for return value do not match

This is on MS SQL Server 2014 but will also be used on 2008 R2.


I am attempting to use the YAddress UDF DLL found here http://ift.tt/1IZ9B29. I have created the assembly on my database and it appears under sys.assemblies.


I have created a stored procedure from the FillRow method, but I need the CallYAddress function.


I am trying to create a function on SQL Server using the EXTERNAL NAME YAddressSqlFunction.YAddressSqlFunction.CallYAddress.


The error returned by



CREATE FUNCTION ProcessAddress2008b(@Addr1 NVARCHAR(255),@Addr2 NVARCHAR(255),@user NVARCHAR(100))
RETURNS table (..here I list all the fields see code below for the list..)
AS EXTERNAL NAME YAddressSqlFunction.YAddressSqlFunction.CallYAddress


is


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


I think the issue is the 'table' return. What should I be returning for the UDF? The assembly fills rows but they aren't coming back as a TABLE. I have tried several things (such as IENUMERABLE) and they return the same error.


Here is the code from the DLL:



Public Shared Function CallYAddress(sAddressLine1 As String, sAddressLine2 As String, sUserKey As String) As YAddressSqlFunction.Address
Dim httpWebRequest As HttpWebRequest = DirectCast(WebRequest.Create(String.Format("http://ift.tt/1D2y6TC}", DirectCast(Uri.EscapeDataString(If(sAddressLine1 Is Nothing, "", sAddressLine1)), Object), DirectCast(Uri.EscapeDataString(If(sAddressLine2 Is Nothing, "", sAddressLine2)), Object), DirectCast(Uri.EscapeDataString(If(sUserKey Is Nothing, "", sUserKey)), Object))), HttpWebRequest)
httpWebRequest.Accept = "application/xml"
Return DirectCast(New XmlSerializer(GetType(YAddressSqlFunction.Address)).Deserialize(httpWebRequest.GetResponse().GetResponseStream()), YAddressSqlFunction.Address)
End Function

<SqlFunction(FillRowMethodName:="FillRow")> _
Public Shared Function InitMethod(AddressLine1 As String, AddressLine2 As String, Optional UserKey As String = Nothing) As IEnumerable
Return DirectCast(New YAddressSqlFunction.YAddressResults(AddressLine1, AddressLine2, UserKey), IEnumerable)
End Function

Public Shared Sub FillRow(obj As Object, ByRef ErrorCode As Integer, ByRef ErrorMessage As String, ByRef AddressLine1 As String, ByRef AddressLine2 As String, _
ByRef Number As String, _
ByRef PreDir As String, ByRef Street As String, ByRef Suffix As String, ByRef PostDir As String, ByRef Sec As String, ByRef SecNumber As String, _
ByRef City As String, ByRef State As String, ByRef Zip As String, ByRef Zip4 As String, ByRef County As String, ByRef CountyFP As String, _
ByRef CensusBlock As String, ByRef CensusTract As String, ByRef Latitude As Double, ByRef Longitude As Double, ByRef GeoPrecision As Integer)

Dim address As YAddressSqlFunction.Address = DirectCast(obj, YAddressSqlFunction.Address)

ErrorCode = address.ErrorCode
ErrorMessage = address.ErrorMessage
AddressLine1 = address.AddressLine1
AddressLine2 = address.AddressLine2
Number = address.Number
PreDir = address.PreDir
Street = address.Street
Suffix = address.Suffix
PostDir = address.PostDir
Sec = address.Sec
SecNumber = address.SecNumber
City = address.City
State = address.State
Zip = address.Zip
Zip4 = address.Zip4
County = address.County
CountyFP = address.CountyFP
CensusBlock = address.CensusBlock
CensusTract = address.CensusTract
Latitude = address.Latitude
Longitude = address.Longitude
GeoPrecision = address.GeoPrecision
End Sub