CODEWITHSUNDEEP
c#parsingsql-server-ceexecutenonquery
Redzix
Redzix

Reputation: 188

There was an error parsing the query. How to resolve it?

I want to run sql statement which I frist read from .sql file.

I'm getting this error: {"There was an error parsing the query. [ Token line number = 13,Token line offset = 1,Token in error = ALTER ]"}

There is my sql statement in .sql file:

CREATE TABLE [Test]
(
   [Id] INT NOT NULL IDENTITY (1,1),
   [DatabaseVersion] NVARCHAR(20) NOT NULL,
   [Autorun] BIT,
   [CurrentCulture] NVARCHAR(10),
   [MailNotificationEnabled] BIT,
   [RefreshInterval] INT,
   [ModifiedDate] DATETIME NOT NULL,
   [schemat] NVARCHAR(255)
)

ALTER TABLE [Test] ADD CONSTRAINT [PK_Test] PRIMARY KEY ([Id])

UPDATE [AppConfig]
SET [DatabaseVersion] = '0.12'

Reading file:

string oldVersion = GetOldDatabaseVersion();
string sqlScript = "";
sqlScript = GetScriptFromAssembly(oldVersion, 
ConfigurationSettings.ValidDatabaseVersion);

ExecuteNonQuery(CommandType.Text, sqlScript);

ExecuteNonQuery method:

public int ExecuteNonQuery(CommandType type, string sql)
{
    using (SqlCeConnection connection = CreateConnection())
    {
        return ExecuteNonQuery(connection, type, sql);
    }
}

private int ExecuteNonQuery(SqlCeConnection connection, CommandType type, string sql)
{
    using (SqlCeCommand command = new SqlCeCommand())
    {
        command.Connection = connection;
        command.CommandType = type;
        command.CommandText = sql;

        return command.ExecuteNonQuery();
    }
}

I doesn't know how to resolve it. When I run script dirctly on db it works.

Upvotes: 0

Views: 1866

Answers (3)

ErikEJ
ErikEJ

Reputation: 41799

You must run each statement as a seperate command, you can use code like the helper function to seperate into commands if you seperate each command with GO:

https://github.com/ErikEJ/SqlCeToolbox/blob/master/src/API/Repositories/ServerDBRepository.cs#L639

As you can see I am using SqlCommandReaderStreamed from the DbUp package to do this

Upvotes: 1

Redzix
Redzix

Reputation: 188

Solved:

string sqlScript = GetScriptFromAssembly(GetOldDatabaseVersion(), ConfigurationSettings.ValidDatabaseVersion);

string[] scripts = sqlScript.Split(new string[] { @"/*$$*/" }, StringSplitOptions.None);

foreach(var script in scripts)
    ExecuteNonQuery(CommandType.Text, script);

Added delimiters to sql:

CREATE TABLE [Test]
(
   [Id] INT NOT NULL IDENTITY (1,1),
   [DatabaseVersion] NVARCHAR(20) NOT NULL,
   [Autorun] BIT,
   [CurrentCulture] NVARCHAR(10),
   [MailNotificationEnabled] BIT,
   [RefreshInterval] INT,
   [ModifiedDate] DATETIME NOT NULL,
   [schemat] NVARCHAR(255)
)

/*$$*/
ALTER TABLE [Test] ADD CONSTRAINT [PK_Test] PRIMARY KEY ([Id])

/*$$*/
UPDATE [AppConfig]
SET [DatabaseVersion] = '0.12'

Upvotes: 2

Surendra
Surendra

Reputation: 1

  • Please use the Go keyword before the Alter the table

Upvotes: -1

Related Questions