Object cannot be cast from DBNull to other types

Yann

Hi,

Each time I want to compare my two databases, I have got the following error:
Object cannot be cast from DBNull to other types.

It's during the first step:
Registering databases
MYSERVER.MYDB-Reading columns.

Why i am having that? any idea?

Thx a lot,
Yann.

Brian Donahue

Hello Yann,

Sorry, I don't know. When this happens, it's usually because the request for schema information from the database is returning (or not returning any) data that is invalid.

If you have purchased the product, I'd recommend contacting support@red-gate.com so we can look deeper into it.

Yann

Can't find the concerning NULL object...

In fact, when SQCompare tries to "Retieving schema", I got the error.
What your application really do during the step "reading columns"?

Thx,
Yann.

Brian Donahue

At least in SQL 2000, it's running a query against syscolumns. If you get an error like this, the last time this happened the solution was inconsistency in the database. Specifically there was an IDENTITY column with no seed and increment. SQL Server would not normally allow this to happen so we don't cater for this inevitibility.

I think the first thing to try would be to check the database for curruption using DBCC CHECKDB.

PDinCA

DBCC CHECKDB on each DB reveals ZERO errors or any kind.

I used Management Studio to generate a script for each database. I chose all SPs, all Tables and all Users. Both scripts were generated successfully.

As Brian's post cited an IDENTITY issue, I searched both scripts and the IDENTITY(1,1) is correct an all tables (there are only 5 User tables in this DB, so the script and DB is miniscule).

Should a support issue be opened?

PDinCA

It appears that SQL Compare, SQL Data Compare and Dependency Tracker cannot handle:

CREATE TABLE [dbo].Group](
[GroupId] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](80) NOT NULL,
[Description] [nvarchar](255) NULL,
CONSTRAINT [PK__[Group]]__76CBA758] PRIMARY KEY NONCLUSTERED
(
[GroupId] ASC
) ON [PRIMARY]
) ON [PRIMARY]

Inrestingly enough, SQL Prettifier barfs on this... and only gives:

CREATE TABLE [dbo].Group](
[GroupId] [int

in the rendered HTML...

OPINION: Parsing is upset by the 3rd ']' after the table name, Group (Group being a reserved word, it must be terminated by an additional ].)

I verified this by:
1. Creating a new DB and adding the Group table to it. All tools barfed.
2. DROP the table and add it back as Group_SLA, with only and surrounding the table name. Table definition is now readable...

PDinCA

Pardon spellin' mistake in prior post - editing before anyone else replies to a post would be REALLY HANDY

It would also be REALLY USEFUL if, despite my Profile being HTML=Yes, the HTML option for my posts were ON, so I could post Prettifier output here

PDinCA

The revised table name, GROUP_SLA was NOT surrounded by and, just the one on each end, thanks :oops: :

CREATE TABLE [dbo].[Group_SLA](
[GroupId] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](80) NOT NULL,
[Description] [nvarchar](255) NULL,
CONSTRAINT [PK__[Group]]__76CBA758] PRIMARY KEY NONCLUSTERED
(
[GroupId] ASC
) ON [PRIMARY]
) ON [PRIMARY]

Brian Donahue

Thanks!

Editing maybe, I'm told allowing HTML code is potentially dangerous, so you're limited to BBCODE. Anyway, about this...

CONSTRAINT [PK__[Group]]__76CBA758]

Do you mean to have the end-bracket (deleimiter) in there twice? The only other thing I see could be a BIT suspicious is that you create the primary key index on PRIMARY inside the create table DDL, which is also being created on PRIMARY.

I'll put that into a SQL database tomorrow morning and see if it throws me a curve with SQL Data Compare.

PDinCA

All I did to narrow down the problem was add the table using the reserved word GROUP as the table name.

Disclaimer: A consulting firm wrote the app. - I would NEVER use a reserved word as a table name 8)

Onwards...:
Having DROPped the Group_SLA table, I used this SQL:

CREATE TABLE [dbo].[[Group]]]( 
[GroupId] [int] IDENTITY(1,1) NOT NULL, 
[Name] [nvarchar](80) NOT NULL, 
[Description] [nvarchar](255) NULL, 
) ON [PRIMARY]

It's a bare-bones table, no constraints or indexes...

Back to SQL Data Compare to run the compare on my test DB vs. a Development version - it croaks (thankfully).

If only I could insert a jpg, I could show you the dialog box... But, the process fails at "Registering databases" and shows the failing db as (local).UserAccount-Reading columns

As my test table is the only user-object there, I posited on the 3rd ]]] on the 1st line of the Create DDL being the Prettifier parser's likely stumbling block and that there may some issue with the SQL Compare tools handling a reserved word, i.e., []bounded. Wild guess maybe... but it seems the only difference that causes a problem is the use of a reserved word...

Hope this helps.