User Defined Table Type - Issue with Defaults

RyanCRyanC Posts: 4 New member
in SQL Compare
I'm using SQL Compare Command Line V13.0.7.5250 to compare a database to a backup file.

In cases where I have User Defined Table Type with a Default, the default value gets replaced with a comment and the script fails with a syntax error.

e.g.

Original Create:
CREATE TYPE [dbo].[UDT_DIMTABLES] AS TABLE
(
id INT IDENTITY(1,1) NOT NULL,
[description] [nvarchar](100) NULL,
[tablename] [nvarchar](1000) NULL,
[modelid] [int] NOT NULL,
[joinsto] [nvarchar](100) NULL,
[pointerfield1] [nvarchar](100) NULL,
[pointerfield2] [nvarchar](100) NULL,
[pointerfield3] [nvarchar](100) NULL,
[joinpointerfield1] [nvarchar](100) NULL,
[joinpointerfield2] [nvarchar](100) NULL,
[joinpointerfield3] [nvarchar](100) NULL,
[alias] [nvarchar](100) NOT NULL,
[schemaname] SYSNAME NULL,
[nolock] BIT NOT NULL DEFAULT (1),
[jointype] NVARCHAR(100),
PRIMARY KEY ([modelid], [alias])
) 
GO

SC output:

CREATE TYPE [dbo].[UDT_DIMTABLES] AS TABLE
(
[id] [int] NOT NULL IDENTITY(1, 1),
[description] [nvarchar] (100) NULL,
[tablename] [nvarchar] (1000)  NULL,
[modelid] [int] NOT NULL,
[joinsto] [nvarchar] (100)  NULL,
[pointerfield1] [nvarchar] (100)  NULL,
[pointerfield2] [nvarchar] (100) NULL,
[pointerfield3] [nvarchar] (100) NULL,
[joinpointerfield1] [nvarchar] (100) NULL,
[joinpointerfield2] [nvarchar] (100) NULL,
[joinpointerfield3] [nvarchar] (100) NULL,
[alias] [nvarchar] (100) NOT NULL,
[schemaname] [sys].[sysname] NULL,
[nolock] [bit] NOT NULL DEFAULT -- No text is available,
[jointype] [nvarchar] (100) NULL,
PRIMARY KEY CLUSTERED  ([modelid], [alias])
)

I have 10-12 of these Table Types and in all cases where I have a Default value, it gets replaced with "-- No text is available,"

Please advise.  Thank you.

Tagged:
· Share on Twitter

Best Answer

  • Eddie DEddie D Posts: 1,803 Rose Gold 5
    Answer ✓
    Hi,
    using command line versions V13.3.6.6387 and V13.4.5.6953 of SQL Compare, I cannot recreate the reported problem where the default value for the nolock becomes:
    [nolock] [bit] NOT NULL DEFAULT -- No text is available,

    I have tried to locate the version of SQL Compare you are using, V13.0.7.5250.  Sadly the versioning history in the releases notes for both SQL Compare and DLM Automation V2, contain no mention of this version.  V13.0.6 become available on the 9th October 2017 for SQL Compare and 11th October 2017 for DLM Automation V2.  With V13.1 released on the 16th October 2017 for SQL Compare and 25th October 2017 for DLM Automation V2.

    I recommend that you upgrade your SQL Compare installation using the 'check for Updates' feature to the latest version and confirm if you are still experiencing the problem you reported in this forum topic.

    Many Thanks

    Eddie

    Eddie Davis
    Senior Product Support Engineer
    Redgate Software Ltd
    Email: support@red-gate.com
    · Share on Twitter

