SQL Server 2008 GEOMETRY.AsGml() In Views With Joins

Question

Hello,

I have a table that contains a GEOMETRY data type. SQL Server 2008 ships with a built in function to convert these GEOMETRY data types to GML - GEOMETRY.AsGml(). I believe this function is nothing more than a custom user defined function.

This function works exactly as expected, until I try to use it in a view that is joined to other tables/views. In that case, I get an error message along the lines "Remote function reference 'dbo.PROPERTY.SHAPE.AsGml' is not allowed, and the column name 'dbo' could not be found or is ambiguous."

What I have been doing is creating an initial view that contains all of the joins needed to get the desired fields, leaving the GEOMETRY field in its native format. Then, in a secondary view, I will perform the GML conversion.

The layering of these views has obvious performance implications, and I am wondering why I can't just do the AsGml() in the views with joins?

Answer 1

Using an inline Select statement solved this for me.

This didn't work:

SELECT dbo.H1N1_2009.COUNTY, dbo.states.STATE_NAME, dbo.states.geom.AsGml() 
AS GML
FROM dbo.H1N1_2009 INNER JOIN dbo.states ON dbo.H1N1_2009.ID = dbo.states.ID

This works:

SELECT     dbo.H1N1_2009.COUNTY, states_1.STATE_NAME,
           (SELECT geom.AsGml() AS Expr1 FROM dbo.states WHERE(ID =dbo.H1N1_2009.ID))  AS GML
FROM       dbo.H1N1_2009 INNER JOIN dbo.states AS states_1 ON dbo.H1N1_2009.ID states_1.ID

Hope this helps someone else.