Invalid syntax in CREATE TYPE statement

isme

My production logging server has received many ad-hoc changes over the past few months.

I want to use SQL Compare to get the schema of the logging servers in testing in production in sync again to re-establish a meaningful testing process.

SQL Compare is normally great for this task. But today it's generating invalid syntax for a user-generated table type.

Here's what what I do:

Compare production database as source and testing database as target.

Check the object dbo.tvpPointsTableType.

Inspect the diff. It looks like this:



The diff shows that the object eixsts in production but not in testing.

Choose to deploy the diff using SQL Compare.

The error message looks like this:

The following error message was returned from the SQL Server:

[155] 'fillfactor' is not a recognized CREATE TYPE option.

The following SQL command caused the error:

CREATE TYPE [dbo].[tvpPointsTableType] AS TABLE
(
[id] [int] NOT NULL IDENTITY(1, 1),
[point1_id] [char] (4) COLLATE Latin1_General_CI_AS NULL,
[latitude1] [numeric] (9, 6) NULL,
[longitude1] [numeric] (9, 6) NULL,
[point2_id] [char] (4) COLLATE Latin1_General_CI_AS NULL,
[latitude2] [numeric] (9, 6) NULL,
[longitude2] [numeric] (9, 6) NULL,
PRIMARY KEY CLUSTERED ([id]) WITH (FILLFACTOR=75)
)


The following messages were returned from the SQL Server:

[5701] Changed database context to 'Logging'.
[0] Creating types

The full deployment script SQL Compare generates looks like this:

/*
Run this script on:

        CloudcorpTesting\Logging.Logging    -  This database will be modified

to synchronize it with:

        CloudcorpProd.Logging

You are recommended to back up your database before running this script

Script created by SQL Compare version 10.2.0 from Red Gate Software Ltd at 06/11/2012 16:51:27

*/
SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
USE [Logging]
GO
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id=OBJECT_ID('tempdb..#tmpErrors')) DROP TABLE #tmpErrors
GO
CREATE TABLE #tmpErrors (Error int)
GO
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION
GO
PRINT N'Creating types'
GO
CREATE TYPE [dbo].[tvpPointsTableType] AS TABLE
(
[id] [int] NOT NULL IDENTITY(1, 1),
[point1_id] [char] (4) COLLATE Latin1_General_CI_AS NULL,
[latitude1] [numeric] (9, 6) NULL,
[longitude1] [numeric] (9, 6) NULL,
[point2_id] [char] (4) COLLATE Latin1_General_CI_AS NULL,
[latitude2] [numeric] (9, 6) NULL,
[longitude2] [numeric] (9, 6) NULL,
PRIMARY KEY CLUSTERED  ([id]) WITH (FILLFACTOR=75)
)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
IF EXISTS (SELECT * FROM #tmpErrors) ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT>0 BEGIN
PRINT 'The database update succeeded'
COMMIT TRANSACTION
END
ELSE PRINT 'The database update failed'
GO
DROP TABLE #tmpErrors
GO

I would expect the script generated by SQL Compare to look more like the one generated by Management Studio. Management Studio scripts the source object like this:

USE [Logging]
GO

/****** Object:  UserDefinedTableType [dbo].[tvpPointsTableType]    Script Date: 06/11/2012 16:49:10 ******/
CREATE TYPE [dbo].[tvpPointsTableType] AS TABLE(
	[id] [int] IDENTITY(1,1) NOT NULL,
	[point1_id] [char](4) NULL,
	[latitude1] [numeric](9, 6) NULL,
	[longitude1] [numeric](9, 6) NULL,
	[point2_id] [char](4) NULL,
	[latitude2] [numeric](9, 6) NULL,
	[longitude2] [numeric](9, 6) NULL,
	PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (IGNORE_DUP_KEY = OFF)
)
GO

This script executes successfully at the target.

I can work around this problem by using Management Studio to deploy the object.

It would be awesome if SQL Compare knew how to handle it properly.

Is there something I can do to fix my copy of SQL Compare?

Brian Donahue

Unfortunately, I cannot reproduce this problem. When I use your script, SQL Compare recreates the type correctly:

SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id=OBJECT_ID('tempdb..#tmpErrors')) DROP TABLE #tmpErrors
GO
CREATE TABLE #tmpErrors (Error int)
GO
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION
GO
PRINT N'Creating types'
GO
CREATE TYPE [dbo].[tvpPointsTableType] AS TABLE
(
[id] [int] NOT NULL IDENTITY(1, 1),
[point1_id] [char] (4) COLLATE Latin1_General_CI_AS NULL,
[latitude1] [numeric] (9, 6) NULL,
[longitude1] [numeric] (9, 6) NULL,
[point2_id] [char] (4) COLLATE Latin1_General_CI_AS NULL,
[latitude2] [numeric] (9, 6) NULL,
[longitude2] [numeric] (9, 6) NULL,
PRIMARY KEY CLUSTERED  ([id])
)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
IF EXISTS (SELECT * FROM #tmpErrors) ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT>0 BEGIN
PRINT 'The database update succeeded'
COMMIT TRANSACTION
END
ELSE PRINT 'The database update failed'
GO
DROP TABLE #tmpErrors
GO

Possibly your version of SQL Compare is out of date, or you will have to send us the entire schema for this database to replicate the problem.

isme

Thanks for investigating, Brian.

This is academic now, because I can no longer reproduce the original problem after I synchronizing the object using Management Studio.

However, your attempt to reproduce the problem still demonstrates that SQL Compare does not correctly script out the user-defined table type.

In the Management Studio script, the primary key is declared like this:

PRIMARY KEY CLUSTERED ([id])
(
  [id] ASC
)WITH (IGNORE_DUP_KEY = OFF)

isme

Thanks for investigating, Brian.

This is academic now, because I can no longer reproduce the original problem after I synchronizing the object using Management Studio.

You can close the original issue as 'not reproducible'.

However, your attempt at reproducing the problem still indicates a problem with how SQL Compare handles user-defined table types.

I'll start a new thread to focus on the new issue.

Please ignore my last post. I hit 'Submit' instead of 'Preview'.

isme

Also ignore my second comment.

I misread the SSMS script.

I just realized that IGNORE_DUP_KEY = OFF is the default setting.

Never mind!

Thanks for your help, Brian!

Brian Donahue

Thanks for following up.