Invalid syntax in CREATE TYPE statement

Compares and synchronizes SQL Server databases, backups and scripts.

Moderators: JonathanWatts, Chris Auckland, David Atkinson, eddie davis, Anu Deshpande, Michelle Taylor, alice.easey, james.billings, chengvoon.tong

Invalid syntax in CREATE TYPE statement

Postby isme » Wed Nov 07, 2012 11:19 am

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:

Image

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:

Code: Select all
/*
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:

Code: Select all
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?
isme
 
Posts: 81
Joined: Tue Jun 12, 2012 1:49 pm
Location: Edinburgh

Postby Brian Donahue » Fri Nov 09, 2012 1:44 pm

Unfortunately, I cannot reproduce this problem. When I use your script, SQL Compare recreates the type correctly:
Code: Select all
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.
Brian Donahue
 
Posts: 6670
Joined: Mon Aug 23, 2004 10:48 am

Postby isme » Fri Nov 09, 2012 2:50 pm

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:

Code: Select all
PRIMARY KEY CLUSTERED ([id])
(
  [id] ASC
)WITH (IGNORE_DUP_KEY = OFF)
isme
 
Posts: 81
Joined: Tue Jun 12, 2012 1:49 pm
Location: Edinburgh

Postby isme » Fri Nov 09, 2012 2:54 pm

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
 
Posts: 81
Joined: Tue Jun 12, 2012 1:49 pm
Location: Edinburgh

Postby isme » Fri Nov 09, 2012 3:12 pm

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!
isme
 
Posts: 81
Joined: Tue Jun 12, 2012 1:49 pm
Location: Edinburgh

Postby Brian Donahue » Tue Nov 13, 2012 1:58 pm

Thanks for following up.
Brian Donahue
 
Posts: 6670
Joined: Mon Aug 23, 2004 10:48 am


Return to SQL Compare 10

Who is online

Users browsing this forum: No registered users and 0 guests