How do I schema bind a function that uses geography or Json in SSDT?

Question

I have a table valued function that I'd like to add to my ssdt project.

create function dbo.fn_get_n_geos(@p0 nvarchar(max),@n bigint)
returns table
with schemabinding as
return 
select top(@n) geo=geography::Point(latitude,longitude,4326)
              ,row=-1 + convert(int,row_number() over (order by (select 1)))
from openjson(@p0)
with (latitude float 'strict $[0]', longitude float 'strict $[1]');

When I add this function I get the following warnings::

SQL70561: Cannot schema bind function 'dbo.fn_get_n_geos' because name 'geography' is invalid for schema binding. Names must be in two-part format and an object cannot reference itself.

SQL70561: Cannot schema bind function 'dbo.fn_get_n_geos' because name 'float' is invalid for schema binding. Names must be in two-part format and an object cannot reference itself.

When I execute this function in SQL management studio everything works correctly. How do I get SSDT to play ball with these features?

My project is targeting SQL Server 2016.

Bannerman

Answer 1

TL;DR: Try qualifying names with sys.

In your case, try changing geography to sys.geography; and you might also have to do the same with the float columns in your OPENJSON call, too.

---

Just came across exactly the same problem but with an OPENJSON with explicit nvarchar column mappings, e.g:

OPENJSON(@query) 
WITH
(
  axis nvarchar(64) '$.axis',
  value nvarchar(max) '$.value' AS JSON
)

I was getting the errors on the axis and value lines.

It appears to be a bug in SSDT - reported by John.Nelson on the Developer Community portal here.

I was all-set to suggest the ugly fall back to post-deploy, and then I stumbled across this over on Dba Stackexchange (I searched for the error code SQL70561) where the OP is getting the same error in a view that uses hierarchyid.

The solution there was to qualify hierarchyid to sys.hierarchyid.

I have never bothered qualifying built-in SQL type names - but, sure enough, once I changed my OPENJSON call to:

OPENJSON(@query) 
WITH
(
  axis sys.nvarchar(64) '$.axis',
  value sys.nvarchar(max) '$.value' AS JSON
)

That worked for me.

Byelaw

Inexplicably this worked. Noone would probably do this except to work around the bug. – Bannerman