Object Reference not set to an instance of an Object

Brian Donahue

Hello Hambledon,

This doesn't seem right -- SQL Compare should completely ignore Extended Properties applied to system tables. I even double-checked by adding a column comment to dtproperties and running SQL Compare against the database and it didn't appear in the migration SQL.

Is it possible that somehow, your dtproperties had got changed from a System-type table to a User-type table?

Daniel Handley

Hi Hambledon

Can you check the xtype of the object in the sysobjects table? It should be S for system.

This is another way to identify a system object as well as using OBJECTPROPERTY.

Regards
Dan

Brian Donahue

Hello Hambledon,

You're right: dtproperties has a designation of U for some reason. Maybe because this table contains database diagram information. I'd guess that SQL Compare would filter this table by name only.

Is the owner of the table still [dbo], or perhaps the ownership has changed, causing SQL Compare to pick up on the table anyway?

Brian Donahue

Hi Hambledon,

I'll give it a try and see what happens!

Brian Donahue

Hi Hambledon,

I think we know what the problem is now! I can add an extended property to any trigger in any table in my database except for dtproperties.

This is what's happening:

SQL Compare detects all of the extended properties in the database. Then it tries to link the EP to a database object so that it will appear as part of SQL Compare's internal representation of the table.

As you've pointed out, the way to filter out all system tables is by excluding all of the ones with an xtype of 'S' AND dtproperties (by name).

So when SQL Compare tries to resolve the extended property to a table -- it can't -- because SQL Compare had not registered the dtproperties table!

That explains the null-reference error.

Now, we have to figure out how to drop the extended property. SQL Compare will not provide the information because it errors out. We'll need to query sysobjects to find it.

select p.name as EP, s3.name as [Trigger] from sysproperties p inner join sysobjects s3 on p.id=s3.id where p.id IN (select s2.id from sysobjects s1 inner join sysobjects s2 on s2.parent_obj=s1.id where s1.name='dtproperties' and s2.xtype='TR')

Result:
EP Trigger

---

MS_Description trgdtproperties

This will tell you what arguments to supply to sp_dropextendedproperty to drop the extended property:

exec sp_dropextendedproperty 'MS_Description', 'user', dbo, 'table', dtproperties, 'trigger', trgdtproperties

I hope this helps!

Brian Donahue

Hello,

This error message is a very generic error. Most errors are 'trapped' by the software and return a 'user-friendly' error message.

Noll-reference exceptions like this one usually indicate a database problem due to a database inconsistency.

For instance, IDENTITY columns that have a SEED or INCREMENT value of NULL will cause this error, because these values should never, ever be NULL, or your database will be in really bad shape.

Likewise, with the above, SQL Compare filters out the system tables, so any attempt to resolve an extended property to a system table will result in an error. We'll trap and fix this condition in a future release, but as a rule, people shouldn't be making modifications to MS-Shipped tables!

If you'd provide us with some more information, such as exactly where the error occurs (during database registration, comparing databases, etc), then we can try to figure out where the problem lies.

rdobrich

Hi,
I have two snapshot for the databases that I can't compare.
I get error after registrate the databases, during the comparation.

My version if SQLCompare is 3.1.9.4
(with trial version everithing works fine).

Please send me an e_mail so I can send you snapshot of that databases.

Radovan

hambledon

Radovan,
You might try running SQL Profiler against the server while running Red-Gate. That will give you the last query that ran before the wreck, and that is how I found out that it was an extended property issue.

hambledon

Brian/Daniel,
It appears that SQL Compare doesn't compare extended properties on View Columns - is that correct?

Thanks!

Brian Donahue

Hello,

SQL Compare does not properly script extended properties on a view, a problem which will be fixed in a future version.

This will not cause the software to throw a NULL reference exception, however.

Brian Donahue

Radovan,

One of your user-defined functions has no text (DDL) associated with it. The function may work in the database because it's been compiled, but SQL Compare looks at the database's syscomments table to find the DDL, and since it found nothing in syscomments for that function, SQL Compare throws a null-reference error.

rdobrich

