I'm trying to create a SQL Function by CRL integration, but I'm having the following error:
CREATE FUNCTION for "GetWSClient" failed because T-SQL and CLR types for return value do not match.
I'm trying to consume a WebService in my dll, then integreate it as an Assembly in SQL Server.
My C# Code is:
namespace InternalLists
{
public class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.Read, Name = "WSClient", FillRowMethodName = "Fill_WSClient",
TableDefinition = "Name nvarchar(255)")]
public static DataSet WSClient(SqlString url, SqlString idClient)
{
WSInternalList.CLIENTS client = new WSInternalList.CLIENTS();
client.Url = url.ToString();
return client.WM_CLIENT(idClient.ToString());
}
public static void Fill_WSClient(object ProductObj, out SqlString Name)
{
DataRow row = (DataRow)ProductObj;
Name = new SqlString(row["VWSDN_NAME"].ToString());
}
}
}
My SQL Code is:
CREATE FUNCTION [dbo].[GetWSClient](@url nvarchar(255), @idClient nvarchar(255))
RETURNS TABLE (
Name nvarchar(255) NULL
)
EXTERNAL NAME [InternalLists].[InternalLists.UserDefinedFunctions].[WSClient]
GO
I have seen this answer, but I'm declaring my function as DataSet return type, and I know that my WS is returning a DataSet result.
So, what I'm doing wrong??
You are creating a CLR table-valued function. From the Documentation at http://msdn.microsoft.com/en-us/library/ms131103.aspx, here is a sample:
You need to return an
IEnumerable
orIEnumerable<T>
. The runtime will take care of enumerating over thatIEnumerable
, passing each enumerated object to your Fill Row method (as the first argument,Object obj
in the example above) and take care of mapping that into something that can be squirted down to the client via TDS, where the client, depending on what it is, will convert it into a result set.