Unexpected Null Value

I am using an eval version of your product, preparing to purchase it for my company. I am trying out the source control capability but I am having an issue with a particular database being added. As soon as I commit to source control I start getting the unexpected null value error.

This is a geospatially enabled database with computed properties. It is the central database to our entire project so it is rather complex. I'm not sure what about it is triggering this error though nor how to resolve. I copied the sql script for it below.

We are very interested in source control, so would like to get this to work.

Looking at the error message it looks like it is crashing on the spatial index compare

object reference not set to an instance of an object. at redgate.sqlcompare.engine.spatialindex.#am(string #lwpb)


[dbo].[Boundary]
crescodev\sql2012 > DEV_MesoFarm > Tables > dbo.Boundary
Edit...Edit...
Properties Columns Indexes Statistics Spatial Indexes Triggers SQL Script Uses Used By
Properties
Property Value
Collation SQL_Latin1_General_CP1_CI_AS
Row Count Available in generated documentation only
Created 12:32:30 PM Monday, September 09, 2013
Last Modified 12:40:15 PM Monday, September 09, 2013

Columns
Name Data Type Persisted Computed Max Length (Bytes) Allow Nulls Identity Default Description
BoundaryId bigint 8 1 - 1 Edit...Edit...
GeoBoundary geography max Edit...Edit...
Centroid geography max Edit...Edit...
ExternalKey varchar(50) 50 Edit...Edit...
CreatedDate smalldatetime 4 Edit...Edit...
Active bit 1 Edit...Edit...
BoundaryKey uniqueidentifier 16 (newid()) Edit...Edit...
CentroidLat float ([Centroid].[Lat]) 8 Edit...Edit...
CentroidLon float ([Centroid].[Long]) 8 Edit...Edit...
CentroidLatRad float (radians([Centroid].[Lat])) 8 Edit...Edit...
CentroidLonRad float (radians([Centroid].[Long])) 8 Edit...Edit...
CentroidLatCos float (cos(radians([Centroid].[Lat]))) 8 Edit...Edit...
CountyId bigint 8 Edit...Edit...
MapImage image max Edit...Edit...
BoundingBox geography ([geography]::STGeomFromWKB([geometry]::STGeomFromWKB([GeoBoundary].[STAsBinary](),(4326)).MakeValid().STEnvelope().STAsBinary(),(4326))) max Edit...Edit...
BoundingMinLat float 8 ((0)) Edit...Edit...
BoundingMinLong float 8 ((0)) Edit...Edit...
BoundingMaxLat float 8 ((0)) Edit...Edit...
BoundingMaxLong float 8 ((0)) Edit...Edit...
Acreage decimal(18,4) 9 ((0)) Edit...Edit...
SectionId bigint 8 Edit...Edit...

Indexes
Name Columns Type Unique Description
PK_Boundary BoundaryId Edit...Edit...
IX_Boundary BoundaryKey Edit...Edit...
IX_Boundary_LatLon CentroidLat, CentroidLon, Active, CentroidLatRad, CentroidLonRad, CentroidLatCos, BoundaryId Edit...Edit...
idx_BoundaryWithGeoBoundary GeoBoundary, BoundaryId Edit...Edit...
idx_Bounding BoundaryId, Active, BoundingMinLat, BoundingMinLong, BoundingMaxLat, BoundingMaxLong, Acreage Edit...Edit...
IX_Boundary_1 BoundaryId, CountyId Edit...Edit...
IX_Boundary_Bounding_Coords Acreage, BoundingMinLat, BoundingMinLong, BoundingMaxLat, BoundingMaxLong Edit...Edit...
BoundingBoxSpatial BoundingBox spatial Edit...Edit...
CentroidSpatial Centroid spatial Edit...Edit...
GeoBoundarySpatial GeoBoundary spatial Edit...Edit...