Thanks you red-gate

Today I update new version (3.1.9.12) and works fine.

Glad that snepshot helps

Radovan

Nir_Kornfled

Hello,

I've read the thread but nothing seems to solve the error on my database.

I get this error while trying to compare a specific database to a snapshot or to another database. Trying to compare a snapshow of the database results to the same error.

I'm using SQLCompare 3.1.9.12
Is there a way to see where the error occurs in SQLCompare?

I'm currently stuck and can not make a release!

Please hurry.

Nir_Kornfled

OK. Found it! I hope...

A database with a table function and a non-english comment in it will raise the error, making SQLCompare fail to compare it with any other database or image.

Example - The following function created to an empty database will fail SQLCompare:

create function dbo.udfTest()
returns @Res table (a1 int)
as
begin
  -- אא
	return
end

Same function exactly with english remark works fine!

create function dbo.udfTest()
returns @Res table (a1 int)
as
begin
  -- aa
	return
end

This happens with the latest version 3.1.9.12 and did not in earlier versions! :evil:

Following is the callstack when simply trying to display the changes on this function:

************** Exception Text **************
System.NullReferenceException: Object reference not set to an instance of an object.
at RedGate.SQL.Shared.ExecutionBlock.ᐁ(String , Boolean , Boolean )
at RedGate.SQL.Shared.ExecutionBlock.AddBatch(String batch)
at RedGate.SQLCompare.Engine.Work.ᐁ(IBlockWriter , Function , Options )
at RedGate.SQLCompare.Engine.Work.BuildFromDifferences(Differences differences, Difference onlyDifferenceToInclude, Options options, Boolean runOnTwo)
at ᐉ.ᐄ()
at ᐉ.ᐁ(Difference )
at ᐏ.ᐂ(Difference )
at ᐏ.ᐁ(Difference )
at ᐅ.ᐁ(Object , EventArgs )
at RedGate.SQL.Grids.GridControl.OnGridCellSelected(GridCellSelectedEventArgs e)
at RedGate.SQL.Grids.GridControl.set_SelectedCell(SelectedCell value)
at ᐅ.ᐁ(Difference , Int32 )
at ᐅ.ᐁ(Differences )
at ᐏ.ᐑ()
at ᐏ.ᐎ()
at ᐏ.ᐁ(Object , ItemClickEventArgs )
at DevExpress.XtraBars.BarManager.OnClickItem(BarItem item, BarItemLink link)
at DevExpress.XtraBars.BarItem.OnClick(BarItemLink link)
at DevExpress.XtraBars.BarButtonItem.OnClick(BarItemLink link)
at DevExpress.XtraBars.BarItem.OnClickCore(BarItemLink link)
at DevExpress.XtraBars.BarItemLink.OnLinkClick()
at DevExpress.XtraBars.BarItemLink.OnLinkAction(BarLinkAction action, Object actionArgs)
at DevExpress.XtraBars.BarButtonItemLink.OnLinkAction(BarLinkAction action, Object actionArgs)
at DevExpress.XtraBars.ViewInfo.BarSelectionInfo.ClickLink(BarItemLink link)
at DevExpress.XtraBars.ViewInfo.BarSelectionInfo.UnPressLink(BarItemLink link)
at DevExpress.XtraBars.Controls.CustomLinksControl.OnMouseUp(MouseEventArgs e)
at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
at System.Windows.Forms.Control.WndProc(Message& m)
at DevExpress.XtraBars.Controls.DockedBarControl.WndProc(Message& msg)
at System.Windows.Forms.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)

I need a quick fix - my release is delayed because I was stupid to update SQLCompare before it.

Red-Gate people - please respond.

Thank you.

Daniel Handley

Hi

There is a new issue that has been highlighted by the new version.
I will email you directly, or can you send me further information to support(at)red-gate.com .

Regards
Dan

Brian Donahue

The patched version has been released to correct the problem and is available by using the 'check for updates' from the help menu. Once you've upgraded to SQL Compare 3.2.0.5, the problem should go away.
If you have any problems with the update, feel free to write support -at- red-gate.com.