Annoying error when trying to run SQL exported from db browser for sqlite

Question

I am simply trying to run a basic SQL script to recreate a database.

The database was initially created in SQLite, and I exported it using DB Browser for SQLite.

The start of the file looks like this:

BEGIN TRANSACTION;

CREATE TABLE "AspNetUsers" 
(
    `Id`    varchar(128) NOT NULL,
    `Email` varchar(256) DEFAULT NULL,
    `EmailConfirmed`    tinyint(1) NOT NULL,
    `PasswordHash`  longtext,
    `SecurityStamp` longtext,
    `PhoneNumber`   longtext,
    `PhoneNumberConfirmed`  tinyint(1) NOT NULL,
    `TwoFactorEnabled`  tinyint(1) NOT NULL,
    `LockoutEndDateUtc` datetime DEFAULT NULL,
    `LockoutEnabled`    tinyint(1) NOT NULL,
    `AccessFailedCount` int(11) NOT NULL,
    `UserName`  varchar(256) NOT NULL,
    `IsActivated`   tinyint(1) NOT NULL DEFAULT (0),
    `Organisation`  TEXT NOT NULL,
    PRIMARY KEY(`Id`)
);

I created a new db and when running the query in SSMS I get this annoying error

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '`'.

I tried deleting all the whitespace between the first ( and 'Id' but then I just get

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '`'.

I also tried replacing the `s with 's but with the same result....

I'm pretty sure the server I'm trying to execute this on is running SQL Server Express - not sure if that makes a difference

Why must life be so difficult?

@Corion Yes, I remoevd them all, also tried replacing them with ' and " and it just doesn't work.... — Bassie
– Bassie, Commented Oct 20, 2018 at 8:55
At least on MS SQL server, I get no such error after removing all the backticks: dbfiddle.uk/… - it doesn't know the longtext type, but that's different. — Corion
– Corion, Commented Oct 20, 2018 at 8:58
@Corion This makes no sense ffs... it feels like whenever I want to do the most simple thing I just get screwed over by some obscure computing rule... I think God hates me — Bassie
– Bassie, Commented Oct 20, 2018 at 8:59
How did you get "Incorrect syntax near '`'" if you replaced all the backticks? All of this is documented on the internet. There's no suprise here — Nick.Mc
– Nick.Mc, Commented Oct 20, 2018 at 12:02

Gordon Linoff · Accepted Answer · 2018-10-20 11:37:32Z

0

The code is rather specific to SQLite in several respects:

The use of backticks is non-standard.
Having a length for integer columns in non-standard.
text and longtext are non-standard.

The equivalent create table statement in SQL Server would be:

CREATE TABLE AspNetUsers (
    Id varchar(128) NOT NULL,
    Email varchar(256) DEFAULT NULL,
    EmailConfirmed tinyint NOT NULL,
    PasswordHash varchar(max),
    SecurityStamp varchar(max),
    PhoneNumber varchar(max),
    PhoneNumberConfirmed  tinyint NOT NULL,
    TwoFactorEnabled  tinyint NOT NULL,
    LockoutEndDateUtc datetime DEFAULT NULL,
    LockoutEnabled tinyint NOT NULL,
    AccessFailedCount int NOT NULL,
    UserName  varchar(256) NOT NULL,
    IsActivated   tinyint NOT NULL DEFAULT (0),
    Organisation  varchar(max) NOT NULL,
    PRIMARY KEY (Id)
);

Except for the varchar(max), this would be pretty standard for any database.

Some notes:

You probably don't need varchar(max) for any of these fields. Although you can use it, it looks awkward to have a phone number that could occupy megabytes of data.
You could probably replace the tinyints with bits.
DEFAULT NULL is redundant.

answered Oct 20, 2018 at 11:37

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Shawn Over a year ago

It's pretty non-standard for sqlite too. Looks more like a statement meant for MySQL with all the backticks. The only column type in the original that is a native sqlite one is the TEXT one.

Gordon Linoff Over a year ago

@shawn . . . I think you're right. SQLite does support backticks (for compatibility with MySQL). However, I don't think MySQL supports length arguments for numbers.

Shawn Over a year ago

They're formatting hints for desired display width, not storage size: dev.mysql.com/doc/refman/5.7/en/numeric-type-attributes.html (and completely meaningless in sqlite, of course)

Gordon Linoff Over a year ago

@Shawn . . . I take that back. SQLite does support the lengths, even though I don't see them documented: dbfiddle.uk/…. So, the create table statement is valid SQLite.

Shawn Over a year ago

They're ignored, and it makes a token effort to convert unknown type names to a sqlite type (so say VARCHAR(NN) is treated as TEXT). Details. (It is very lenient about what it accepts for column definitions)

Collectives™ on Stack Overflow

Annoying error when trying to run SQL exported from db browser for sqlite

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related