Unknown type - dbo.dbo

twistedstream

I just fired up Data Compare 5 for the first time and got an error comparing 2 databases that compare fine in v4. Here's a screenshot:



Any ideas?
~pete

twistedstream

I also forgot to mention that I successfully performed a schema compare with these two databases using SQL Compare 5. It's only SQL Data Compare 5 that seems to have the issue.

Thanks,
~pete

Daniel Handley

Hi Pete

This sounds like a mapping that has been performed incorrectly.

Data Compare uses the SQL Compare database registration so if it works in one it should in the other.

The screenshot shows that the mapping failed so as I suggested can you check this.

Regards
Dan

richardjm

Can I ask if you have any user defined types in your database and what they may be defined as. We came across this problem in testing and we thought we had it fixed.

Any information on types used would be most helpful - perhaps you could send me a SQL Compare snapshot of the structures of the two databases?

Thanks

richardjm

After having a look at the snapshots that I've been sent we've found out that SQL Data Compare has a problem with user defined types in SQL 2000 that are owned by any user other than 'dbo'.

We're looking into this now and will hopefully get a fix out as soon as we can.

SQL 2005 and SQL Server 7 are unaffected you'll all be happy to hear.

dairec

Hi there,

my user-defined types are owned by 'dbo' and I'm getting the same error. I'm using SQLServer 2000.
Any progress on this fix?

Daire Cunningham

richardjm

If you download the latest point release for SQL Bundle 5 - funnily enough called 5.1 this issue should be fixed.

If it isn't I'll cry - one of those bugs that refuses to die.

Hope this helps.

dairec

Richard, you better get the handkerchief because I'm using version 5.1.0.106 of Data Compare.

Daire

richardjm

What's the exact error message that you are receiving? At least I thought the error message was more sensible nowadays.

Which version of SQL Server are you using?

dairec

Richard,

The message is "Unknown type - sys.udtCode". udtCode is my user-defined data type {varchar(20)} and is in both DBs being compared.

The comaprison is between two different servers both with SQL Server 2000. Perhaps interestingly one server is version 8.00.194 and the other 8.00.2093 (SP4). Do you think applying SP4 to be dev server might fix it?

Daire

richardjm

Hmmm, is there any chance you can send me a snapshot of the schema of the databases on each server?

Not sure if it's a SP4 issue but that may be worth giving a try. We can't yet reproduce your error in-house but we're trying.

I'll pm you my email.

Thanks

richardjm

OK, thanks for the snapshots. We've had a look and what we're thinking is that maybe if the database was created on SQL Server 7 and then upgraded to SQL 2000 sometimes SQL Server doesn't set the user of the UDT correctly.

I've just been on the phone to another user who also has this problem, but even more interesting version 5.0 seemed to work.

We'll keep you posted on progress. If there is any way to get a database to me that exhibits the behaviour that would be fantastic.

Also if there is anybody else out there having this problem please let me know.

GiageJoe

Richard,

I just attempted to use the 5.1.0.106 build of Data Compare and got Unknown Type - sys.Address. The user defined type of Address is owned by dbo in our SQL Server 2000. I received this problem in mutliple databases (all having our user defined types). One of them was just generated by a home grown app that creates the db based on creation scripts.

Any info on how to get around this would be great as I am suddenly stuck!

Thanks,

Joe Huber

jacquih

I'm using the SQL Packager 5.1.0.121 and got Unknown type - sys.PartOfYear error. The user-defined type PartOfYear is owned by dbo, and the two databases are on the same server. Should I go back to 5.0 in the mean time?

richardjm

I think if you have the option of going back to 5.0 in the meantime and it worked for you that's probably the best. We're looking into this problem urgently (obviously ) and we're hoping to get a fix out just as soon as we can replicate the problem. If anybody experiencing the problem has scripts or a database backup they can send in that exhibits the problem that would be great, you can email them to support@red-gate.com and mark them for my attention.

A few assumptions we're making on this problem.

1) Only SQL 2000 is affected
2) udts in tables cause the error
3) The problem isn't connected with upgrading a database from SQL 7 to 2000

If anybody out there is experiencing the problem and doesn't match the assumptions I'm interested in hearing from you.

If anybody can give me any more information on exactly how they created the UDT or what script was run to create the problem that would be great and also the exact version of SQL 2000 you're running. Like I said we're unable to re-create the error in house so far.

Thanks everybody for any help in getting to the bottom of this.

richardjm

Well thanks to Daire we now know exactly what is causing the problem. It is caused by using a UDT in a view on SQL Server 2000. We'll get this fixed as a priority.

If in the meantime a workaround is to edit the comparison project and de-select 'Include indexed views' in the Options tab.


Thanks guys for all of your help.