User Defined Table Type - Issue with Defaults

RyanC

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.

Eddie D

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

Matthew_Chappelow

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.

RyanC

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.

Matthew_Chappelow

Just to clarify in the diff viewer is it the backup or the live database that is displaying this text?

RyanC

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

RyanC

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.

Eddie D

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?