There was an error parsing the query. [ Token line number] sql error while creating table

Hi guys im tryin to cr开发者_高级运维eate tables in sql compact.

Here is the query:

CREATE TABLE [dbo.A] 
(
    Id nvarchar(37) NOT NULL CONSTRAINT Info_PK PRIMARY KEY,
    ImportDate datetime NOT NULL        
);

CREATE TABLE [dbo.B]
(
    Id uniqueidentifier PRIMARY KEY,
        DeviceId smallint,

);

But it is giving error in the line where it is tryin to create table B.

---

Don't use dbo. Schemas are not supported in SQL CE. And you may need execute your queries separately.

Here is a similar problem and the solution: http://social.msdn.microsoft.com/Forums/en-US/sqlce/thread/d6f1db96-8724-4376-990e-3f6da18c2d08/

---

I would remove the comma after DeviceId smallint

CREATE TABLE [dbo.A]
( Id nvarchar(37) NOT NULL CONSTRAINT Info_PK PRIMARY KEY,             
ImportDate datetime NOT NULL
);

CREATE TABLE [dbo.B] ( Id uniqueidentifier PRIMARY KEY, DeviceId smallint
);

---

I googled this and it sounds like you are using SQL CE (for future reference it's always good to tell us what implementation of SQL you are using).

Brackets [] are not supported as delimiters in CE. Try removing those from your table names.

---

Despite the fact a batch with multiple query is working on SQL Server Management Studio, it won't work with SqlCeCommand.

You have to split your batch in multiple query (you can use a transaction to be sur all the query are executed or none). I recommand to use ExecuteNonQuery as it doesn't use cursor (which could make some error in a transaction).

You could use the following sample.

Using connexion As New SqlCeConnection(connexionstring)
            connexion.Open()
            Dim transaction As SqlCeTransaction = connexion.BeginTransaction()
            Try
                Dim batch As String = GetCommandText()
                For Each query In batch.Split(";")
                    If Not String.IsNullOrWhiteSpace(query) Then
                        Dim command As New SqlCeCommand(query, connexion, transaction)
                        command.ExecuteNonQuery()
                    End If
                Next
                transaction.Commit()
            Catch ex As Exception
                transaction.Rollback()
                Throw
            End Try
        End Using