CODEWITHSUNDEEP
c#sql-serverado.netsqlcommand
Awais Mahmood
Awais Mahmood

Reputation: 1336

Incorrect syntax near the keyword 'USE' error while creating database from Script

I am developing a multi tenant application so I need to create database from code for every new client. I have generated script of my schema from SSMS "Generate Script" utility, then I tried the following:

public static void CreateIfNotExist(string path) // the path where the script for creating database is located
{
    var db = System.Web.HttpContext.Current.Session["dbName"].ToString();
    using (var con = new SqlConnection("Data Source=.; uid=sa; pwd=password;"))
    {
        con.Open();

        var xmlStr = File.ReadAllText(path);
        var str = XElement.Parse(xmlStr);
        foreach (var item in str.Elements())
        {
             var query = "CREATE DATABASE " + db + Environment.NewLine +  "GO" + Environment.NewLine + "USE " + db + Environment.NewLine + "GO" + Environment.NewLine;
             var scriptToRun = File.ReadAllText(item.Value);
             query += scriptToRun;

             SqlCommand cmd = new SqlCommand(query, con);
             try
             {
                 cmd.ExecuteNonQuery();
             }
             catch (Exception ex)
             {
             }
        }

    }
}

And my script is:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING OFF
GO
CREATE TABLE [dbo].[ACOD](
    [id] [numeric](10, 0) NOT NULL,
    [serial] [numeric](10, 0) NULL,
    [name] [varchar](250) NULL,
 CONSTRAINT [PK_ACOD] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING OFF
GO
CREATE TABLE [dbo].[AGZH](
    [id] [numeric](10, 0) NOT NULL,
    [number] [numeric](10, 0) NULL,
    [date] [smalldatetime] NULL,
    [name] [varchar](50) NULL,
CONSTRAINT [PK_AGZH] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO

This is just a portion of script. In actual, I have 100+ tables with every table have more than 50 columns.

Now when I use the C# code written above it gave me the following exception:

Incorrect syntax near the keyword 'USE'. Database 'databaseName' does not exist. Make sure that the name is entered correctly.

But when I get the script from text visualizer in Visual Studio and execute it in SSMS, it runs successfully and created the new database name.

What am I doing wrong? Please help.

Upvotes: 2

Views: 2686

Answers (1)

Lasse V. Karlsen
Lasse V. Karlsen

Reputation: 391566

GO is not a legal command in T-SQL.

This command is handled by whatever program that you edit your script in, such as SQL Server Management Studio and is used as a delimiter to separate your big query into multiple smaller ones.

You cannot send the GO command to SQL Server, or you will get that error.

So basically you should use the GO commands to separate your query and execute only the bits inbetween, one bit at a time.

Here is the documentation of GO (Transact-SQL):

GO is not a Transact-SQL statement; it is a command recognized by the sqlcmd and osql utilities and SQL Server Management Studio Code editor.

Upvotes: 4

Related Questions