Link to home
Start Free TrialLog in
Avatar of raymurphy
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
ASKER CERTIFIED SOLUTION
Avatar of Vikas Garg
Vikas Garg
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I believe you will need Visual Studio 2013 to use this
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of raymurphy
raymurphy

ASKER

No parameters being used in dataset, and have .NET 4.5 framework installed.
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.Value</VectorData>
                </MapSpatialDataRegion>

Would appreciate any thoughts ....
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 ....
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>Geographic</MapCoordinateSystem>
              <MapProjection>Mercator</MapProjection>

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