Object reference not set to an instance of an object

elstx

I'm getting the message:
Object reference not set to an instance of an object
when trying to compare databases. I saw in a post in another forum that a table with no Primary Key and an ntext field would cause the issue. The table has no PK, but it doesn't have any ntext fields. Adding a PK fixes the problem, but is there a fix for this? I'm comparing on a unique index, not a primary key.

Thanks.

Brian Donahue

Hi,

The problem that you saw in in the other bulletin board posts was an issue with Packager. It has a problem with tables that have a text field and no primary key.

The problem you describe happens with Data Compare.Engine and has to do with there being no tables eligible for comparison returned by the CreateFromIntersection method. That happens when the table has no primary key/unique index.

If you want to use a unique index, remember to give the index the same case-sensitive name in both databases, or you'll have a problem!

elstx

I used the same script to create the unique index on each database, so the names should not be an issue.

I tried running the GUI to see how it reacted. The GUI did not include the UI in the drop down list. If I changed it to compare the database to itself (the db receiving the updates) I got the UI in the drop down. If I changed it to compare the sending db to itself, no UI in the drop down list. I’m obviously missing something pretty basic here, just not sure what it is.

Is there a way to get better diagnostic information out of the system?

Comparison Results
Getting database 1 10.0.66.35.DB2
Getting database 2 10.0.66.35.DB1
Comparing databases
TableName Field
[dbo].[_get_remedies_for_session_vulns_for_devices_table] RedGate.SQLDataCompare.Engine.Fields
[dbo].[_get_remedies_for_vulns_for_devices_table] RedGate.SQLDataCompare.Engine.Fields
[dbo].[ACTION] RedGate.SQLDataCompare.Engine.Fields
[dbo].[ACTION_CATEGORY] RedGate.SQLDataCompare.Engine.Fields
FAILED TO COMPARE DATABASES
Object reference not set to an instance of an object.

