Incorrect syntax error while data compare

reval_pradeep

Error occured in the following scenario :-

Use [master]
Go

---

1

---

Create Database [TestDB1]
Go
use [TestDB1]
Go
Drop Table [tbAssumptionElementspecref]
Go

CREATE TABLE [dbo].[tbAssumptionElementspecref](
[Invcoid] [int] NOT NULL,
[InvUniqBizDivId] [int] NOT NULL,
[SpecElementID] [bigint] NOT NULL,
[Description] [varchar](1000) COLLATE Arabic_100_CI_AS NULL,
[ElementSpecRefTaxonomy] [varchar](550) COLLATE Arabic_100_CI_AS NOT NULL,
[SubFormId] [int] NOT NULL,
[DetailLevel] [bigint] NULL,
[ChangeScope] [int] NULL,
[Formula] [varchar](max) COLLATE Arabic_100_CI_AS NULL,
[SrIdx] [int] NOT NULL,
[ParentSpecElementID] [int] NULL,
[PresentationParentSpecElementID] [bigint] NULL,
[Unit] [nvarchar](50) COLLATE Arabic_100_CI_AS NULL,
[BaseCurrencyCode] [nvarchar](3) COLLATE Arabic_100_CI_AS NULL,
[DataTypeId] [int] NULL,
[PeriodType] [varchar](50) COLLATE Arabic_100_CI_AS NULL,
CONSTRAINT [PK_tbAssumptionelementspecref] PRIMARY KEY NONCLUSTERED
(
[SpecElementID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)

---

2

---

Go
Create Database [TestDB2]
Go
Use [TestDB2]
Go
Drop Table [tbAssumptionElementspecref]
Go
CREATE TABLE [dbo].[tbAssumptionElementspecref](
[Invcoid] [int] NOT NULL,
[InvUniqBizDivId] [int] NOT NULL,
[SpecElementID] [bigint] NOT NULL,
[Description] [varchar](1000) COLLATE Arabic_100_CI_AS NULL,
[ElementSpecRefTaxonomy] [varchar](550) COLLATE Arabic_100_CI_AS NOT NULL,
[SubFormId] [int] NOT NULL,
[DetailLevel] [bigint] NULL,
[ChangeScope] [int] NULL,
[Formula] [varchar](max) COLLATE Arabic_100_CI_AS NULL,
[SrIdx] [int] NOT NULL,
[ParentSpecElementID] [int] NULL,
[PresentationParentSpecElementID] [bigint] NULL,
[Unit] [nvarchar](50) COLLATE Arabic_100_CI_AS NULL,
[BaseCurrencyCode] [nvarchar](3) COLLATE Arabic_100_CI_AS NULL,
[DataTypeId] [int] NULL,
[PeriodType] [varchar](50) COLLATE Arabic_100_CI_AS NULL,
CONSTRAINT [PK_tbAssumptionelementspecref] PRIMARY KEY NONCLUSTERED
(
[SpecElementID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)

while I'm comparing these 2 tables I'm getting following errors

" The following error message was returned from the SQL Server:

[102] Incorrect syntax near '-'.
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

The following SQL command caused the error:

SELECT [SpecElementID], [Invcoid], [InvUniqBizDivId], convert(nvarchar(1000),[Description]) COLLATE Latin1_General_BIN , convert(nvarchar(550),[ElementSpecRefTaxonomy]) COLLATE Latin1_General_BIN , [SubFormId], [DetailLevel], [ChangeScope], convert(nvarchar(-1),[Formula]) COLLATE Latin1_General_BIN , [SrIdx], [ParentSpecElementID], [PresentationParentSpecElementID], [Unit], [BaseCurrencyCode], [DataTypeId], convert(nvarchar(50),[PeriodType]) COLLATE Latin1_General_BIN
FROM [dbo].[tbAssumptionElementspecref] WITH (NOLOCK) ORDER BY [SpecElementID] "

peter.peart

Thanks for your post. I've used these scripts to create 2 DB's and then pumped one DB full of data using SQL Data Generator.

I'm using SQL Data Compare version 9.0.0.117 and SQL Server 2008, and no errors at all were encountered.

Again, this is likely to be specific to your data assuming you are running the latest version of SQL Data Compare?