Options
SQL Syntax Error on Alter Table statement
kskistad
Posts: 2 Bronze 1
I am using SQL Server 2008 and comparing the database to the scripts folder. Whenever a table definition has an ALTER ABLE ... ADD CONSTRAINT ... after the table create portion of the script, SQL Compare throws an error and won't script it. Is this a SQL 2008 issue?
The line that errors is similar to this:
ALTER TABLE [dbo].[TableName] ADD CONSTRAINT [PJE_PK] PRIMARY KEY CLUSTERED ([KEY1], [Key2], [Key3]) WITH (DATA_COMPRESSION = PAGE)
GO
On a slightly related note, when will SQL Changeset support TFS 2008?
The line that errors is similar to this:
ALTER TABLE [dbo].[TableName] ADD CONSTRAINT [PJE_PK] PRIMARY KEY CLUSTERED ([KEY1], [Key2], [Key3]) WITH (DATA_COMPRESSION = PAGE)
GO
On a slightly related note, when will SQL Changeset support TFS 2008?
Comments
This issue is specific to SQL 2008, but it is really a problem with our script reader.
We are not handling the syntax to change/set the compression when a constraint is added. It is probably because when the constraint is created on the heap, it will inherit the compression state of the heap unless a different one is stated, and if a different state is chosen when the constraint is created it will change the compression on the heap. Therefore, when we compare the objects we only really need to check the compression state on the heap, as it will be the same as for the constraint. However, when it comes to the scripts, as in your case, it doesn't expect the compression to be set in the ALTER TABLE statement and reports a syntax error.
This should be fixed in the next major version of SQL Compare (v9). The bug tracking code for this is SC-4501.
If you move the 'WITH (DATA_COMPRESSION = PAGE)' on the constraint to the create table statement, the script should parse.
With regards TFS 2008, we don't technically support it, but as long as TFS 2008 is SCCI compliant, then it should work. You might need to install the SCCI provider plug-in.
I hope this is helpful.