SQL Compare 6 - sysname as column datatype

Yuri

When I compare databases and source database contains a new table with column of type "sysname" - generated sql to create table uses [dbo].[sysname] as datatype, which causes an error in script, as sysname apparently does not belong to [dbo] schema.

SQL Compare 6.2.0.271

chris.buckingham

You are comparing SQL 2000 and 2005 databases - however sadly in SQL Server 2000 you cannot use a fieldname called 'sysname', so you will have to rename it when comparing between the two versions.

kibbage

He is not talking about a field name he is talking about a datatype. This appears to be a RedGate error. Do you have a fix?

Michelle T

I'm not sure how you're getting a table with [dbo].[sysname] as a column type - do you have a user defined type of this name? When I try creating a table with:

CREATE TABLE tab (id sysname, sid [sys].[sysname])

it comes out as:

CREATE TABLE [dbo].[tab]
(
[id] [sys].[sysname] NOT NULL,
[sid] [sys].[sysname] NOT NULL
) ON [PRIMARY]

GO

So I'm not sure where your tables are getting the [dbo] from. If you could send me (michelle.taylor@red-gate.com) the script you used to create the problem table or a backup of the database with the problem table, I might be able to find out what's actually being reported by SQL Server about this table / what syntax it is that we don't understand properly.

kibbage

Michelle, I have emailed you the script. Let me know if you need any more information.

Michelle T

Just to update the thread in case anyone else has this problem, it looks like SQL Compare currently has a problem synchronizing sysname datatypes from SQL 2000 servers to SQL 2005 servers - we incorrectly add 'dbo' as the schema instead of 'sys'. This can be worked around by search and replace on the generated script (replacing [dbo].[sysname] with [sys].[sysname]).

JanetMcCallion

This still seems to be a problem in SQL Compare 7. Is there a patch out for this?

Michelle T

We've just fixed this internally, so it should be working in the final release of v8.