SQL Compare 6 - sysname as column datatype

YuriYuri Posts: 6
edited February 4, 2009 7:28AM in SQL Compare Previous Versions
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

Comments

  • 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.
    Chris Buckingham
    Red-Gate support
  • kibbagekibbage Posts: 7 Bronze 2
    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?
    Ken Gladden
    www.teeso.com
    The Essential Electronic School Office
  • 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 ([email protected]) 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.
    Software Developer
    Redgate Software
  • kibbagekibbage Posts: 7 Bronze 2
    Michelle, I have emailed you the script. Let me know if you need any more information.
    Ken Gladden
    www.teeso.com
    The Essential Electronic School Office
  • 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]).
    Software Developer
    Redgate Software
  • This still seems to be a problem in SQL Compare 7. Is there a patch out for this?
    Jan McCallion
  • We've just fixed this internally, so it should be working in the final release of v8.
    Software Developer
    Redgate Software
Sign In or Register to comment.