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
raymurphyAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vikas GargAssociate Principal EngineerCommented:
Hi,



Are you using a parameter in the where clause of your dataset?

You are are getting this error because the mapping engine is trying to sample/test run your dataset and is tripping over the parameter. Try mapping your dataset without a parameter; if it works, your issue was in the parameter, not the datatypes.

Rework your query to pull all the rows you might need, and use a filter on the dataset

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Lee SavidgeCommented:
I believe you will need Visual Studio 2013 to use this
Lee SavidgeCommented:
Actually, as long as .NET 4.5 is installed you should be fine. What framework are you compiling against?
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

raymurphyAuthor Commented:
No parameters being used in dataset, and have .NET 4.5 framework installed.
raymurphyAuthor Commented:
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 ....
raymurphyAuthor Commented:
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 ....
raymurphyAuthor Commented:
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 ....
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.