Am I doing something silly, or is this a bug?

Question

Hi,

I'm trying to learn some internal regarding spatial indexes. Is this a bug in RC0, or am I doing something wrong?

When running the following query:

use AdventureWorks2008R2
declare @qs geography 
select @qs = SpatialLocation from [Person].[Address] where [AddressID] = 1
declare @x xml

exec sp_help_spatial_geography_index_xml'Person.Address', 'SpatialIndex-20120303-124545', 1, @qs, @x

I get the following error:

Msg 208, Level 16, State 1, Procedure sp_help_spatial_geography_index_helper, Line 141

Invalid object name 'Sys.GetGeographyTessellation_VarBinary'.

---

Peter Carter http://sqlserverdownanddirty.blogspot.com/

Answer 1

I can recreate this. My guess is that it's got something to do with the settings or compatability level of the AdventureWorks2008R2 database, although I confess I don't know exactly what.

The same script works in a copy of the address table in tempdb, for example:

USE tempdb;

SELECT * INTO AddressCopy
FROM AdventureWorks2008R2.Person.Address

ALTER TABLE AddressCopy 
ADD CONSTRAINT PK_AddressCopy_AddressID PRIMARY KEY CLUSTERED (AddressID ASC);

CREATE SPATIAL INDEX [SpatialIndex-20120304-194728] ON AddressCopy(SpatialLocation)
USING GEOGRAPHY_GRID;

and then, as before:

DECLARE @qs geography;
SELECT @qs = SpatialLocation from [AdventureWorks2008R2].Person.[Address] where [AddressID] = 1;

EXEC sp_help_spatial_geography_index
'AddressCopy', 'SpatialIndex-20120304-194728',1, @qs

---

twitter: @alastaira blog: http://alastaira.wordpress.com/

Answer 2

Ok cool. Thanks.

---

Peter Carter http://sqlserverdownanddirty.blogspot.com/