none
How to deploy SQL Server 2005 Express Edition database with Visual Studio 2008 application using Windows Installer.

    Question

  • I need to deploy an empty SQL Server 2005 Express Edition database and tables with my first Visual Studio 2008 project using Windows Installer. The database can be seen using SQL Server 2005 Express Edition Management Studio.  Also, the database tables appear in Visual Studio 2008 dataset designer. 

    I've tried to use an installer class with custom actions to create the database from a backup of the database but I got error "3 (The system cannot find the path specified.) ...SQL.HDR" due to the database containing a full-text catalog.

    I've tried to create the database and tables during installation using custom actions and the SQL Server user instance but the database cannot be found anywhere on the PC even though subsequent installs say the database exists.

    I added the code to create the database to the load event of the main form and the database was created in the SQL Server user instance folder.

    What is the best way to create the SQL Server 2005 Express Edition database and tables during installation of the application using Windows Installer?  Any help would greatly be appreciated.  Thanks in advance. 

    Friday, January 21, 2011 6:06 PM

Answers

  • Hi isomcsa,

     

    The xml content is in the app.config file as i has mentioned in my first reply, you can see it at last of that post.

     

    If you have any concern, please feel free to let me know.

    Best wishes,


    Mike [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by isomcsa Monday, January 31, 2011 11:14 PM
    Thursday, January 27, 2011 10:25 AM
    Moderator
  • I ended up having to create the database and tables programmatically during the install process to make this work.
    • Marked as answer by isomcsa Wednesday, July 27, 2011 12:52 AM
    Wednesday, July 27, 2011 12:52 AM

All replies

  • Hi isomcsa,

     

    We can implement auto attach database just use the connection string following.

     

    1)       Add a folder named ”DB” to the project, and copy the database file into it after you detach it from your sql server.

    2)       Change your connection string like this:

        <connectionStrings>

            <add name="WindowsFormsApplication1.Properties.Settings.BabakConnectionString"

                connectionString="Data Source=.;AttachDbFilename=|DataDirectory|\DB\Babak.mdf;Initial Catalog=test;Integrated Security=True"

                providerName="System.Data.SqlClient" />

        </connectionStrings>

     

    3)       After these, the database file will auto attach to the sql server when you run your application, and the database name is “test”, you can open the sql server management studio to find it.

     

    In addition, you can find the connection in the app.config file through the solution explorer in vs2010. And you also can find this file under your project. With these information, you can find it and modify it.

     

     

    If there's any concern, please feel free to let me know.

     

    Have a nice day!


    Mike [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, January 24, 2011 8:07 AM
    Moderator
  • This is my first Visual Studio 2008 Standard Edition project.  Therefore, I've been learning everything as I've been developing this project.  The connection string you've given seem to be XML.  I'm using C#.  Should I add the string as given?

    isomcsa

     

    Monday, January 24, 2011 4:19 PM
  • Hi isomcsa,

     

    The xml content is in the app.config file as i has mentioned in my first reply, you can see it at last of that post.

     

    If you have any concern, please feel free to let me know.

    Best wishes,


    Mike [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by isomcsa Monday, January 31, 2011 11:14 PM
    Thursday, January 27, 2011 10:25 AM
    Moderator
  • I made the modifications you suggested with no luck on resolving my problem.  After making the changes you suggested, I got the error below when I opened a windows form that has to load data from one of the tables.

    The error is "CREATE FILE encountered operating system error 5(Access is denied.) while attempting to open or create the physical file 'C:\Program Files\CSA\Parts-Tracker\Resources\parts_log.LDF'. Could not open new database 'Parts'.  CREATE DATABASE is aborted.  Cannot attach the file 'C:\Program Files\CSA\Parts-Tracker\Resources\parts.mdf' as database 'Parts'.  File activation failure.  The physical file name C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\parts_log.ldf' may be incorrect."

    I tried including the log file parts_log.ldf in the deployment but that caused another error.

    Any suggestions would be greatly appreciated.

     

    Saturday, January 29, 2011 11:42 PM
  • I was able to resolve my problem by using a connection string like the one above, modified for my environment.

    I also had to include the log file in the deployment and add the Modify permision for domain users to the database and log files.

    Thanks!!!

     

    Monday, January 31, 2011 11:13 PM
  • I ended up having to create the database and tables programmatically during the install process to make this work.
    • Marked as answer by isomcsa Wednesday, July 27, 2011 12:52 AM
    Wednesday, July 27, 2011 12:52 AM
  • I ended up having to create the database and tables programmatically during the install process to make this work.
    Hi, Can u please elaborate the process u did, which worked for you ? I am also facing the same issue of deploying SQL Express and creating tables / procedures along with my .net application installation. Your process may help me also., Thanks in advance
    Wednesday, September 21, 2011 11:39 AM
  • I added code to the main form to try to open the database.  I added code to the exception handler to create the database and tables if an exception occurred when I tried to open the database.  This was the only way I could do it. 

    I had tried deploying the database in the MSI setup files and also tried to restore the database after the application installed but none of those worked.

    Let me know if you need further explanation.

    isomcsa

     

    Thursday, September 22, 2011 2:07 AM
  • I added code to the main form to try to open the database.  I added code to the exception handler to create the database and tables if an exception occurred when I tried to open the database.  This was the only way I could do it. 

    I had tried deploying the database in the MSI setup files and also tried to restore the database after the application installed but none of those worked.

    Let me know if you need further explanation.

    isomcsa

     

    Exactly the same kind of approach i am doing right now.. but i have made a separate .exe (one form application) which in-tern executes the required sql scripts for creating a Database, login, creating tables , procs etc..

    But still i need to solve the issue of embedding the sql express into my .net application with silent installation passing an ini file.. !!

    any idea on this would be very needy..

    thanks

     

    Saturday, September 24, 2011 3:45 PM
  • My application is a Visual Studio 2008 C# single user application.  I needed to deploy the database and tables with the application when it is installed.  The only way I could accomplish it was to have my application to create the database and tables the first time the application is executed.

    In the load event of the main form, I placed the connection string and the open command in a try-catch block.  If the database has not been created, an exception will occur.  I catch the SQLException error and call a code file (SetupDatabase) that creates the database and tables.

    In the SetupDatabase code, I have the connection string to SQL Server Exress 2005, an open statement to open the connection to SQL Server and the statements to create the database and statements to create the tables.  The statements to create the database are

    string sCreateDatabase = "CREATE DATABASE parts ON (NAME=parts, FILENAME='C:\\Program Files\\Microsoft SQL Server\\MSSQL.1\\MSSQL\\Data\\parts.mdf')";

    SqlCommand mycommand = new SqlCommand();

    mycommand.CommandType = CommandType.Text;

    mycommand.CommandText = sCreateDatabase;

    mycommand.Connection = dbConnection;

     

    mycommand.ExecuteNonQuery();

     

    dbConnection.Close();

    The database has to be closed after creating the database so the changes are written to disk.  Then open the database and use similiar statements as above to create your tables.

    I hope this help.  Let me know if you need more explanation.

     

    Sunday, September 25, 2011 12:05 AM
  • Thank u ,

    I am alo acomplishing the in exactly the similar way., but instead of Main form , i have created a separate .exe file , as it is one time job, and to avoid to check db existance everytime in main form.

    But I need know any idea about silent installation of SQL server along with application package? any idea? how u deploy the application with sql server?

    Sunday, September 25, 2011 11:55 AM
  • You specify SQL Server in Prerequisites under Properties of the Setup project.  You can also specify other prerequisites such as Windows Installer 3.1.
    Monday, September 26, 2011 2:45 PM
  • Thank u.. Yes, that is what im employing, but it brings an interface for all the sql installation parameters..  have can thought of any idea how to supply the parameter ini file? Is it possible? Thanks
    • Edited by hbkelkar Monday, September 26, 2011 4:13 PM typo
    Monday, September 26, 2011 4:12 PM
  • I did not employ an ini file.  I used the prerequisites under Properties of the Setup project.  I think this would be better because it will install SQL Server Express 2005, .Net Framework 2.0, Windows Installer 3.1, etc during installation of the application without the user having to do anything.

    Your installation project should be a Visual Studio Setup and Deployment project.  Under Properties in the Setup and Deployment project, Select Build under Configuration Properties and then click the Prerequisites button.

    On the Prerequisites screen, place a check mark in box for "Create setup program to install the prerequisites components".  Select the necessary components in the window "Choose which prerequisites to install".  Under "Specify install location for prerequisites", select "Download prerequisites from the component vendor's web site".

    These are the steps that I used which worked just fine.

     

    Monday, September 26, 2011 4:49 PM
  • Thank u Isomcsa,

    I added all the prerequisites along with sql 2005 express, Yes , it installed without any user interface.

    It Installed with default settings i.e. services installed under Network service , windows authentication and Network protocol (TCP / IP) disabled.

    But How to manage the Authentication mode, which i need Mixed Mode and set a password for sa. and Run the services as Local System instead of Network service. And Need to enable TCPIP protocol, while installation only. So how do i do this with package / prerequisite.

    These can be done with unattended installation with the help of an .ini file with all these settings. But with Package / Prerequisite,.. i dont have any idea..

    Any idea will be helpful...

     

    Thanks

     

    Wednesday, September 28, 2011 6:23 AM
  • The authentication mode can be set in the connection string.  I don't know how to set the other requirements during installation.

     

    Wednesday, September 28, 2011 5:24 PM
  • Ok, Thank u ,

    im on the job to merge all these in to one piece.. update on getting some fruitful solution..

    thanks

     

    Wednesday, September 28, 2011 6:08 PM