Unable to create database programmatically

Helfdane

Hi there, I'm trying to create a database programmatically using .Net3.5 and C#. I have this nice SQL-script which works fine in the SSMS editor, but now to try it for real in C#. I learned that the GO-statements were only for the SSMS editor, so I removed those. (am I right on that one?). What am I missing here? Context: MSSQL 2008R2, VS2008, .Net3.5, WindowsXP. Below is my script. The error I get is the following:

Incorrect syntax near 'LOG'.\r\nDatabase 'dbName' does not exist. Make sure that the name is entered correctly.

The source I use to execute this script is here. (removed the try/catch/finally blocks etc)

SqlConnection conn = new SqlConnection(connectionString);
conn.Open();
string initDBQuery = string.Format(InstallationHelper.CreateDBScript(), settings.DatabaseLocation, settings.Database, settings.DatabaseUser, settings.DatabasePassword);
SqlCommand command = new SqlCommand(initDBQuery, conn);
command.ExecuteNonQuery();

This is the script (in C#-form):

        string script = 
        @"
        CREATE DATABASE \[{1}\] ON  PRIMARY ( NAME = N'{1}', FILENAME = N'{0}\\{1}.mdf' , SIZE = 3072KB , FILEGROWTH = 1024KB );
        
        LOG ON ( NAME = N'{1}\_log', FILENAME = N'{0}\\{1}\_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%);

        ALTER DATABASE \[{1}\] SET COMPATIBILITY\_LEVEL = 100;

        ALTER DATABASE \[{1}\] SET ANSI\_NULL\_DEFAULT OFF ;

        ALTER DATABASE \[{1}\] SET ANSI\_NULLS OFF ;

        ALTER DATABASE \[{1}\] SET ANSI\_PADDING OFF ;

        ALTER DATABASE \[{1}\] SET ANSI\_WARNINGS OFF ;

        ALTER DATABASE \[{1}\] SET ARITHABORT OFF ;

        ALTER DATABASE \[{1}\] SET AUTO\_CLOSE OFF ;

        ALTER DATABASE \[{1}\] SET AUTO\_CREATE\_STATISTICS ON ;

        ALTER DATABASE \[{1}\] SET AUTO\_SHRINK OFF ;

        ALTER DATABASE \[{1}\] SET AUTO\_UPDATE\_STATISTICS ON ;

        ALTER DATABASE \[{1}\] SET CURSOR\_CLOSE\_ON\_COMMIT OFF ;

        ALTER DATABASE \[{1}\] SET CURSOR\_DEFAULT  GLOBAL ;

        ALTER DATABASE \[{1}\] SET CONCAT\_NULL\_YIELDS\_NULL OFF ;

        ALTER DATABASE \[{1}\] SET NUMERIC\_ROUNDABORT OFF ;

        ALTER DATABASE \[{1}\] SET QUOTED\_IDENTIFIER OFF ;

        ALTER DATABASE \[{1}\] SET RECURSIVE\_TRIGGERS OFF ;

        ALTER DATABASE \[{1}\] SET  DISABLE\_BROKER ;

        ALTER DATABASE \[{1}\] SET AUTO\_UPDATE\_STATISTICS\_ASYNC OFF ;

        ALTER DATABASE \[{1}\] SET DATE\_CORRELATION\_OPTIMIZATION OFF ;

Wendelius

Helfdane wrote:

I learned that the GO-statements were only for the SSMS editor, so I removed those. (am I right on that one?).

Yap, that's correct.

Helfdane wrote:

Incorrect syntax near 'LOG'.\r\nDatabase 'dbName' does not exist. Make sure that the name is entered correctly.

The semicolon is used to end a statement so in your database creation statement the LOG portion is part of the CREATE DATABASE statement. Try removing the extra semicolon from the first row:

CREATE DATABASE [{1}] ON PRIMARY ( NAME = N'{1}', FILENAME = N'{0}\{1}.mdf' , SIZE = 3072KB , FILEGROWTH = 1024KB )
LOG ON ( NAME = N'{1}_log', FILENAME = N'{0}\{1}_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%);

Haven't tried to execute a script using SqlCommand but hopefully it'll work. If it doesn't, you should split the statements to separate executions. Just a thought: From error-handling point of view it could better to use separate executions.

The need to optimize rises from a bad design.My articles[^]

PIEBALDconsult

Yeah, what he said, plus I don't think the USE statement will work.

Roger Wright

A while back I was trying to do the same, and several helpful souls told me it can't be done. Well, that just gets my dander up, so I went ahead and did it:

private void btnCreateDatabase_Click(object sender, EventArgs e)
{
String str;
SqlConnection myConn = new SqlConnection
("Server=BAAL\\SQLEXPRESS2;Integrated security=SSPI ;database=master");
str = "CREATE DATABASE MyDatabase";
SqlCommand myCommand = new SqlCommand(str, myConn);
try
{
myConn.Open();
myCommand.ExecuteNonQuery();
MessageBox.Show("Database Created Successfully", "MyApp",
MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (System.Exception ex)
{
MessageBox.Show(ex.ToString(), "MyApp", MessageBoxButtons.OK,
MessageBoxIcon.Information);
}
finally
{
if (myConn.State == ConnectionState.Open)
{
myConn.Close();
}
}

I ran it once, then switched over to the server to check - sure enough, myDatabease was there, ready to be populated. Your app will certainly need more, but it works, and should get you started. :)

Will Rogers never met me.

Helfdane

You're a lifesaver! Thanx a million!

A good programmer is someone who always looks both ways before crossing a one-way street. (Doug Linder)

Helfdane

Thanks!

A good programmer is someone who always looks both ways before crossing a one-way street. (Doug Linder)

Wendelius

No problem :)

The need to optimize rises from a bad design.My articles[^]