DB1 Table Formats
CREATE TABLE [dbo].[ACTION] (
[ACTION_ID] [int] NOT NULL ,
[SEQUENCE_ID] [int] NOT NULL ,
[REMEDY_ID] [int] NOT NULL ,
[ACTION_TYPE] [int] NOT NULL ,
[ServerName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[RecordID] [bigint] IDENTITY (1, 1) NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[ACTION_CATEGORY] (
[ACTION_CATEGORY_ID] [int] NOT NULL ,
[ACTION_CATEGORY_NAME] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[RecordID] [bigint] IDENTITY (1, 1) NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[ACTION_DEFINITION] (
[ACTION_DEFINITION_ID] [int] NOT NULL ,
[ACTION_CATEGORY_ID] [int] NOT NULL ,
[ACTION_TEXT] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[RecordID] [bigint] IDENTITY (1, 1) NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[ACTION] WITH NOCHECK ADD
CONSTRAINT [PK_ACTION_RecKey] PRIMARY KEY CLUSTERED
(
[RecordID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[ACTION_CATEGORY] WITH NOCHECK ADD
CONSTRAINT [PK_ACTION_CATEGORY_RecKey] PRIMARY KEY CLUSTERED
(
[RecordID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[ACTION_DEFINITION] WITH NOCHECK ADD
CONSTRAINT [PK_ACTION_DEFINITION_RecKey] PRIMARY KEY CLUSTERED
(
[RecordID]
) ON [PRIMARY]
GO

CREATE INDEX [IX_ACTION] ON [dbo].[ACTION]([SEQUENCE_ID], [REMEDY_ID]) ON [PRIMARY]
GO

CREATE UNIQUE INDEX [IX_ACTION_1] ON [dbo].[ACTION]([ACTION_ID], [ACTION_TYPE], [SEQUENCE_ID]) ON [PRIMARY]
GO

CREATE UNIQUE INDEX [UI_AK_ComparisonKey] ON [dbo].[ACTION]([ACTION_ID], [ServerName]) ON [PRIMARY]
GO

CREATE UNIQUE INDEX [UI_AK_ComparisonKey] ON [dbo].[ACTION_CATEGORY]([ACTION_CATEGORY_ID]) ON [PRIMARY]
GO

CREATE UNIQUE INDEX [UI_AK_ComparisonKey] ON [dbo].[ACTION_DEFINITION]([ACTION_DEFINITION_ID]) ON [PRIMARY]
GO

DB2 Table formats
CREATE TABLE [dbo].[ACTION] (
[ACTION_ID] [int] NOT NULL ,
[SEQUENCE_ID] [int] NOT NULL ,
[REMEDY_ID] [int] NOT NULL ,
[ACTION_TYPE] [int] NOT NULL ,
[ServerName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[ACTION_CATEGORY] (
[ACTION_CATEGORY_ID] [int] NOT NULL ,
[ACTION_CATEGORY_NAME] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[ACTION_DEFINITION] (
[ACTION_DEFINITION_ID] [int] NOT NULL ,
[ACTION_CATEGORY_ID] [int] NOT NULL ,
[ACTION_TEXT] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[ACTION] WITH NOCHECK ADD
CONSTRAINT [PK_BASELINE_ACTION] PRIMARY KEY CLUSTERED
(
[ACTION_ID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[ACTION_CATEGORY] WITH NOCHECK ADD
CONSTRAINT [PK_ACTION_CATEGORY] PRIMARY KEY CLUSTERED
(
[ACTION_CATEGORY_ID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[ACTION_DEFINITION] WITH NOCHECK ADD
CONSTRAINT [PK_ACTION_DEFINITION] PRIMARY KEY CLUSTERED
(
[ACTION_DEFINITION_ID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[ACTION] ADD
CONSTRAINT [DF__ACTION__ServerNa__51EF2864] DEFAULT ('GR08') FOR [ServerName]
GO

CREATE INDEX [IX_ACTION] ON [dbo].[ACTION]([SEQUENCE_ID], [REMEDY_ID]) ON [PRIMARY]
GO

CREATE UNIQUE INDEX [UI_AK_ComparisonKey] ON [dbo].[ACTION]([ACTION_ID], [ServerName]) ON [PRIMARY]
GO

CREATE UNIQUE INDEX [UI_AK_ComparisonKey] ON [dbo].[ACTION_CATEGORY]([ACTION_CATEGORY_ID]) ON [PRIMARY]
GO

CREATE UNIQUE INDEX [UI_AK_ComparisonKey] ON [dbo].[ACTION_DEFINITION]([ACTION_DEFINITION_ID]) ON [PRIMARY]
GO

CREATE UNIQUE INDEX [IX_ACTION_1] ON [dbo].[ACTION]([ACTION_ID], [ACTION_TYPE], [SEQUENCE_ID]) ON [PRIMARY]
GO

CREATE UNIQUE INDEX [UI_AK_ComparisonKey] ON [dbo].[ACTION]([ACTION_ID], [ServerName]) ON [PRIMARY]
GO

CREATE UNIQUE INDEX [UI_AK_ComparisonKey] ON [dbo].[ACTION_CATEGORY]([ACTION_CATEGORY_ID]) ON [PRIMARY]
GO

CREATE UNIQUE INDEX [UI_AK_ComparisonKey] ON [dbo].[ACTION_DEFINITION]([ACTION_DEFINITION_ID]) ON [PRIMARY]
GO

ALTER TABLE [dbo].[ACTION] ADD
CONSTRAINT [FK_ACTION_ACTION_DEFINITION] FOREIGN KEY
(
[ACTION_TYPE]
) REFERENCES [dbo].[ACTION_DEFINITION] (
[ACTION_DEFINITION_ID]
),
CONSTRAINT [FK_ACTION_VULNERABILITY_ENVIRONMENT_REMEDY] FOREIGN KEY
(
[REMEDY_ID]
) REFERENCES [dbo].[REMEDY] (
[REMEDY_ID]
) ON DELETE CASCADE
GO

ALTER TABLE [dbo].[ACTION_DEFINITION] ADD
CONSTRAINT [FK_ACTION_DEFINITION_ACTION_CATEGORY] FOREIGN KEY
(
[ACTION_CATEGORY_ID]
) REFERENCES [dbo].[ACTION_CATEGORY] (
[ACTION_CATEGORY_ID]
)
GO

elstx

Actually, If I changed it to compare the sending db to itself, no UI in the drop down list. Apparently the GUI came up before everything was fully functional. I went back and the drop down was fully populated with the PK and the UI.

If I reverse the order of the DB being compared, I get the UI available for all tables. Is there some sort of rule the compare follows as to when a UI is valid for comparison purposes?

elstx

I think I see the issue, but need to know how to force UI usage. It appears that if the UI is the same as the PK, the tool does not present the UI as an option for use in a comparison. If the UI is different than the PK, then it's presented.

In my situation, data is being migrated from one system to a staging db where additional data is added to some of the tables, part of which becomes part of the new key (UI). The staging db is then used to update the reporting db using the UI as the key to match.

In the console exe, I changed the key compare to use the UI (which is named the same for all tables). I'm guessing that since the GUI datacompare didn't offer the UI in all cases the same would hold true for the console exe. I'm trying to keep the compare as generic as possible so new tables can be added without having to alter any compare activities. Therefore, I don't really want to change the code to look for specific tables to see if they should compare against UI or PK.
Is there any way to force datacompare to use the UI in all instances?

Brian Donahue

Hi,

Just having a glance at the SQL, I can see that the [dbo].[ACTION_CATEGORY] table will not be compared because it's missing the RecordID (bigint) identity column in one of the databases. The primary key needs to be on the same column.

elstx

Action_Category compares and synchs in the GUI version. Best guess would be that it compares the PK to the UI since in this instance they are comprised of the same fields.

Maybe I'm making this harder than I need to by trying to tell it which key to compare on - it appears it is doing a pretty good job on its own. I am concerned though that you think it shouldn't be working - does that mean what I see as a good feature may be deemed a bug and fixed, thus altering the way the application works?