Hi,
I am trying some example code in Visual Studio 2005 (C#) to create a SQL Server 2005 Express Edition database, but the code is falling over in the try/catch statements of the following code:
private voidMySqlTest(){
String str;
SqlConnection myConn = new SqlConnection("Server=localhost;Integrated security=SSPI;database=master");
str = "CREATE DATABASE MyDatabase ON PRIMARY " +
"(NAME = MyDatabase_Data, "+
"FILENAME = 'C:\\MyDatabaseData.mdf', " +
"SIZE = 2MB, MAXSIZE = 10MB, FILEGROWTH = 10%) " +
"LOG ON (NAME = MyDatabase_Log, "
"FILENAME = 'C:\\MyDatabaseLog.ldf', " +
"SIZE = 1MB, " +
"MAXSIZE = 5MB, " +
"FILEGROWTH = 10%)";
SqlCommand myCommand = new SqlCommand(str, myConn);
try
{
myConn.Open();
myCommand.ExecuteNonQuery();
MessageBox.Show("DataBase is Created Successfully,"MyProgram",MessageBoxButtons.OK,MessageBoxIcon.Information);
}
catch (System.Exception ex)
{
MessageBox.Show(ex.ToString(), "MyProgram",MessageBoxButtons.OK,MessageBoxIcon.Information);
}
finally
{ if (myConn.State == ConnectionState.Open)
{
myConn.Close();
}
}
}
This is the error I get when the 'myConn.Open();' statement is executed:
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server
Can someone please help!
Thank You!
SQL Server test code returning an error.
Global
Hi Bill,
Many thanks for your comments, it made me think of something I hadn't tried which worked (I got my PC's name and the SQL Server string by running SSMS and copying the connection string from the startup screen). Now the connection to SQL Server 2005 is working.
I fixed another error as the new error message was that the database had to be a minimum of 3MB in size and the example code had a 2MB minimum size. I quickly upped the minimum to 10MB and the maximum size of 100MB.
Now, with these errors fixed, the next error I am encountering is coming from the creation of the file name for the database. In debug mode, I viewed the string that is passed to SQL Server 2005 which creates the database (in HTML mode). I copied that string and went into SSMS to test the string out. I pasted in the code and the colors (I'll use the American spelling here) for the syntax were correct. I highlighted the statement and hit F5 to execute the statement to see what I got. This is the error it reported back:
Msg 1802, Level 16, State 4, Line 1
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.
Msg 5123, Level 16, State 1, Line 1
CREATE FILE encountered operating system error 5(error not found) while attempting to open or create the physical file 'C:\MyDatabaseData.mdf'.
The string which is being executed is formed as follows:
CREATE
DATABASE MyDatabase ON PRIMARY (NAME = MyDatabase_Data, FILENAME = 'C:\MyDatabaseData.mdf', SIZE = 10MB, MAXSIZE = 100MB, FILEGROWTH = 10%) LOG ON (NAME = MyDatabase_Log, FILENAME = 'C:\MyDatabaseLog.ldf', SIZE = 1MB, MAXSIZE = 5MB, FILEGROWTH = 10%) This has made me a little confused this error. For some reason it cannot create the file for one reason or another.Once I get over this hurdle locally on my work PC, it will be time to ask our I.T. guys who manage our network server to let me try it out over a network environment!
Sean.
Mark Ingram
Hi Zlatko
I tried your method but it didn't work. Reading through Bill's previous comments (and his blog), it turned out to be a connection string problem. However, I think your method is geared towards earlier versions of SQL Server (I'm using SQL Server 2005 and the .NET 2.0 framework).
Thanks for trying.
moinj
Replace the Server value with ‘.’ (dot) and try again:
“Data Source=.;Integrated Security=SSPI;”
RvA
OK, I seem to have fixed my problem with not being able to create files. it turned out that even though my username is set up as an Administrator account, there were some settings under 'Users' which stopped SQL from being able to execute and create files. This must have been an oversight on our Networking guys who set my PC up!
Thanks for all your help, guys!
Sean
IMauricio
This is a very common error. The error message (while verbose) does not always tell what's really wrong.
I suggest taking a look at my blog entry that lists the most common reasons for not being able to connect. See http://betav.com/BLOG/billva/archive/2006/01/17/3511.aspx