Statistics
Name Columns
_dta_stat_1966630049_1_24_25_26_27 BoundaryId, BoundingMinLat, BoundingMinLong, BoundingMaxLat, BoundingMaxLong
_dta_stat_1966630049_1_24_28_6 BoundaryId, BoundingMinLat, Acreage, Active
_dta_stat_1966630049_1_27_24_28_26_25 BoundaryId, BoundingMaxLong, BoundingMinLat, Acreage, BoundingMaxLat, BoundingMinLong
_dta_stat_1966630049_1_27_24_28_26_6 BoundaryId, BoundingMaxLong, BoundingMinLat, Acreage, BoundingMaxLat, Active
_dta_stat_1966630049_1_6_24_25_26_27_28 BoundingMaxLong, Acreage, BoundaryId, Active, BoundingMinLat, BoundingMinLong, BoundingMaxLat
_dta_stat_1966630049_24_25_26_27_28_6 BoundingMinLat, BoundingMinLong, BoundingMaxLat, BoundingMaxLong, Acreage, Active
_dta_stat_1966630049_24_25_28_6 BoundingMinLat, BoundingMinLong, Acreage, Active
_dta_stat_1966630049_24_28_25_1_26 BoundingMinLat, Acreage, BoundingMinLong, BoundaryId, BoundingMaxLat
_dta_stat_1966630049_25_6 BoundingMinLong, Active
_dta_stat_1966630049_28_24_25_1_6 BoundingMinLong, BoundaryId, Active, Acreage, BoundingMinLat
_dta_stat_1966630049_28_24_25_26 Acreage, BoundingMinLat, BoundingMinLong, BoundingMaxLat
_dta_stat_1966630049_28_6_24 Acreage, Active, BoundingMinLat

Spatial Indexes
Name Grids Cells Per Object
BoundingBoxSpatial level_1=MEDIUM, level_2=MEDIUM, level_3=MEDIUM, level_4=MEDIUM 16
CentroidSpatial level_1=MEDIUM, level_2=MEDIUM, level_3=MEDIUM, level_4=MEDIUM 16
GeoBoundarySpatial level_1=HIGH, level_2=HIGH, level_3=HIGH, level_4=HIGH 128

Triggers
Name ANSI Nulls On Quoted Identifier On On Description
trgInsertBoundary After Insert Edit...Edit...
trgUpdateBoundary After Update Edit...Edit...

