fujihuynh
asked on
Attaching SQL Express database via code (.NET) or Installshield
I have a SQL Express database that I need to include with an application. I have created an Installshield package with SQL Express 2005 as a prerequisite.
Now I want to be able to attach a SQL Express database prior to the application running.
Rather than doing this via an InstallShield script of which I have very little knowledge of, I thought I could create a connection to the database when the application runs and run the SQL command:
create database MyDB ON (FILENAME = 'C:\MyData\MyDB.mdf') FOR ATTACH;
(I have verified that running that SQL in Management Studio successfully attaches the database).
However I am getting an error:
"Unable to open the physical file "C:\MyData\MyDB.mdf". Operating system error 32: "32(error not found)"."
My VB.NET code is:
Dim sql As New CommonTools.SQL.SQLObjects
Dim strCommand As String = "create database EPMNetDB ON (FILENAME = 'C:\MyData\MyDB.mdf') FOR ATTACH;"
Try
sql.Cmd.CommandType = CommandType.Text
sql.ExecuteNonQuery(strCom
Catch ex As Exception
MessageBox.Show(ex)
Finally
sql.Close()
End Try
The connection to the database is fine, the error only occurs when trying to execute the SQL
Any suggestions regarding this error, or a better way of attaching a SQL Express database at runtime, or even a way to attach the database as part of the InstallShield process.
Now I want to be able to attach a SQL Express database prior to the application running.
Rather than doing this via an InstallShield script of which I have very little knowledge of, I thought I could create a connection to the database when the application runs and run the SQL command:
create database MyDB ON (FILENAME = 'C:\MyData\MyDB.mdf') FOR ATTACH;
(I have verified that running that SQL in Management Studio successfully attaches the database).
However I am getting an error:
"Unable to open the physical file "C:\MyData\MyDB.mdf". Operating system error 32: "32(error not found)"."
My VB.NET code is:
Dim sql As New CommonTools.SQL.SQLObjects
Dim strCommand As String = "create database EPMNetDB ON (FILENAME = 'C:\MyData\MyDB.mdf') FOR ATTACH;"
Try
sql.Cmd.CommandType = CommandType.Text
sql.ExecuteNonQuery(strCom
Catch ex As Exception
MessageBox.Show(ex)
Finally
sql.Close()
End Try
The connection to the database is fine, the error only occurs when trying to execute the SQL
Any suggestions regarding this error, or a better way of attaching a SQL Express database at runtime, or even a way to attach the database as part of the InstallShield process.
...just realized that you create database, rather than attach an existing file. Still, you have to create the folder c:\mydata, and make sure it has the permissions to create files in it. All considerations from the previous comment still apply.
ASKER
I believe CREATE DATABASE... FOR ATTACH is the correct way of attaching a database
http://msdn.microsoft.com/en-us/library/ms176061.aspx
Yes both the directory and database files are in the correct location.
If I run the SQL from within Enterprise Manager then the database attaches successfully and I can query it but when I execute the SQL from within .NET I get the "Unable to open the physical file "C:\MyData\MyDB.mdf". Operating system error 32: "32(error not found)"." message.
The SQL Express database is installed locally on my PC and that is where the code is being run from.
http://msdn.microsoft.com/en-us/library/ms176061.aspx
Yes both the directory and database files are in the correct location.
If I run the SQL from within Enterprise Manager then the database attaches successfully and I can query it but when I execute the SQL from within .NET I get the "Unable to open the physical file "C:\MyData\MyDB.mdf". Operating system error 32: "32(error not found)"." message.
The SQL Express database is installed locally on my PC and that is where the code is being run from.
Check the credential the application runs under. Are you running it yourself, or maybe it's a service, or by web server?
You can run process monitor (or old filemon), and find out what's going on.
You can run process monitor (or old filemon), and find out what's going on.
ASKER
I am running the application myself in Visual Studio and debugging the application. It gets to the ExecNonQuery method and then throws the exception.
I'm running proces monitor but I'm not sure what I should be looking out for.
The first results which are not 'SUCCESS' are e.g.:
99996 10:43:46.2940705 devenv.exe 2780 RegQueryValue HKLM\SOFTWARE\Microsoft\Vi
99997 10:43:46.2940971 devenv.exe 2780 RegOpenKey HKLM\SOFTWARE\Microsoft\Vi
I'm running proces monitor but I'm not sure what I should be looking out for.
The first results which are not 'SUCCESS' are e.g.:
99996 10:43:46.2940705 devenv.exe 2780 RegQueryValue HKLM\SOFTWARE\Microsoft\Vi
99997 10:43:46.2940971 devenv.exe 2780 RegOpenKey HKLM\SOFTWARE\Microsoft\Vi
This screenshot hopefully shows everything you need.
Capture-03-02-00001.png
Capture-03-02-00001.png
ASKER
Thanks for the screenshot, that was very useful.
So in ProcMon these are the records::
18 13:24:59.9939672 sqlservr.exe 1332 CreateFile C:\MyData\MyDB.mdf NAME INVALID Desired Access: Read Attributes, Synchronize, Disposition: Open, Options: Synchronous IO Non-Alert, Open For Backup, Open Reparse Point, Attributes: N, ShareMode: Read, Write, AllocationSize: n/a, Impersonating: MYNETWORK\myaccount
19 13:25:00.0701546 sqlservr.exe 1332 CreateFile C:\MyData\MyDB.mdf SHARING VIOLATION Desired Access: Generic Read/Write, Disposition: Open, Options: Synchronous IO Non-Alert, Non-Directory File, Open No Recall, Attributes: N, ShareMode: Read, AllocationSize: n/a, Impersonating: MYNETWORK\myaccount
20 13:25:00.0706974 sqlservr.exe 1332 QueryOpen C:\MyData\MyDB.mdf SUCCESS CreationTime: 22/12/2010 11:04:50, LastAccessTime: 01/03/2011 16:59:58, LastWriteTime: 01/03/2011 16:59:58, ChangeTime: 01/03/2011 16:59:58, AllocationSize: 22,478,848, EndOfFile: 22,478,848, FileAttributes: A
21 13:25:00.0716043 sqlservr.exe 1332 CreateFile C:\MyData\MyDB.mdf SHARING VIOLATION Desired Access: Generic Read/Write, Write DAC, Write Owner, Disposition: Open, Options: Write Through, Non-Directory File, Random Access, Open No Recall, Attributes: N, ShareMode: None, AllocationSize: n/a, Impersonating: MYNETWORK\myaccount
22 13:25:00.1656649 sqlservr.exe 1332 CreateFile C:\MyData\MyDB.mdf SHARING VIOLATION Desired Access: Generic Read/Write, Write DAC, Write Owner, Disposition: Open, Options: Write Through, Non-Directory File, Random Access, Open No Recall, Attributes: N, ShareMode: None, AllocationSize: n/a, Impersonating: MYNETWORK\myaccount
23 13:25:00.2604633 sqlservr.exe 1332 CreateFile C:\MyData\MyDB.mdf SHARING VIOLATION Desired Access: Generic Read/Write, Write DAC, Write Owner, Disposition: Open, Options: Write Through, Non-Directory File, Random Access, Open No Recall, Attributes: N, ShareMode: None, AllocationSize: n/a, Impersonating: MYNETWORK\myaccount
24 13:25:00.3555501 sqlservr.exe 1332 CreateFile C:\MyData\MyDB.mdf SHARING VIOLATION Desired Access: Generic Read/Write, Write DAC, Write Owner, Disposition: Open, Options: Write Through, Non-Directory File, Random Access, Open No Recall, Attributes: N, ShareMode: None, AllocationSize: n/a, Impersonating: MYNETWORK\myaccount
Does that provide any clues for you?
So in ProcMon these are the records::
18 13:24:59.9939672 sqlservr.exe 1332 CreateFile C:\MyData\MyDB.mdf NAME INVALID Desired Access: Read Attributes, Synchronize, Disposition: Open, Options: Synchronous IO Non-Alert, Open For Backup, Open Reparse Point, Attributes: N, ShareMode: Read, Write, AllocationSize: n/a, Impersonating: MYNETWORK\myaccount
19 13:25:00.0701546 sqlservr.exe 1332 CreateFile C:\MyData\MyDB.mdf SHARING VIOLATION Desired Access: Generic Read/Write, Disposition: Open, Options: Synchronous IO Non-Alert, Non-Directory File, Open No Recall, Attributes: N, ShareMode: Read, AllocationSize: n/a, Impersonating: MYNETWORK\myaccount
20 13:25:00.0706974 sqlservr.exe 1332 QueryOpen C:\MyData\MyDB.mdf SUCCESS CreationTime: 22/12/2010 11:04:50, LastAccessTime: 01/03/2011 16:59:58, LastWriteTime: 01/03/2011 16:59:58, ChangeTime: 01/03/2011 16:59:58, AllocationSize: 22,478,848, EndOfFile: 22,478,848, FileAttributes: A
21 13:25:00.0716043 sqlservr.exe 1332 CreateFile C:\MyData\MyDB.mdf SHARING VIOLATION Desired Access: Generic Read/Write, Write DAC, Write Owner, Disposition: Open, Options: Write Through, Non-Directory File, Random Access, Open No Recall, Attributes: N, ShareMode: None, AllocationSize: n/a, Impersonating: MYNETWORK\myaccount
22 13:25:00.1656649 sqlservr.exe 1332 CreateFile C:\MyData\MyDB.mdf SHARING VIOLATION Desired Access: Generic Read/Write, Write DAC, Write Owner, Disposition: Open, Options: Write Through, Non-Directory File, Random Access, Open No Recall, Attributes: N, ShareMode: None, AllocationSize: n/a, Impersonating: MYNETWORK\myaccount
23 13:25:00.2604633 sqlservr.exe 1332 CreateFile C:\MyData\MyDB.mdf SHARING VIOLATION Desired Access: Generic Read/Write, Write DAC, Write Owner, Disposition: Open, Options: Write Through, Non-Directory File, Random Access, Open No Recall, Attributes: N, ShareMode: None, AllocationSize: n/a, Impersonating: MYNETWORK\myaccount
24 13:25:00.3555501 sqlservr.exe 1332 CreateFile C:\MyData\MyDB.mdf SHARING VIOLATION Desired Access: Generic Read/Write, Write DAC, Write Owner, Disposition: Open, Options: Write Through, Non-Directory File, Random Access, Open No Recall, Attributes: N, ShareMode: None, AllocationSize: n/a, Impersonating: MYNETWORK\myaccount
Does that provide any clues for you?
It looks like this mdf file already belongs to another existing database.
ASKER
But if I run the SQL in a query window in Enterprise Manager, then the database attaches successfully without any problems so it can't already be attached??
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Yes, I refreshed the databases and confirmed that it was no longer attached before running the code again.
ASKER
Sorry, I tried to attach the database again using Enterprise Manager just now and got the same error as reported by VS.
I restored the database files from a backup and retried and it now works.
So corruption of the database files appears to be the cause of this problem.
Thanks for all your help.
I restored the database files from a backup and retried and it now works.
So corruption of the database files appears to be the cause of this problem.
Thanks for all your help.
ASKER
Trying to diagnose the problem helped to eliminate possible cuases which eventually led to the answer as a result of trying out a different suggested solution
hmm... "sharing violation" usually means that some other process is already using the file. Most likely it was.
Also note that process monitor shows "Impersonating: MYNETWORK\myaccount" in every line. This is related to the permissions I mentioned before. I highly recommend to find a better location than c:\mydata - best under all user's application data.
Also note that process monitor shows "Impersonating: MYNETWORK\myaccount" in every line. This is related to the permissions I mentioned before. I highly recommend to find a better location than c:\mydata - best under all user's application data.
ASKER
I have several named SQL Express instances and several very similar named directories containing the SQL Express .mdf and .ldf files so it is possible that I had mistyped the path.
I was just using C:\MyData as a substitute for the actual real life path.
Actually now that I've checked I have a SQL Express 2008 instance with a database attached with the same name so I think you are correct and that was the cause of the problem.
My code points to a path with a slightly different variation - spaces in the name.
I will take your advice though and use AppData instead.
I was just using C:\MyData as a substitute for the actual real life path.
Actually now that I've checked I have a SQL Express 2008 instance with a database attached with the same name so I think you are correct and that was the cause of the problem.
My code points to a path with a slightly different variation - spaces in the name.
I will take your advice though and use AppData instead.
Note that this location is not compliant with Microsoft standard practices, and you are likely to experience trouble with permissions, especially on Vista and Windows 7. The recommended location to place database files is under %appdata%. If you want to do this from the application, the application will have to find out the environment variable, and expand it to the path. If you do it with Installshield script, you can use placeholders for the paths, and Installshield will expand them prior to executing; so in your script you can put this:
create database EPMNetDB ON (FILENAME = '[CommonAppDataFolder]\MyC