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

Question

Post reply

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

Marcus Farrugia

SSCarpal Tunnel

Points: 4718
More actions
March 20, 2015 at 6:42 am

#295271

Hi I am trying to create a CLR function to call a webservice, the CLR function return data type is double, whether I try to create this as a table valued funcion or a scalar to return a distance travelled value I am receiving the error below. I've tried changing data types around in the CLR side and the SQL side but keep receiving the same error message, any help would be appreciated,
Thank you,
[Microsoft.SqlServer.Server.SqlFunction(Name = "DistanceCalc")]
public static Double DistanceCalc(Double SrcLat, Double SrcLong,
Double DestLat, Double DestLong)
{
MileageWS ws = new MileageWS();
ws.Url = "http://test.isp.ca/Distance.asmx";
int intUom = 0; // 0 = Mile, 1 = KM
RouteType RouteMethod = RouteType.Practical;
Requester RequestedFrom = Requester.LinkRoute;
Double distance;
distance = ws.GetDistanceInfoForLonLat(SrcLat, SrcLong, DestLat, DestLong, intUom, RouteMethod, RequestedFrom);
return distance;
}
CREATE FUNCTION DistanceCalc
(
@SrcLat as float, @SrcLong as float,
@DestLat as float, @DestLong as float
)
RETURNS TABLE (Distance float)
External NAME CLRfunctions.RIFunctions.DistanceCalc
GO
Error received when try to Create function ...
Msg 6551, Level 16, State 2, Procedure pcMiler, Line 6
CREATE FUNCTION for "pcMiler" failed because T-SQL and CLR types for return value do not match.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Alan Burstein SSC Guru Points: 61136 More actions · Answer 1

You're returning a Table Valued Function so you need to return a IEnumerable (Note: CLR Table-Valued Functions)

I have not written a CLR in a while but I think that

public static Double DistanceCalc(...)

Should be

public static IEnumerable DistanceCalc(...)

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Lowell SSC Guru Points: 323504 More actions · Answer 2

i didn't compile this or test it , but it looks right to me;

what if you return SQL values? SQLDouble will implicitly convert to Double, i think?

you might have to explicitly convert SrcLat, SrcLong, DestLat, DestLong to Double to pass to teh function, but i think using SQL data types for in and out parameteters will get you there

[Microsoft.SqlServer.Server.SqlFunction(Name = "DistanceCalc")]

public static SqlDouble DistanceCalc(SqlDouble SrcLat, SqlDouble SrcLong,

SqlDouble DestLat, SqlDouble DestLong)

{

MileageWS ws = new MileageWS();

ws.Url = "http://test.isp.ca/Distance.asmx";

int intUom = 0; // 0 = Mile, 1 = KM

RouteType RouteMethod = RouteType.Practical;

Requester RequestedFrom = Requester.LinkRoute;

Double distance;

distance = ws.GetDistanceInfoForLonLat(SrcLat, SrcLong, DestLat, DestLong, intUom, RouteMethod, RequestedFrom);

return new SqlDouble(distance)

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Jason-299789 SSC-Insane Points: 21601 More actions · Answer 3

Can you not use the built in Geography STDistance method?

https://msdn.microsoft.com/en-gb/library/bb933808.aspx

_________________________________________________________________________
SSC Guide to Posting and Best Practices

Marcus Farrugia SSCarpal Tunnel Points: 4718 More actions · Answer 4

hi Babe, I ended up getting it working by creating a scalar function, since it was only returning one number anyway, thanks for the reference to iEnumerable. Definitely going to be useful sometime soon.

Marcus Farrugia SSCarpal Tunnel Points: 4718 More actions · Answer 5

Hi Lowell, I had tried the SQLDouble and was still receiving the data type conflict error. Creating a scalar function worked for me. But I think Babe may have been on to something with returning ienumerable, still have to test that solution.

Marcus Farrugia SSCarpal Tunnel Points: 4718 More actions · Answer 6

thanks for the suggestion jason, I'm calling a web service in my clr that takes long and lat coords and give back distance 'route' options, rather than an as the crow flies distance...but I wasn't aware of this data type. Will be in my back pocket.

Utrolig Valued Member Points: 61 More actions · Answer 7

I think this may be exactly what I've been looking for. Is it creating a SQL Server CLR function that calls the equivalent of the PC Miler miles() function in Excel? Did the described changes solve the issue? Also, what is the url for? As in, what would I need to use in order to make this work for me?

Sorry so many questions... I'm new to CLRs.