SQL Script
CREATE TABLE [dbo].[Boundary]
(
[BoundaryId] [bigint] NOT NULL IDENTITY(1, 1),
[GeoBoundary] [sys].[geography] NOT NULL,
[Centroid] [sys].[geography] NULL,
[ExternalKey] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[CreatedDate] [smalldatetime] NOT NULL,
[Active] [bit] NOT NULL,
[BoundaryKey] [uniqueidentifier] NOT NULL CONSTRAINT [DF_Boundary_BoundaryGuid] DEFAULT (newid()),
[CentroidLat] AS ([Centroid].[Lat]) PERSISTED,
[CentroidLon] AS ([Centroid].[Long]) PERSISTED,
[CentroidLatRad] AS (radians([Centroid].[Lat])) PERSISTED,
[CentroidLonRad] AS (radians([Centroid].[Long])) PERSISTED,
[CentroidLatCos] AS (cos(radians([Centroid].[Lat]))) PERSISTED,
[CountyId] [bigint] NULL,
[MapImage] [image] NULL,
[BoundingBox] AS ([geography]::STGeomFromWKB([geometry]::STGeomFromWKB([GeoBoundary].[STAsBinary](),(4326)).MakeValid().STEnvelope().STAsBinary(),(4326))) PERSISTED,
[BoundingMinLat] [float] NOT NULL CONSTRAINT [DF_Boundary_MinLat] DEFAULT ((0)),
[BoundingMinLong] [float] NOT NULL CONSTRAINT [DF_Boundary_MinLong] DEFAULT ((0)),
[BoundingMaxLat] [float] NOT NULL CONSTRAINT [DF_Boundary_MaxLat] DEFAULT ((0)),
[BoundingMaxLong] [float] NOT NULL CONSTRAINT [DF_Boundary_MaxLong] DEFAULT ((0)),
[Acreage] [decimal] (18, 4) NOT NULL CONSTRAINT [DF_Boundary_Acreage] DEFAULT ((0)),
[SectionId] [bigint] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE TRIGGER [dbo].[trgInsertBoundary] On [dbo].[Boundary] After Insert
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for trigger here
Update
Boundary
Set
GeoBoundary = IsNull(GS.SimplifiedGeog, i.GeoBoundary),
BoundingMinLat = IsNull(i.BoundingBox.STPointN(1).Lat, 0),
BoundingMinLong = IsNull(i.BoundingBox.STPointN(1).Long,0),
BoundingMaxLat = IsNull(i.BoundingBox.STPointN(3).Lat,0),
BoundingMaxLong = IsNull(i.BoundingBox.STPointN(3).Long,0),
Acreage = Cast(Round(i.GeoBoundary.STArea() / 4035.87, 2) as decimal(18,4))
From
Boundary B
Join inserted i On B.BoundaryId = I.BoundaryId
Cross Apply [dbo].[GeographySimplifyCA](i.GeoBoundary, .5, 100) GS

Delete From BoundaryGrid Where BoundaryId In (Select BoundaryId From Inserted)

--Delete From BoundaryLines Where BoundaryId In (Select BoundaryId From Inserted)

--Insert Into
-- BoundaryLines
--Select Distinct
-- I.BoundaryId,
-- SP.Lat1,
-- SP.Lat2,
-- SP.Long1,
-- SP.Long2
--From
-- Inserted I
-- Join Boundary B On I.BoundaryId = B.BoundaryId
-- Cross Apply dbo.SplitBoundaryToLines(I.BoundaryId, B.GeoBoundary) SP
--Where
-- B.Acreage < 1000
END
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE TRIGGER [dbo].[trgUpdateBoundary] On [dbo].[Boundary] After Update
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for trigger here
If Update(GEOBoundary) Begin
Update
Boundary
Set
GeoBoundary = IsNull(GS.SimplifiedGeog, i.GeoBoundary),
BoundingMinLat = IsNull(i.BoundingBox.STPointN(1).Lat, 0),
BoundingMinLong = IsNull(i.BoundingBox.STPointN(1).Long,0),
BoundingMaxLat = IsNull(i.BoundingBox.STPointN(3).Lat,0),
BoundingMaxLong = IsNull(i.BoundingBox.STPointN(3).Long,0),
Acreage = Cast(Round(i.GeoBoundary.STArea() / 4035.87, 2) as decimal(18,4))
From
Boundary B
Join inserted i On B.BoundaryId = I.BoundaryId
Cross Apply [dbo].[GeographySimplifyCA](i.GeoBoundary, .5, 100) GS


Delete From BoundaryGrid Where BoundaryId In (Select BoundaryId From Inserted)

--Delete From BoundaryLines Where BoundaryId In (Select BoundaryId From Inserted)

--Insert Into
-- BoundaryLines
--Select Distinct
-- I.BoundaryId,
-- SP.Lat1,
-- SP.Lat2,
-- SP.Long1,
-- SP.Long2
--From
-- Inserted I
-- Join Boundary B On I.BoundaryId = B.BoundaryId
-- Cross Apply dbo.SplitBoundaryToLines(I.BoundaryId, B.GeoBoundary) SP
--Where
-- B.Acreage < 1000
End

END
GO
ALTER TABLE [dbo].[Boundary] ADD CONSTRAINT [PK_Boundary] PRIMARY KEY CLUSTERED ([BoundaryId]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [idx_Bounding] ON [dbo].[Boundary] ([Active], [BoundingMinLat], [BoundingMinLong], [BoundingMaxLat], [BoundingMaxLong], [Acreage]) INCLUDE ([BoundaryId]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [idx_BoundaryWithGeoBoundary] ON [dbo].[Boundary] ([BoundaryId]) INCLUDE ([GeoBoundary]) ON [PRIMARY]
GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_Boundary] ON [dbo].[Boundary] ([BoundaryKey]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_Boundary_Bounding_Coords] ON [dbo].[Boundary] ([BoundingMinLat], [BoundingMinLong], [BoundingMaxLat], [BoundingMaxLong]) INCLUDE ([Acreage]) ON [PRIMARY]
GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_Boundary_LatLon] ON [dbo].[Boundary] ([CentroidLat], [CentroidLon], [Active], [CentroidLatRad], [CentroidLonRad], [CentroidLatCos], [BoundaryId]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_Boundary_1] ON [dbo].[Boundary] ([CountyId]) INCLUDE ([BoundaryId]) ON [PRIMARY]
GO
CREATE SPATIAL INDEX [BoundingBoxSpatial] ON [dbo].[Boundary] ([BoundingBox]) WITH (GRIDS = (MEDIUM, MEDIUM, MEDIUM, MEDIUM), CELLS_PER_OBJECT = 16) ON [PRIMARY]
GO
CREATE SPATIAL INDEX [CentroidSpatial] ON [dbo].[Boundary] ([Centroid]) WITH (GRIDS = (MEDIUM, MEDIUM, MEDIUM, MEDIUM), CELLS_PER_OBJECT = 16) ON [PRIMARY]
GO
CREATE SPATIAL INDEX [GeoBoundarySpatial] ON [dbo].[Boundary] ([GeoBoundary]) WITH (GRIDS = (HIGH, HIGH, HIGH, HIGH), CELLS_PER_OBJECT = 128) ON [PRIMARY]
GO
CREATE STATISTICS [_dta_stat_1966630049_28_6_24] ON [dbo].[Boundary] ([Acreage], [Active], [BoundingMinLat])
GO
CREATE STATISTICS [_dta_stat_1966630049_28_24_25_26] ON [dbo].[Boundary] ([Acreage], [BoundingMinLat], [BoundingMinLong], [BoundingMaxLat])
GO
CREATE STATISTICS [_dta_stat_1966630049_1_27_24_28_26_6] ON [dbo].[Boundary] ([BoundaryId], [BoundingMaxLong], [BoundingMinLat], [Acreage], [BoundingMaxLat], [Active])
GO
CREATE STATISTICS [_dta_stat_1966630049_1_27_24_28_26_25] ON [dbo].[Boundary] ([BoundaryId], [BoundingMaxLong], [BoundingMinLat], [Acreage], [BoundingMaxLat], [BoundingMinLong])
GO
CREATE STATISTICS [_dta_stat_1966630049_1_24_28_6] ON [dbo].[Boundary] ([BoundaryId], [BoundingMinLat], [Acreage], [Active])
GO
CREATE STATISTICS [_dta_stat_1966630049_1_24_25_26_27] ON [dbo].[Boundary] ([BoundaryId], [BoundingMinLat], [BoundingMinLong], [BoundingMaxLat], [BoundingMaxLong])
GO
CREATE STATISTICS [_dta_stat_1966630049_1_6_24_25_26_27_28] ON [dbo].[Boundary] ([BoundingMaxLong], [Acreage], [BoundaryId], [Active], [BoundingMinLat], [BoundingMinLong], [BoundingMaxLat])
GO
CREATE STATISTICS [_dta_stat_1966630049_24_28_25_1_26] ON [dbo].[Boundary] ([BoundingMinLat], [Acreage], [BoundingMinLong], [BoundaryId], [BoundingMaxLat])
GO
CREATE STATISTICS [_dta_stat_1966630049_24_25_28_6] ON [dbo].[Boundary] ([BoundingMinLat], [BoundingMinLong], [Acreage], [Active])
GO
CREATE STATISTICS [_dta_stat_1966630049_24_25_26_27_28_6] ON [dbo].[Boundary] ([BoundingMinLat], [BoundingMinLong], [BoundingMaxLat], [BoundingMaxLong], [Acreage], [Active])
GO
CREATE STATISTICS [_dta_stat_1966630049_25_6] ON [dbo].[Boundary] ([BoundingMinLong], [Active])
GO
CREATE STATISTICS [_dta_stat_1966630049_28_24_25_1_6] ON [dbo].[Boundary] ([BoundingMinLong], [BoundaryId], [Active], [Acreage], [BoundingMinLat])
GO

Uses
Object dependencies are only available when you generate your documentation. This enables SQL Doc to display the preview more quickly.
Used By
Object dependencies are only available when you generate your documentation. This enables SQL Doc to display the preview more quickly.
Created 09 October 2013 13:06
Copyright 2013 - All Rights Reserved