Simple function to create SQL Database Fails: whats wrong?

I have a simple C# function that should create a new Microsoft SQL database called Database2.mdf.

My Problem: The function fails to create the database & I am unsure why? I have copied code from MSDN that demonstrates how to create a database but I am not sure my SqlConnection string or my SQLCommand strings are correct?

The error that occurs is:

{"A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)"}

My Code:

    public static string DEF_DB_NAME = "Database2";
    private bool create()
    {
        Console.WriteLine("populating data");
        bool res = false;
        SqlConnection myConn = new SqlConnection("Server=localhost;Integrated security=SSPI;database=master");
        string str = "CREATE DATABASE "+DEF_DB_NAME+" ON PRIMARY " +
            "(NAME = .SQLEXPRESS, " +
            "FILENAME = " + DEF_DB_NAME + ".mdf', " +
            "SIZE = 2MB, MAXSIZE = 10MB, FILEGROWTH = 10%) " +
            "LOG ON (NAME = " + DEF_DB_NAME + "_Log, " +
            "FILENAME = " + DEF_DB_NAME + "Log.ldf', " +
            "SIZE = 1MB, " +
            "MAXSIZE = 5MB, " +
            "FILEGROWTH = 10%)";

        SqlCommand myCommand = new SqlCommand(str, myConn);
        myConn.Open();  // ERROR OCCURS HERE
        myCommand.ExecuteNonQuery();
        insertDefData(myConn);
        myConn.Close();
        res = true;

        return res;
    }

---

Your problem is not that the command is failing and the db not getting created. You are not connecting to the db to run the command in the first place. There is something wrong with your connection string and probably the server name, (or the server itself).

According to your create db script the name of the server seems to be .SQLEXPRESS Adjust your connection string accordingly:

SqlConnection myConn = new SqlConnection(@"Server=.SQLEXPRESS ;Integrated security=SSPI;database=master");

Check here for extra details, but these are the things to check:

Is your server instance the default? If not, then it's named and the server name in the conn string should look something like: .SQLEXPRESS

Is your server configured to allow remote connections? (read the error message)

Optional connection strings to try:

Server=.;Database=master;Trusted_Connection=True;

Server=(local);Database=master;Trusted_Connection=True;

Server=127.0.0.1;Database=master;Trusted_Connection=True;

and... is your SQL Server running?

Also parse out the error:

A network-related or instance-specific error occurred while establishing a connection to SQL Server.

The server was not found or was not accessible.

Verify that the instance name is correct and that SQL Server is configured to allow remote connections.

(provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)"}

Don't assume that a command did not work. The error explicitly says that this is a connection issue and not a SQL syntax or other some such problem.

---

Your CREATE DATABASE statement should be:

        string str = "CREATE DATABASE " + DEF_DB_NAME + " ON PRIMARY " +
            "(NAME = N'" + DEF_DB_NAME + "_Dat', " +
            "FILENAME = 'C:somepath" + DEF_DB_NAME + ".mdf', " +
            "SIZE = 10MB, MAXSIZE = 10MB, FILEGROWTH = 10%) " +
            "LOG ON (NAME = N'" + DEF_DB_NAME + "_Log', " +
            "FILENAME = 'C:somepath" + DEF_DB_NAME + "Log.ldf', " +
            "SIZE = 1MB, " +
            "MAXSIZE = 5MB, " +
            "FILEGROWTH = 10%)";

Tested, and works 100%. (You were missing quotes in a few places)

You may need to increase the inital size of the data file depending on the size of your model database. (I had to increase your specified size > 3MB on my PC)

The default path for database files is C:Microsoft SQL ServerMSSQL10.MSSQLSERVERMSSQLDATA [If this is a production database, the log and data file should be on different (and appropriate RAID level) drives...]

链接地址: http://www.djcxy.com/p/60286.html

上一篇: 从数据库显示数据到标签

下一篇: 简单的函数来创建SQL数据库失败：最新错误？