| Author |
Message |
isme
Joined: 12 Jun 2012
Posts: 10
|
 Posted: Wed Nov 07, 2012 11:19 am Post subject: Invalid syntax in CREATE TYPE statement |
 |
|
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 |
|
 |
Brian Donahue
Joined: 23 Aug 2004
Posts: 6341
Location: Red Gate Software
|
| Posted: Fri Nov 09, 2012 1:44 pm Post subject: |
|
|
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.
_________________
Brian Donahue
Technical Support
Red Gate Software Ltd.
44 (0)870 160 0037 ext 8521
US and CAN 1-866-RED GATE ext 8521 |
|
| Back to top |
|
|
isme
Joined: 12 Jun 2012
Posts: 10
|
| Posted: Fri Nov 09, 2012 2:50 pm Post subject: |
|
|
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 |
|
|
isme
Joined: 12 Jun 2012
Posts: 10
|
| Posted: Fri Nov 09, 2012 2:54 pm Post subject: |
|
|
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 |
|
|
isme
Joined: 12 Jun 2012
Posts: 10
|
| Posted: Fri Nov 09, 2012 3:12 pm Post subject: |
|
|
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 |
|
|
Brian Donahue
Joined: 23 Aug 2004
Posts: 6341
Location: Red Gate Software
|
| Posted: Tue Nov 13, 2012 1:58 pm Post subject: |
|
|
Thanks for following up.
_________________
Brian Donahue
Technical Support
Red Gate Software Ltd.
44 (0)870 160 0037 ext 8521
US and CAN 1-866-RED GATE ext 8521 |
|
| Back to top |
|
|
|
