DataContext.CreateDatabase () says the file already exists, but it doesn't
It might be a Windows 7 problem, but the call
using (var context = new DataClassesDataContext())
{
if (!context.DatabaseExists())
{
context.CreateDatabase();
}
}
Results with the following error:
System.Data.SqlClient.SqlException was unhandled message = database 'C: \ Temp \ SmallBusinessManager.mdf' already exists. Please select a different database name. Source = .Net SqlClient Data Provider ErrorCode = -2146232060 Class = 16 LineNumber = 1 Number = 1801 Procedure = ""
Server = \. \ Pipe \ 952FCA9D-B4B6-4C \ TSQL \ Query State = 3 StackTrace: at System.Data.SqlClient.SqlConnection.OnError (SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection (SqlError exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning () at System.Data.SqlClient.TdsParser.Run (RunBehavior runBehavior, SqlCommand cmdHandler, SqlDatakReader dataStream, BulkRject SqlCommand.RunExecuteNonQueryTds (string methodName, Boolean async) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery (DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqltemClientSqlClient.SqlProvider.ExecuteCommand (String Command) at System.Data.Linq.SqlClient.SqlProvider.System.Data.Linq.Provider.IProvider.CreateDatabase () at System.Data.Linq.DataContext.CreateDatabase ()
However, I deleted the database files. What could be causing this? Are the files actually there but invisible? Something to do with transitions?
a source to share
Take Process Monitor and you should be able to see operations on the io file and why they fail. I find this is often the best and quickest way to get to the root of the problem. This is a great utility.
a source to share
Tracking error
So I also tried to create a file based database (named C: \ y \ bjdatamig \ IntermediateData.mdf ) by running the command DataContext.CreateDatabase()
and getting the same error.
I'm on Vista SP2 with SQLExpress 2008 and VS2008, and used Process Monitor like Josh suggested in his answer here. This led me to SQL Server writing to the error.log file, which in turn found the following block of relevant information (here's the full text in case anyone sees anything helpful among the messages):
2010-06-23 17:04:10.50 Server SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
2010-06-23 17:04:10.50 Server Detected 2 CPUs. This is an informational message; no user action is required.
2010-06-23 17:04:10.55 Server Perfmon counters for resource governor pools and groups failed to initialize and are disabled.
2010-06-23 17:04:10.55 Server Using dynamic lock allocation. Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node. This is an informational message only. No user action is required.
2010-06-23 17:04:10.65 Server Node configuration: node 0: CPU mask: 0x00000003 Active CPU mask: 0x00000003. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.
2010-06-23 17:04:10.69 spid7s Starting up database 'master'.
2010-06-23 17:04:10.79 spid7s 1 transactions rolled forward in database 'master' (1). This is an informational message only. No user action is required.
2010-06-23 17:04:10.91 spid7s 0 transactions rolled back in database 'master' (1). This is an informational message only. No user action is required.
2010-06-23 17:04:10.91 spid7s Recovery is writing a checkpoint in database 'master' (1). This is an informational message only. No user action is required.
2010-06-23 17:04:11.12 spid7s Error: 5598, Severity: 16, State: 2.
2010-06-23 17:04:11.12 spid7s FILESTREAM feature is not supported on user instances.
2010-06-23 17:04:11.12 spid7s FILESTREAM: effective level = 0, configured level = 0, file system access share name = 'SQLEXPRESS'.
2010-06-23 17:04:11.25 spid7s SQL Trace ID 1 was started by login "sa".
2010-06-23 17:04:11.31 spid7s Starting up database 'mssqlsystemresource'.
2010-06-23 17:04:11.33 spid7s The resource database build version is 10.00.2531. This is an informational message only. No user action is required.
2010-06-23 17:04:11.90 spid9s Starting up database 'model'.
2010-06-23 17:04:11.91 Server Server local connection provider is ready to accept connection on [ \\.\pipe\95B6C915-3DB7-46\tsql\query ].
2010-06-23 17:04:11.94 Server Dedicated administrator connection support was not started because it is disabled on this edition of SQL Server. If you want to use a dedicated administrator connection, restart SQL Server using the trace flag 7806. This is an informational message only. No user action is required.
2010-06-23 17:04:11.95 spid7s Server name is 'JOHN-W500\95B6C915-3DB7-46'. This is an informational message only. No user action is required.
2010-06-23 17:04:12.22 spid7s Starting up database 'msdb'.
2010-06-23 17:04:12.27 Server The SQL Server Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x5, state: 4. Failure to register an SPN may cause integrated authentication to fall back to NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies.
2010-06-23 17:04:12.27 Server SQL Server is now ready for client connections. This is an informational message; no user action is required.
2010-06-23 17:04:12.60 spid9s Clearing tempdb database.
2010-06-23 17:04:15.49 spid9s Starting up database 'tempdb'.
2010-06-23 17:04:16.27 spid12s The Service Broker protocol transport is disabled or not configured.
2010-06-23 17:04:16.27 spid12s The Database Mirroring protocol transport is disabled or not configured.
2010-06-23 17:04:16.36 spid12s Service Broker manager has started.
2010-06-23 17:04:16.37 spid7s Recovery is complete. This is an informational message only. No user action is required.
2010-06-23 17:04:17.84 Logon Error: 15350, Severity: 16, State: 1.
2010-06-23 17:04:17.84 Logon An attempt to attach an auto-named database for file C:\y\bjdatamig\IntermediateData.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.
2010-06-23 17:31:29.89 Logon Error: 15350, Severity: 16, State: 1.
2010-06-23 17:31:29.89 Logon An attempt to attach an auto-named database for file C:\y\bjdatamig\IntermediateData.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.
2010-06-23 17:33:07.09 Logon Error: 15350, Severity: 16, State: 1.
2010-06-23 17:33:07.09 Logon An attempt to attach an auto-named database for file C:\y\bjdatamig\IntermediateData.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.
2010-06-23 17:49:53.53 Logon Error: 15350, Severity: 16, State: 1.
2010-06-23 17:49:53.53 Logon An attempt to attach an auto-named database for file C:\y\bjdatamig\IntermediateData.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.
2010-06-23 18:01:02.74 Logon Error: 15350, Severity: 16, State: 1.
2010-06-23 18:01:02.74 Logon An attempt to attach an auto-named database for file C:\y\bjdatamig\IntermediateData.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.
2010-06-23 18:02:50.92 Logon Error: 15350, Severity: 16, State: 1.
2010-06-23 18:02:50.92 Logon An attempt to attach an auto-named database for file C:\y\bjdatamig\IntermediateData.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.
2010-06-23 18:04:06.31 Logon Error: 15350, Severity: 16, State: 1.
2010-06-23 18:04:06.31 Logon An attempt to attach an auto-named database for file C:\y\bjdatamig\IntermediateData.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.
2010-06-23 18:06:41.22 Logon Error: 15350, Severity: 16, State: 1.
2010-06-23 18:06:41.22 Logon An attempt to attach an auto-named database for file C:\y\bjdatamig\IntermediateData.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.
My SQL log file is located at:
C:\Users\John\AppData\Local\Microsoft\Microsoft SQL Server Data\SQLEXPRESS\error.log
It's important to note that in my situation this indicates that custom instances do not support a "feature", apparently about file streaming, and of course CreateDatabase is trying to write to the filesystem ...
2010-06-23 17:04:11.12 spid7s Error: 5598, Severity: 16, State: 2.
2010-06-23 17:04:11.12 spid7s FILESTREAM feature is not supported on user instances.
... also my connection string in APP.CONFIG indicates "Custom Instance" ...
<add connectionString="Data Source=JOHN-W500\SQLEXPRESS;AttachDbFilename=C:\y\bjdatamig\IntermediateData.mdf;Integrated Security=True;User Instance=True" name="IntermediateData" providerName="System.Data.SqlClient" />
Decision
I changed the connection string from
;Integrated Security=True;
to
;Integrated Security=False;
The database was created on the file system.
Why didn't I have to change the Custom Instance setting? Well, I'm not sure. In fact, before I started getting this error code it worked, but I haven't tried it in a month. My guess is maybe a system update or change on SQL Server. Therefore, although fixed, it is still a bit of a mystery.
a source to share
You need to delete the following folder and restart your computer to remove the old user instances. If you get an error stating that files are in use, you can "Close handle" to their Process Explorer .
For XP:
C:\Documents and Settings\username\Local Settings\Application Data\Microsoft\Microsoft SQL Server Data\SQLEXPRESS
For Vista:
C:\Users\username\AppData\Local\Microsoft\Microsoft SQL Server Data\SQLEXPRESS
For technical details about custom instances, see the article titled "SQL Server 2005 Express Edition User Instances."
a source to share