Incorrect syntax error while data compare

Compares and synchronizes SQL database content.

Moderators: Chris Auckland, David Atkinson, richardjm, david connell

Incorrect syntax error while data compare

Postby reval_pradeep » Thu Jul 14, 2011 7:01 am

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] "
reval_pradeep
 
Posts: 3
Joined: Mon Jul 11, 2011 11:27 am

Postby peter.peart » Thu Jul 14, 2011 8:11 pm

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?
Peter Peart
Red Gate Software Ltd
+44 (0)870 160 0037 ext. 8569
1 866 RED GATE ext. 8569
peter.peart
Site Admin
 
Posts: 362
Joined: Tue Sep 02, 2008 9:58 am
Location: Top floor, RG towers with the cool kids


Return to SQL Data Compare Previous Versions

Who is online

Users browsing this forum: No registered users and 0 guests