search

ErikEJ / SqlCeToolbox

SQLite & SQL Server Compact Toolbox extension for Visual Studio, SSMS (and stand alone)
Other
839 stars 175 forks source link

SQL logic error: Unrecognized token: "0x" #875

Closed hydroculator closed 1 year ago

hydroculator commented 3 years ago

Hi,

When attempting to migrate a SQL database to SQLite, I receive the following error:

SQLite/SQL Server Compact Toolbox ErrorCode : 1 Message : SQL logic error unrecognized token: "0x" Result : Error

Steps to reproduce

Connect SQL database in VS Add data connection in SqlCeToolbox Select Migrate to SQLite... Select tables Select database file name Status changes to Importing Data Error occurs and database file is created with zero bytes.

Further technical details

Toolbox/Power Tools version: (found in About dialog - blue questionmark icon) Version 4.7.670.0 - more than 900,000 downloads

SQL Server Compact 4.0 in GAC - No SQL Server Compact 4.0 DbProvider - No

SQL Server Compact 4.0 DDEX provider - No SQL Server Compact 4.0 Simple DDEX provider - Yes

SQL Server Compact 3.5 in GAC - No SQL Server Compact 3.5 DbProvider - No

SQL Server Compact 3.5 DDEX provider - No

Sync Framework 2.1 SqlCe 3.5 provider - No

SQLite ADO.NET Provider included: 1.0.109.0 SQLite EF6 DbProvider in GAC - No System.Data.SQLite DDEX provider - No SQLite Simple DDEX provider - Yes

Database engine: SQL Server

Visual Studio or SSMS version: Visual Studio Enterprise 2019 16.8.1

hydroculator commented 3 years ago

After further investigation, I have found the following to be the offending item:

[columnName] varbinary(20) DEFAULT (0x) NOT NULL COLLATE NOCASE

Apparently something about the 0x default value is causing the crash.

ErikEJ commented 3 years ago

Wauw - are you able to change in the source database?

hydroculator commented 3 years ago

I'm sorry, I don't understand the question. Can you elaborate on 'change'? Change the DB structure? The default value? Thanks

ErikEJ commented 3 years ago

Change the default value ?

hydroculator commented 3 years ago

I'm looking into that right now. Seeing if removing the default value will cause any problems. Thanks

hydroculator commented 3 years ago

Changing the default value to a non-hex value is a workaround, but any hex data stored with an x throws this error.

ErikEJ commented 3 years ago

Any suggestions for a fix?

hydroculator commented 3 years ago

SQLite hex values are entered in this format:

x'0003FFFF'

I'm guessing that the values just need to be converted into that format prior to import.

ErikEJ commented 2 years ago

It looks like the 0x syntax is also supported, maybe it could just be replaced with 0x0 ??

https://www.sqlite.org/lang_expr.html

ErikEJ commented 1 year ago

https://docs.microsoft.com/en-us/sql/t-sql/data-types/constants-transact-sql?view=sql-server-ver16#binary-constants

Looks like 0x0 should be used iso 0x