Red Gate forums :: View topic - Invalid syntax in CREATE TYPE statement
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Compare 10
SQL Compare 10 forum

Invalid syntax in CREATE TYPE statement

Search in SQL Compare 10 forum
Post new topic   Reply to topic
Jump to:  
Author Message
isme



Joined: 12 Jun 2012
Posts: 75
Location: Edinburgh

PostPosted: Wed Nov 07, 2012 11:19 am    Post subject: Invalid syntax in CREATE TYPE statement Reply with quote

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:

Quote:
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:
/*
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:
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?
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6589

PostPosted: Fri Nov 09, 2012 1:44 pm    Post subject: Reply with quote

Unfortunately, I cannot reproduce this problem. When I use your script, SQL Compare recreates the type correctly:
Code:
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.
Back to top
View user's profile Send private message
isme



Joined: 12 Jun 2012
Posts: 75
Location: Edinburgh

PostPosted: Fri Nov 09, 2012 2:50 pm    Post subject: Reply with quote

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:

PRIMARY KEY CLUSTERED ([id])
(
  [id] ASC
)WITH (IGNORE_DUP_KEY = OFF)
Back to top
View user's profile Send private message
isme



Joined: 12 Jun 2012
Posts: 75
Location: Edinburgh

PostPosted: Fri Nov 09, 2012 2:54 pm    Post subject: Reply with quote

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'.
Back to top
View user's profile Send private message
isme



Joined: 12 Jun 2012
Posts: 75
Location: Edinburgh

PostPosted: Fri Nov 09, 2012 3:12 pm    Post subject: Reply with quote

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!
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6589

PostPosted: Tue Nov 13, 2012 1:58 pm    Post subject: Reply with quote

Thanks for following up.
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group