Answers

  • Matthew_ChappelowMatthew_Chappelow Posts: 19 Bronze 2
    Could you run the following SQL in ssms as the same user as you use for compare:

    SELECT object_id, definition, parent_column_id FROM sys.default_constraints

    The definition column is the one of particular interest here.
    · Share on Twitter
  • RyanCRyanC Posts: 4 New member
    Matthew_Chappelow said:
    Could you run the following SQL in ssms as the same user as you use for compare:

    SELECT object_id, definition, parent_column_id FROM sys.default_constraints

    The definition column is the one of particular interest here.

    I'm not seeing any issues there.  Using this query:

    select dc.definition, dc.*
    from sys.default_constraints dc
    join sys.all_columns c on dc.object_id = c.default_object_id
    join sys.table_types tt on tt.type_table_object_id = c.object_id

    All have the correct value in that column.
    · Share on Twitter
  • Matthew_ChappelowMatthew_Chappelow Posts: 19 Bronze 2
    Just to clarify in the diff viewer is it the backup or the live database that is displaying this text?
    · Share on Twitter
  • RyanCRyanC Posts: 4 New member
    Matthew_Chappelow said:
    Just to clarify in the diff viewer is it the backup or the live database that is displaying this text?
    This is in the file specified by the /ScriptFile switch in the command line.

    Roughly:

    <div>sqlcompare13='C:\Program Files (x86)\Red Gate\DLM Automation 2\SC\SQLCompare.exe'</div><div>SourceBackupPath='E:\sourceDB.bak'</div><div>TargetServerName='TargetSQL'</div><div>TargetDBName='TargetDB'</div><div>UpgradeScriptFile='E:\sc_upgrade.sql'</div><div><br></div><div><br></div><div>"!sqlcompare13!" /Backup1:"!SourceBackupPath!" /server2:"!TargetServerName!" /Database2:"!TargetDBName!" /scriptFile:"!UpgradeScriptFile!"</div>

    These are the options i'm using:

    SQLCompareOptions=/options:NoAutoColumnMapping,DecryptPost2KEncryptedObjects,IgnoreFillFactor,IgnoreWhiteSpace,IncludeDependencies,IgnoreUserProperties,IgnoreWithElementOrder,IgnoreDatabaseAndServerName,NoTransactions,DisableAndReenableDdlTriggers,AddDatabaseUseStatement,IgnorePermissions,IgnoretSQLt,IgnoreUsersPermissionsAndRoleMemberships,IgnoreIdentitySeedAndIncrement



    · Share on Twitter
  • RyanCRyanC Posts: 4 New member
    Eddie D said:
    Hi,
    using command line versions V13.3.6.6387 and V13.4.5.6953 of SQL Compare, I cannot recreate the reported problem where the default value for the nolock becomes:
    [nolock] [bit] NOT NULL DEFAULT -- No text is available,

    I have tried to locate the version of SQL Compare you are using, V13.0.7.5250.  Sadly the versioning history in the releases notes for both SQL Compare and DLM Automation V2, contain no mention of this version.  V13.0.6 become available on the 9th October 2017 for SQL Compare and 11th October 2017 for DLM Automation V2.  With V13.1 released on the 16th October 2017 for SQL Compare and 25th October 2017 for DLM Automation V2.

    I recommend that you upgrade your SQL Compare installation using the 'check for Updates' feature to the latest version and confirm if you are still experiencing the problem you reported in this forum topic.

    Many Thanks

    Eddie


    Thanks Eddie.  Yes, version 13.4 did fix the issue with Types, however, now I'm running into an issue that it is not creating Sequence objects.  Is that a known issue/limitation? I'm going to abandon using the bak as the compare source for now.
    · Share on Twitter
  • Eddie DEddie D Posts: 1,803 Rose Gold 5
    Hi Ryan

    Thank you for your reply.
    SQL Compare does support Sequence objects.
    From your last comment:
    now I'm running into an issue that it is not creating Sequence objects.  Is that a known issue/limitation? I'm going to abandon using the bak as the compare source for now.

    Is this only occurring when you use a backup file as a data source?

    Eddie Davis
    Senior Product Support Engineer
    Redgate Software Ltd
    Email: support@red-gate.com
    · Share on Twitter
Sign In or Register to comment.