Error: Specified Cast Is Not Valid

keeneyen

Receiving the message "specified cast is not valid" when comparing two databases in the same SQL Server 2005 instance. Saw a posting from user digitalage where the thread of the posting mentioned that it was a database compatibility problem. The db compatibility for the 2 dbs that I am comparing is 90, so I do not think that is the problem.

This error is rendering the SQL Compare tool useless in our organization for our SQL 2005 databases as several of us are receiving the error message and we cannot compare databases.

Thank you in advance.

Brian Donahue

Hi,

The only other time I'd seen this error was on a database that had some owner-less views in it. Can you please check that? You should open Enterprise Manager and look for any views where the owner column is blank. I think that happened because the database had been upgraded from 6.5 to 7 to 2000 to 2005, and some objects that 'look like' system tables were left behind such as INFORMATION_SCHEMA (SQL 2005 also has these, but it's listed under system views).

keeneyen

Thanks for replying, Brian.

Checked SQL 2005 Management Studio (both databases are in SQL 2005) in both databases for views w/o an owner, but I did not see any.

Should I look for other objects that do not have an owner/schema assigned. We use a schema other than dbo for all of our user defined objects. Also, I am not in db_owner, could there be an issue related to permissions?

What else can I check?

Thanks again.

Neil Davidson

Hi,

Can you go to the help->about box and let us know what version and build number you're using please.

keeneyen

4.1.0.59

SQL Compare installed on Windows XP SP2. Database server is Windows Server 2003.

SQL Server version is 9.00.1399.06, Standard Edition.

Let me know if there is anything else.

Thanks!

Neil Davidson

Hi,

Any chance you could try out the v 5 release candidate (http://www.red-gate.com/messageboard/vi ... php?t=2074)?

keeneyen

If you think it would help to isolate and fix the bug we are experiencing then yes I would try it.

This may be obvious, but my organization would not implement release candidate software in production environments.

So, hopefully we can get a production release build or patch to fix the problem.

Thanks again for your help!

Tom Harris

Hi there,

SQL Bundle 5 has now been released. Can you check out whether your problem is fixed in the new release?

Thanks,

Tom

keeneyen

I installed version 5 and received the same error when trying to perform the same comparison. The exception seemed to be thrown while reading permissions. I selected "Ignore permissions" and "Ignore users roles...", to no avail, same error:

Specified cast is invalid.[/img]

Brian Donahue

Hello,

We may need to ask for backups of this database for debugging. There is probably an inconsistency in the system tables (maybe caused by some upgrading baggage from 65->70->2000->2005).

keeneyen

Ok, let me know how you would like to go about exchanging the backups. To avoid exposing my company's proprietary information, database schema etc.., would it make sense just to send you the system tables?

Regarding the upgrade scenario you described, the original databases involved in the comparison was copied from a sql 2000 instance having been originally created on that 2000 instance. I am checking with our DBAs regarding the method they used to copy the database. The second database involved in the comparison is a copy that first database.

Let me know how we should proceed.

Thanks,

Eric

Brian Donahue

Hi Eric,

This may not be necessary now -- thanks to another customer we have got a working example of this, and found an issue with the way SQL Server 2005 handles databases in backwards-compatibility mode. If you could please send me an email to support@red-gate.com, I will notify you when I know more about a potential fix.

keeneyen

How is it in "backwards compatibility mode" when the database compatibility level is set to 90? Are you referring to the fact that the database was restored on SQL 2005 from a SQL 2000 backup?

Let me know how it goes. I will email to red-gate support as you suggested.

Thanks.

Brian Donahue

Hi.

Looking forward to your email.

dac

Has this issue been resolved?
I'm running into a similar / exact same error message:
Unknown
Specified cast is not valid.

when trying to compare a database from our test and production environments. (both sql server 2000, which leads me to believe the above problem isn't 2k5 specific).

I am able to compare other databases between the instances, just having a problem with a specific set of databases.

Thanks
D

Brian Donahue

Hello,

The error could possibly occur on SQL 2000; it happens because the schema information being returned is not correct, for instance an IDENTITY column is missing a seed or increment value -- a situation which should theoretically never happen because SQL Server would not allow an IDENTITY to be created this way.

Possibly there could be a software issue affecting you. What I'd like to do is send an internal build out to you and see if it helps. If you don't mind, I'd appreciate it if you could email support@red-gate.com with your serial number.