raymurphy
asked on
SQL Server Spatial Error in Visual Studio 2010
In summary, I get an error in Visual Studio 2010, trying to design an SSRS report with a Map - I am getting the following error :
Unable to determine the spatial data type in the specified dataset field : geom
So that's the summary, and here is the background detail ...
Have the following table structure in SQL Server 2012 :
WorldMap
[NAME] [nvarchar](255),
[LON] [float],
[LAT] [float],
[geom] [geometry]
The data in WorldMap has been populated by importing a shape file, and the data looks OK.
SELECT
NAME, LAT, LON, geom
FROM WorldMap
In SSMS, executing the above query shows the World Map in the Spatial Results tab.
In SSMS, running the same query shows data in the standard Results tab (geom column contents have been deliberately truncated for clarity)
NAME LON LAT geom
Antigua and Barbuda 17.078 -61.783 0x00000000010436
Algeria 28.163 2.632 0x000000000104D9
Azerbaijan 40.43 47.395 0x00000000010467
Albania 41.143 20.068 0x00000000010451
For the geom column running the following query shows the geom value in readable format
SELECT
NAME, LAT, LON,
geom.STAsText() AS WKT
FROM WorldMap
NAME LON LAT WKT
Antigua and Barbuda 17.078 -61.783 MULTIPOLYGON (((-61.686668 17.024441000000138,
Algeria 28.163 2.632 POLYGON ((2.96361 36.802216,
Azerbaijan 40.43 47.395 MULTIPOLYGON (((45.083321000000012 39.768044000000145,
Albania 41.143 20.068 POLYGON ((19.436214 41.021065, 19.461109 41.006943,
So the data in WorldMap looks OK, and shows the expected map in Spatial Results tab in SSMS, and the spatial data column is geom with a data type of geometry.
But have hit a problem when trying to develop an SSRS report with a map in Visual Studio 2010 :
Have set up required VS2010 project;
Have set up a report in VS2010 (not via the Report Wizard)
Against the Report, have set up a DataSource with appropriate credentials for WorldMap table
Against the Report, have set up a Dataset containing SELECT NAME, LAT, LON, geom FROM WorldMap
When trying to add a Map from SSRS Toolbox onto the Report, on the New Map Layer dialog, I select SQL Server Spatial query for Choose a source of spatial data, and then select the 'Choose an existing dataset with SQL Server data in this report' option and specify the Dataset referred to above.
But then the Report Designer gives the following error
Unable to determine the spatial data type in the specified dataset field : geom
Can't see why this error has occurred - any ideas, please ?
Thanks
Ray
Unable to determine the spatial data type in the specified dataset field : geom
So that's the summary, and here is the background detail ...
Have the following table structure in SQL Server 2012 :
WorldMap
[NAME] [nvarchar](255),
[LON] [float],
[LAT] [float],
[geom] [geometry]
The data in WorldMap has been populated by importing a shape file, and the data looks OK.
SELECT
NAME, LAT, LON, geom
FROM WorldMap
In SSMS, executing the above query shows the World Map in the Spatial Results tab.
In SSMS, running the same query shows data in the standard Results tab (geom column contents have been deliberately truncated for clarity)
NAME LON LAT geom
Antigua and Barbuda 17.078 -61.783 0x00000000010436
Algeria 28.163 2.632 0x000000000104D9
Azerbaijan 40.43 47.395 0x00000000010467
Albania 41.143 20.068 0x00000000010451
For the geom column running the following query shows the geom value in readable format
SELECT
NAME, LAT, LON,
geom.STAsText() AS WKT
FROM WorldMap
NAME LON LAT WKT
Antigua and Barbuda 17.078 -61.783 MULTIPOLYGON (((-61.686668 17.024441000000138,
Algeria 28.163 2.632 POLYGON ((2.96361 36.802216,
Azerbaijan 40.43 47.395 MULTIPOLYGON (((45.083321000000012 39.768044000000145,
Albania 41.143 20.068 POLYGON ((19.436214 41.021065, 19.461109 41.006943,
So the data in WorldMap looks OK, and shows the expected map in Spatial Results tab in SSMS, and the spatial data column is geom with a data type of geometry.
But have hit a problem when trying to develop an SSRS report with a map in Visual Studio 2010 :
Have set up required VS2010 project;
Have set up a report in VS2010 (not via the Report Wizard)
Against the Report, have set up a DataSource with appropriate credentials for WorldMap table
Against the Report, have set up a Dataset containing SELECT NAME, LAT, LON, geom FROM WorldMap
When trying to add a Map from SSRS Toolbox onto the Report, on the New Map Layer dialog, I select SQL Server Spatial query for Choose a source of spatial data, and then select the 'Choose an existing dataset with SQL Server data in this report' option and specify the Dataset referred to above.
But then the Report Designer gives the following error
Unable to determine the spatial data type in the specified dataset field : geom
Can't see why this error has occurred - any ideas, please ?
Thanks
Ray
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I believe you will need Visual Studio 2013 to use this
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
No parameters being used in dataset, and have .NET 4.5 framework installed.
ASKER
Sorry for the delay in updating this question, got seconded onto another project for a few days.
Having now come back to this, I've noticed something odd. On my previous attempt, when the Report Designer gave the 'Unable to determine the spatial data type in the specified dataset field : geom' error, I just quit out of the New Map Layer wizard and deleted the map.
However, this time round, I've actually continued with the New Map Layer wizard after getting the 'Unable to determine the spatial data type' error. Having done that, the expected Map (which should show European countries) does NOT appear in the map viewport on Report Designer. But if I then build the project, and then use Preview in Visual Studio 2010 (or alternatively use 'Debug..Start Debugging') then the Map DOES show correctly in the map viewport - so at runtime it appears that the Map is actually being produced as expected.
So I am now wondering - is this just an issue with the Report Designer rather than being a complete showstopper ? Interestingly, if I look at the rdl code for the report, I can see the following lines :
<MapSpatialDataRegion>
<VectorData>=Fields!geom.V alue</Vect orData>
</MapSpatialDataRegion>
Would appreciate any thoughts ....
Having now come back to this, I've noticed something odd. On my previous attempt, when the Report Designer gave the 'Unable to determine the spatial data type in the specified dataset field : geom' error, I just quit out of the New Map Layer wizard and deleted the map.
However, this time round, I've actually continued with the New Map Layer wizard after getting the 'Unable to determine the spatial data type' error. Having done that, the expected Map (which should show European countries) does NOT appear in the map viewport on Report Designer. But if I then build the project, and then use Preview in Visual Studio 2010 (or alternatively use 'Debug..Start Debugging') then the Map DOES show correctly in the map viewport - so at runtime it appears that the Map is actually being produced as expected.
So I am now wondering - is this just an issue with the Report Designer rather than being a complete showstopper ? Interestingly, if I look at the rdl code for the report, I can see the following lines :
<MapSpatialDataRegion>
<VectorData>=Fields!geom.V
</MapSpatialDataRegion>
Would appreciate any thoughts ....
ASKER
Additionally, I've just gone through this same process on another development laptop, also using SQL Server 2012 with Visual Studio 2010 with Net Framework 4.5 installed (so essentially the same development configuration) and everything worked fine without this issue occurring in Report Designer.
I'll probably do a closer comparison of these two development platforms to pinpoint any differences but this issue might well just remain a mystery ....
I'll probably do a closer comparison of these two development platforms to pinpoint any differences but this issue might well just remain a mystery ....
ASKER
Just a quick follow-up in case anyone encounters the same problem ....
When I compared the ‘.rdl’ Report Definition files between my 'good' development platform (which DID NOT exhibit this obscure Report Designer ‘Unable to determine the spatial data type’ error) and my 'bad' development platform (which DID exhibit this obscure Report Designer ‘Unable to determine the spatial data type’ error), there was only really one significant difference.
The good version contained two tags under <MapViewport> which were missing from the bad version :
<MapCoordinateSystem>Geogr aphic</Map Coordinate System>
<MapProjection>Mercator</M apProjecti on>
Once I had pasted those two differing lines into the bad version, and then reopened the Visual Studio 2010 project on my 'bad' development platform then at that point the expected European map DID display correctly in the Report Designer .....
Additionally, since doing that and creating new Reporting Services projects on my 'bad' development platform, every project has subsequently always correctly displayed the expected map in Report Designer without the original ‘Unable to determine the spatial data type’ error occurring.
Wouldn't even pretend to know why/how/if this has specifically resolved the issue, but in my case it does seem to have done the trick ....
When I compared the ‘.rdl’ Report Definition files between my 'good' development platform (which DID NOT exhibit this obscure Report Designer ‘Unable to determine the spatial data type’ error) and my 'bad' development platform (which DID exhibit this obscure Report Designer ‘Unable to determine the spatial data type’ error), there was only really one significant difference.
The good version contained two tags under <MapViewport> which were missing from the bad version :
<MapCoordinateSystem>Geogr
<MapProjection>Mercator</M
Once I had pasted those two differing lines into the bad version, and then reopened the Visual Studio 2010 project on my 'bad' development platform then at that point the expected European map DID display correctly in the Report Designer .....
Additionally, since doing that and creating new Reporting Services projects on my 'bad' development platform, every project has subsequently always correctly displayed the expected map in Report Designer without the original ‘Unable to determine the spatial data type’ error occurring.
Wouldn't even pretend to know why/how/if this has specifically resolved the issue, but in my case it does seem to have done the trick ....