Hi,
This is in regard to Version 5.5 services edtion.
I've noticed that the SQL generated to find out the spatial data type of the column of interest for a view or table for SQL Server 2008 is not actually correct (when the table/view is in another schema). In our application the GIS data is kept in a GIS schema for eg with a feature layer defined as:
var worldLayer = new MsSql2008FeatureLayer()
{
ConnectionString = @"Data Source=DB;Initial Catalog=GISDATA;Integrated Security=SSPI;MultipleActiveResultSets=true;",
CustomGeometryColumnName = "Extent",
IsVisible = true,
Srid = 4326,
TableName = @"GIS.vwData"
};
When rendering etc The SQL that is generated is this:
SELECT
allTypes.name
FROM
sys.columns allColumns,
sys.types allTypes,
sys.all_views allViews
WHERE
allColumns.object_id=allViews.object_id AND
allColumns.user_type_id=allTypes.user_type_id AND
allTypes.system_type_id=240 AND
allViews.name='GIS.vwData'
union
SELECT
allTypes.name
FROM
sys.columns allColumns,
sys.tables allTables,
sys.types allTypes
WHERE
allColumns.object_id=allTables.object_id AND
allColumns.user_type_id=allTypes.user_type_id AND
allTypes.system_type_id=240 AND allTables.name='GIS.vwData'
This won't find the column as the schema name is not included in the sys.tables.name field (or in our case the sys.all_views.name). It doesn't seem to affect anything as I'm guessing it defaults to geometry (which is what we are using) if it doesn't find anything.
Am I doing something wrong in the feature layer setup or is this a bug? Also out of interest what would happen if there were two spatial columns in the table one geog and one geom (we don't do this nor have any plan to)?
Thanks,
Neil.