SQL command error and unexpected token:

bbinf

Hi,

We have recently upgraded to SQL 2008 R2 and when trying to compare a database against a backup file, I am regularly getting an error:

The following SQL command caused the error:

SELECT [field1], [field2], [field3], [field4], [field5]
FROM [dbo].[table1] ORDER BY [field1]

The log file shows errors like:


create default def_blank as ''

09:39:02.359|Error |SQL Compare Engine P|32 |line 1:88: unexpected token: ["create",<666>,line=1,col=88] [char=81]
09:39:02.359|Error |SQL Compare Engine P|32 |Parse error for:
/****** Object: Default dbo.def_today Script Date: 23/03/99 3:54:43 ******/
CREATE DEFAULT def_today AS getdate()

09:39:02.359|Error |SQL Compare Engine P|32 |line 1:90: unexpected token: ["CREATE",<666>,line=1,col=90] [char=83]
09:39:02.374|Error |SQL Compare Engine P|32 |Parse error for:
/****** Object: Default dbo.def_zero Script Date: 23/03/99 3:54:43 ******/
CREATE DEFAULT def_zero AS 0

09:39:02.374|Error |SQL Compare Engine P|32 |line 1:89: unexpected token: ["CREATE",<666>,line=1,col=89] [char=82]

I also upgraded to Data Compare 10, just to ensure if this is related to an issue in earlier version, but the same error repeats.

This error is only reported on two tables (biggest tables in terms of numbers of rows) for the database.

We have been successfully running the compare earlier when the databases were on SQL 2000.

Any suggestion on how to resolve this issue?

Regards
Samir

Chris Auckland

Thanks for your post.

It looks like the error is coming from the SQL Compare engine parsing one of your objects.

Could you try comparing the schemas using SQL Compare? It should give you a better error if it encounters errors reading the objects.

If it shows you which object it's having trouble with, could you post or email me the SQL for that object?

There might be a bug in the engine parsing some of the syntax you're using.

bbinf

Hi Chris,

Thanks for the response. We only bought the license for SQL Data Compare, hence do not have SQL Compare to verify the process. I will check if I can download a trial version to run this test.

Also, just in case it helps, the error only occurs when comparing against a backup file. Running the data compare between two databases works fine.

I will get back to you with the results from SQL Compare.

Regards
Samir

bbinf

Hi Chris,

I have run the comparision between the backup file and database using SQL Compare 10. This comparision has run successful without any errors messages.

The database contains commercial information for customers and I will have to seek permission from our CEO regarding providing a copy of the backup.

Does the fact that comparision runs properly using SQL Compare but reports error with Data Compare help in any way?

Regards
Samir

bbinf

Hi Chris,

Please advise how can I send you the backup file for testing? Size of the zip file is approx 200 MB.

Regards
Samir