Solved

Attaching SQL Express database via code (.NET) or Installshield

Posted on 2011-03-01
15
1,417 Views
Last Modified: 2012-05-11
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(strConn)
            Dim strCommand As String = "create database EPMNetDB ON (FILENAME = 'C:\MyData\MyDB.mdf') FOR ATTACH;"
            Try
                sql.Cmd.CommandType = CommandType.Text

                sql.ExecuteNonQuery(strCommand)
            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.
0
Comment
Question by:fujihuynh
  • 8
  • 7
15 Comments
 
LVL 40

Expert Comment

by:Vadim Rapp
Comment Utility
For this to work, your installation must install mydb.mdf into c:\mydata. Have you done that in the installation?

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]\MyCompany\Myproduct\MyDB.mdf') FOR ATTACH
0
 
LVL 40

Expert Comment

by:Vadim Rapp
Comment Utility
...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.
0
 

Author Comment

by:fujihuynh
Comment Utility
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.
0
 
LVL 40

Expert Comment

by:Vadim Rapp
Comment Utility
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.
0
 

Author Comment

by:fujihuynh
Comment Utility
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\VisualStudio\9.0\CLSID\{5A46C6DE-1FE4-43b8-8E1E-598AE65F184A}\Class      NAME NOT FOUND      Length: 144
99997      10:43:46.2940971      devenv.exe      2780      RegOpenKey      HKLM\SOFTWARE\Microsoft\VisualStudio\9.0\CLSID\{5A46C6DE-1FE4-43b8-8E1E-598AE65F184A}\InprocServer32      NAME NOT FOUND      Desired Access: Read
0
 
LVL 40

Expert Comment

by:Vadim Rapp
Comment Utility
This screenshot hopefully shows everything you need.
Capture-03-02-00001.png
0
 

Author Comment

by:fujihuynh
Comment Utility
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?

0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 40

Expert Comment

by:Vadim Rapp
Comment Utility
It looks like this mdf file already belongs to another existing database.
0
 

Author Comment

by:fujihuynh
Comment Utility
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??
0
 
LVL 40

Accepted Solution

by:
Vadim Rapp earned 500 total points
Comment Utility
Have you detached it after you ran it in enterprise manager?
0
 

Author Comment

by:fujihuynh
Comment Utility
Yes, I refreshed the databases and confirmed that it was no longer attached before running the code again.
0
 

Author Comment

by:fujihuynh
Comment Utility
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.
0
 

Author Closing Comment

by:fujihuynh
Comment Utility
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
0
 
LVL 40

Expert Comment

by:Vadim Rapp
Comment Utility
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.
0
 

Author Comment

by:fujihuynh
Comment Utility
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.
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now