Search code examples
jsonsql-serversql-server-data-tools

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

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.

Solution

  • 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.