TheEsisia TheEsisia - 3 days ago 6
SQL Question

Why this query with geography data type works in SSMS but doesn't work in SSRS?

I have a query that works well in SSMS but doesn't work in SSRS. Here it is in SSMS:

declare @or geography = 0xE6100000010CAE8BFC28BCE4474067A89189898A5EC0
declare @dest int =1500

select FirstName+' '+LastName As CustomerName, SpatialLocation as CustomerLocation from Person.Address PA
left join Person.BusinessEntityAddress PBA ON PBA.AddressID=PA.AddressID
left join Sales.Customer SC ON SC.CustomerID = PBA.BusinessEntityID
left join Sales.Store SS ON SS.BusinessEntityID=SC.StoreID
left join Person.Person PP ON PP.BusinessEntityID = PBA.BusinessEntityID
WHERE CustomerID IS NOT NULL
AND @dest >= (@or.STDistance(SpatialLocation)*0.62)


And I use the following in SSRS:

select FirstName+' '+LastName As CustomerName, SpatialLocation as CustomerLocation from Person.Address PA
left join Person.BusinessEntityAddress PBA ON PBA.AddressID=PA.AddressID
left join Sales.Customer SC ON SC.CustomerID = PBA.BusinessEntityID
left join Sales.Store SS ON SS.BusinessEntityID=SC.StoreID
left join Person.Person PP ON PP.BusinessEntityID = PBA.BusinessEntityID
WHERE CustomerID IS NOT NULL
AND @dest >= (@or.STDistance(SpatialLocation)*0.62)


I expect it to accept the query and create the two @dest and @or parameters, but it doesn't. I checked the query for cases too. Any ideas please?

Answer

Problem solved! SSRS does not realize a parameter with type geography. So, I first cast the parameter before using it in the STDistance function like this:

@dest >= cast(@or as geography).STDistance(SpatialLocation)*0.62

Thanks for everybody's help!

Comments