none
VS2010 install project and SQL 2008 Express install RRS feed

  • Question

  • Hi,

    I have a completed WinForms project that uses MS SQL2008 SP1 on a Windows 7 64-bit machine & C#, .net 4.  I have created an Installer Project and am now stuck withe the DB and how to transfer it in the Install project.

    I have been tyring to find a good set of docs on "How to install MS SQL 2008 SP1 along with a DB from a WinForms Installer Project?".  I tried using the InstallShield Limited Edition that comes with VS but the SQL portion needed is disabled and needs upgrading to Premium of Professional... don't have the bucks!

    spent the last 4 hours reading about it and have got nowhere... Oh and its too late to convert the DB to the CE version... (besides I'm old fashioned and like the separation of data business rules logic and interface code.  Besides there are over 100 SP's I'd have to re-create!!!)

    Thanks


    SquireDude
    • Moved by Alexander Sun Tuesday, August 30, 2011 8:25 AM (From:Visual Studio Setup and Installation)
    Saturday, August 27, 2011 8:27 PM

Answers

  • You can download them from Microsoft website, and then use the Bootstrapper Manifest Generator generate a customize bootstrapper, then you can use it as the prerequisite in the Visual Studio Setup project or the ClickOnce publish.

    Steps for generate the bootstrapper:

    http://www.codeproject.com/KB/aspnet/Add_Custom_Prerequisite.aspx

    Download the msi file:

    http://www.microsoft.com/download/en/details.aspx?id=25052

    It auto created the package in the "Document" folder, and then I copy it to the following folder:

    C:\Program Files (x86)\Microsoft SDKs\Windows\v6.0A\Bootstrapper\Packages

    VS2008 will look for the bootstrapper from above folder.

    Then open the VS2008, it can show us the custom prerequisite.

    If you're using the VS2010: C:\Program Files (x86)\Microsoft SDKs\Windows\v7.0A\Bootstrapper\Packages

    Even though there would be a exception when you operate the "Bootstrapper Manifest Generator", we just can ignore it.

     

    If you have any more question on the usage of Bootstrapper Manifest Generator, then you can go to the below forum:

    http://archive.msdn.microsoft.com/bmg/Thread/List.aspx

     

    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.

    Tuesday, August 30, 2011 10:15 AM
    Moderator
  • I think you can use those methods help yourself.

    how to attach sql database file to creating .exe setup in c#

    I think you just can use the SmoApplication.EnumAvailableSqlServers method or SqlDataSourceEnumerator.GetDataSources method to help you collect the instance on the system, and then you can display them to the user on the Form, then let us select which instance they will use with your application.

    After those steps, you can make a correct connection string for the current user. Then your application can use this connection string in feature.

     

    You need to add the database to your Visual Studio application, set the build action to "content" and set "copy to output directory" as "copy if newer". Then the database will be included with the application's deployment.

    By default, the connection string will use the DataDirectory property of the ApplicationDeployment.

    Then ClickOnce will publish the database to the data directory on the user machine. When there is an update, it will copy the database to a folder under the data folder called .\pre and put the new one in the top folder, and you will have to handle moving the data in code.

    This is obviously a p.i.t.a., and not something I recommend doing. For one thing, if the time stamp on the database changes, even if it's just from you checking out a table definition, ClickOnce will assume you changed it and redeploy it, even though you didn't expect it to do so.

    For this reason, I recommend moving it to a folder under ApplicationData after it is deployed, and managing the updates yourself. Here is an article that explains how to do this:

    http://robindotnet.wordpress.com/2009/08/19/where-do-i-put-my-data-to-keep-it-safe-from-clickonce-updates/

    You will also need to check the Application Files dialog and change it from "Include(Data)" to just "Include" so it no longer publishes it to the data folder, or change the code in that article to access it in the DataDirectory when copying it the first time.

    RobinDotNet

    I think the way in my first link is easy to use for you, so that is what I suggest you have a try.

    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.


    Wednesday, August 31, 2011 12:01 PM
    Moderator
  • Good morning Mike,

    Thanks for the response and followup.

    Just to refresh the question... the issue is NOT the applications connection string but "How to install SQL using the Installer Project & What is the best method to transfer the actual database files (mdf & ldf) with the installer project."

    I hope that these solutions will help other trying to make a program and installer project along with it for distribution to the public.  And if anyone can see ways to improve the code please let me know as I am not good with Scripting.

    The solution to Part 1 is to create a Custom Action in the Installer project and tell it to use the VBScript below.

    The solution to part 2 of my question is to create a Backup file then perform a RESTORE in a VBScript file. (see vbscript file below)

    Thanks

    ----------------------------------------------------------------------------------------

    Option Explicit
    
    Dim adoCommand
    Dim strConnect
    Dim strBackupFile
    Dim strServreName 
    Dim strDatabaseName
    Dim Act :Set Act = CreateObject("Wscript.Shell")
    Dim Fso :Set Fso = CreateObject("Scripting.FileSystemObject")
    Dim currentDriveDirectory, strComputerName
     
     ' get all the drive and folder info
     currentDriveDirectory = left(WScript.ScriptFullName,(Len(WScript.ScriptFullName))-(len(WScript.ScriptName)))
     ''sqlcmdmsgbox "current directory " & currentDriveDirectory
     ' Specify backup to be restored.
     strBackupFile = currentDriveDirectory & "abc5.bak"
    'MsgBox "Current Path " & strBackupFile
    
    ' get the server name 
    strComputerName = Act.ExpandEnvironmentStrings( "%COMPUTERNAME%" )
    'msgbox strComputerName
    strServreName = strComputerName & "\sqlexpress"
    'msgbox strServreName 
    
    strDatabaseName ="YachtLogSQL1"
    ' Specify connection string to Master Database on SQL server.
    ' If using the default instance, use SERVER=MyServer.
    strConnect = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=;Data Source=" & strServreName 
    'msgbox strConnect
    
    ' Restore database from backup.
    Set adoCommand = CreateObject("ADODB.Command")
    On Error Resume Next
    adoCommand.ActiveConnection = strConnect
    ''msgbox strconnect
    If (Err.Number <> 0) Then
    ''msgbox Err.number & " - " & Err.Description
    Wscript.Echo "Unable to connect to Master database."
    Wscript.Echo Err.Description
    Wscript.Quit
    End If
    
    On Error GoTo 0
    
    'adoCommand.CommandText = "CREATE DATABASE YachtLogSQL1 ON  PRIMARY ( NAME = N'YachtLogSQL1',FILENAME = N'C:\Program Files\Phillip J Seaman\YachtLog\Data\YachtLogSQL1.mdf') LOG ON ( NAME = N'YachtLogSQL1_log', FILENAME = N'C:\Program Files\Phillip J Seaman\YachtLog\Data\YachtLogSQL1_log.ldf')"
    'adoCommand.Execute
    
    adoCommand.CommandText =" RESTORE DATABASE " & strDatabaseName & " FROM DISK='" & strBackupFile & "' with move 'YachtLogSQL1_dat' To ' C:\Program Files\Phillip J Seaman\YachtLog\Data\" & strDataBaseName & ".mdf'"
    adoCommand.Execute
    
    MsgBox "Restore Complete"
    Set adoCommand = Nothing
    



    SquireDude
    • Marked as answer by SquireDude Saturday, September 3, 2011 3:47 PM
    Saturday, September 3, 2011 3:47 PM

All replies

  • Hi SquireDude,

     

    Welcome to the MSDN forum.

     

    I am sorry that this queue is about Visual Studio Setup and Installation. In my opinion, your problem is related to project deployment and setup, so I will move your thread to ClickOnce forum for better support.

     

    Thanks.

     

    Best Regards,


    Alexander Sun [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.

    Tuesday, August 30, 2011 8:24 AM
  • You can download them from Microsoft website, and then use the Bootstrapper Manifest Generator generate a customize bootstrapper, then you can use it as the prerequisite in the Visual Studio Setup project or the ClickOnce publish.

    Steps for generate the bootstrapper:

    http://www.codeproject.com/KB/aspnet/Add_Custom_Prerequisite.aspx

    Download the msi file:

    http://www.microsoft.com/download/en/details.aspx?id=25052

    It auto created the package in the "Document" folder, and then I copy it to the following folder:

    C:\Program Files (x86)\Microsoft SDKs\Windows\v6.0A\Bootstrapper\Packages

    VS2008 will look for the bootstrapper from above folder.

    Then open the VS2008, it can show us the custom prerequisite.

    If you're using the VS2010: C:\Program Files (x86)\Microsoft SDKs\Windows\v7.0A\Bootstrapper\Packages

    Even though there would be a exception when you operate the "Bootstrapper Manifest Generator", we just can ignore it.

     

    If you have any more question on the usage of Bootstrapper Manifest Generator, then you can go to the below forum:

    http://archive.msdn.microsoft.com/bmg/Thread/List.aspx

     

    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.

    Tuesday, August 30, 2011 10:15 AM
    Moderator
  • Hi Mike,

    Thanks for the reply its is well detailed and I was able to generate the Bootstrap for SQL Express Server R2 (sorry my mistake it should have been R2 NOT SP1).

    My (hopefully last issue) is tconnectivity between the install and DB?

    WHen I run tha newly installed application I get the following error...

    An error occurred during the 'CreateConnection' method.  System.Data.SqlClient.SqlException (0x80131904): A user instance was requested in the connection string but the server specified does not suppor tthis option.
    Changed database to 'master'.
    Changed language to us_english.

    etc...

    etc...

    I believe that the DB was copied over and SQL installed BUT the DB was NOT installed inot SQL... I think!?

    How to I make the connection to the DB from SQL during the Install process?

     


    SquireDude

     

    All prereqssits files and programmes are being installed including SQL Server Express R2, .NET 4, etc.


    If it helps here is teh Connection String in the config file... Data Source=.;AttachDbFilename=|DataDirectory|\Data\YachtLogSQL.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True
    • Edited by SquireDude Tuesday, August 30, 2011 4:44 PM
    Tuesday, August 30, 2011 4:16 PM
  • Connecting from the install process typically doesn't work. Custom actions run with the system account, and databases aren't usually set up to allow access from that account.  Is there some reason you need to create the DB at install time rather than just ship it already populated? Or can you create it at first boot of the app?
    Phil Wilson
    Tuesday, August 30, 2011 4:40 PM
    Moderator
  • Hi Phil,

    Thanks... my preference is to ship the DB ready to use and populated with default data... how do I do that..????


    SquireDude
    Tuesday, August 30, 2011 4:52 PM
  • I think you can use those methods help yourself.

    how to attach sql database file to creating .exe setup in c#

    I think you just can use the SmoApplication.EnumAvailableSqlServers method or SqlDataSourceEnumerator.GetDataSources method to help you collect the instance on the system, and then you can display them to the user on the Form, then let us select which instance they will use with your application.

    After those steps, you can make a correct connection string for the current user. Then your application can use this connection string in feature.

     

    You need to add the database to your Visual Studio application, set the build action to "content" and set "copy to output directory" as "copy if newer". Then the database will be included with the application's deployment.

    By default, the connection string will use the DataDirectory property of the ApplicationDeployment.

    Then ClickOnce will publish the database to the data directory on the user machine. When there is an update, it will copy the database to a folder under the data folder called .\pre and put the new one in the top folder, and you will have to handle moving the data in code.

    This is obviously a p.i.t.a., and not something I recommend doing. For one thing, if the time stamp on the database changes, even if it's just from you checking out a table definition, ClickOnce will assume you changed it and redeploy it, even though you didn't expect it to do so.

    For this reason, I recommend moving it to a folder under ApplicationData after it is deployed, and managing the updates yourself. Here is an article that explains how to do this:

    http://robindotnet.wordpress.com/2009/08/19/where-do-i-put-my-data-to-keep-it-safe-from-clickonce-updates/

    You will also need to check the Application Files dialog and change it from "Include(Data)" to just "Include" so it no longer publishes it to the data folder, or change the code in that article to access it in the DataDirectory when copying it the first time.

    RobinDotNet

    I think the way in my first link is easy to use for you, so that is what I suggest you have a try.

    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.


    Wednesday, August 31, 2011 12:01 PM
    Moderator
  • Hi Mike,

    Thanks for all the good help,  I tried your first suggestion and it fails as before in that the DB is NOT automatically "attached" to SQL.

    I am not having an issue of getting the DB included in the project and carried over and is in the Application structure in a folder call \Data\.

    The information for the "Click Once" I didn't know about and will make the appropriate changes... thanks good tip.

    So I am still having the iiuse of not being able to attach the db to SQL then connecting to it.

    Thanks


    SquireDude
    Wednesday, August 31, 2011 6:13 PM
  • Hi Mike,

    I found this connection string to work better...

    DataSource=.;AttachDbFilename=|DataDirectory|\DB\DataBaseName.mdf;IntegratedSecurity=True;Connect Timeout=30;User Instance=True

    Thanks


    SquireDude
    Excuse me, I saw you said it can work for you right? But you also said it cannot work for you in this web page, I'm a little confused, I think.
    When you open a connection in your application, the DB file will be attached to the correct Database instance(which is set for the DataSource, so please ensure you can use this "." name as the instance login to your Database.), and it will be detached when there's no connection from your application to that DB.
    My connection string struct has no problem, I think, maybe you need to ensure the instance name for the DataSource.
    And please let me know the result after you followed my suggestion step by step, I have ensure that steps again, so I think there'll be no problem if you do it follow my steps.
    Have a nice day, SquireDude!

    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.

    Thursday, September 1, 2011 2:33 AM
    Moderator
  • Hi SquireDude,
    I am writing to check the status of the issue on your side. 
    What about this problem now? 
    Would you mind letting us know the result of the suggestions?

    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.

    Saturday, September 3, 2011 10:07 AM
    Moderator
  • Good morning Mike,

    Thanks for the response and followup.

    Just to refresh the question... the issue is NOT the applications connection string but "How to install SQL using the Installer Project & What is the best method to transfer the actual database files (mdf & ldf) with the installer project."

    I hope that these solutions will help other trying to make a program and installer project along with it for distribution to the public.  And if anyone can see ways to improve the code please let me know as I am not good with Scripting.

    The solution to Part 1 is to create a Custom Action in the Installer project and tell it to use the VBScript below.

    The solution to part 2 of my question is to create a Backup file then perform a RESTORE in a VBScript file. (see vbscript file below)

    Thanks

    ----------------------------------------------------------------------------------------

    Option Explicit
    
    Dim adoCommand
    Dim strConnect
    Dim strBackupFile
    Dim strServreName 
    Dim strDatabaseName
    Dim Act :Set Act = CreateObject("Wscript.Shell")
    Dim Fso :Set Fso = CreateObject("Scripting.FileSystemObject")
    Dim currentDriveDirectory, strComputerName
     
     ' get all the drive and folder info
     currentDriveDirectory = left(WScript.ScriptFullName,(Len(WScript.ScriptFullName))-(len(WScript.ScriptName)))
     ''sqlcmdmsgbox "current directory " & currentDriveDirectory
     ' Specify backup to be restored.
     strBackupFile = currentDriveDirectory & "abc5.bak"
    'MsgBox "Current Path " & strBackupFile
    
    ' get the server name 
    strComputerName = Act.ExpandEnvironmentStrings( "%COMPUTERNAME%" )
    'msgbox strComputerName
    strServreName = strComputerName & "\sqlexpress"
    'msgbox strServreName 
    
    strDatabaseName ="YachtLogSQL1"
    ' Specify connection string to Master Database on SQL server.
    ' If using the default instance, use SERVER=MyServer.
    strConnect = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=;Data Source=" & strServreName 
    'msgbox strConnect
    
    ' Restore database from backup.
    Set adoCommand = CreateObject("ADODB.Command")
    On Error Resume Next
    adoCommand.ActiveConnection = strConnect
    ''msgbox strconnect
    If (Err.Number <> 0) Then
    ''msgbox Err.number & " - " & Err.Description
    Wscript.Echo "Unable to connect to Master database."
    Wscript.Echo Err.Description
    Wscript.Quit
    End If
    
    On Error GoTo 0
    
    'adoCommand.CommandText = "CREATE DATABASE YachtLogSQL1 ON  PRIMARY ( NAME = N'YachtLogSQL1',FILENAME = N'C:\Program Files\Phillip J Seaman\YachtLog\Data\YachtLogSQL1.mdf') LOG ON ( NAME = N'YachtLogSQL1_log', FILENAME = N'C:\Program Files\Phillip J Seaman\YachtLog\Data\YachtLogSQL1_log.ldf')"
    'adoCommand.Execute
    
    adoCommand.CommandText =" RESTORE DATABASE " & strDatabaseName & " FROM DISK='" & strBackupFile & "' with move 'YachtLogSQL1_dat' To ' C:\Program Files\Phillip J Seaman\YachtLog\Data\" & strDataBaseName & ".mdf'"
    adoCommand.Execute
    
    MsgBox "Restore Complete"
    Set adoCommand = Nothing
    



    SquireDude
    • Marked as answer by SquireDude Saturday, September 3, 2011 3:47 PM
    Saturday, September 3, 2011 3:47 PM
  • Morning, SquireDude!

    But that moment here is evening :)

    I see that you're using restore the database bak file as the method, it is working fine for you, but it looks like that cannot prove if the original question's root cause is not the connection string, but in my mind I think the original root cause is the connection string.

    Any way, it is working now, I'm glad to hear that!

     

    Have a nice weekend!


    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.

    Sunday, September 4, 2011 4:49 AM
    Moderator
  • Hi Mike,

    Thanks for the reply...

    Why user Backup & Restore over Detach & Attach?

    Microsoft is scaling back and will be discontinuing the Detach & Attach processes as of SQL 2008.

    -----------------------------------------

    Question for you regarding the Bootstrapper for R2... I noticed that is doesn't install the Studio Management desktop tool.  Got any thoughts or suggestions on how to modify the boostrapper to include the version of R2 that DOES include the manager?

    Thanks


    SquireDude
    Sunday, September 4, 2011 4:00 PM
  • Microsoft® SQL Server® 2008 Management Studio is not in Sql Sever 2008[R2] package, it is a separated tool, it can mange Sql Sever 2008 and Sql Sever 2008 R2. I don't think so the end user need this UI tool, it will let use modify your database easier than before, it will be hard to manage your application's status to you. If you also want this tool, then you also need to follow my first post to generate yours custom bootstrapper. But I also do not suggest you give the end user this tool as I analysis.

    Excuse me, "Why user Backup & Restore over Detach & Attach?", I think I didn't catch you very well.

    I just say the methods will be also let the application work, I have no other meanings.

     

    If there's 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.


    Monday, September 5, 2011 3:26 AM
    Moderator
  • Hi Mike,

    Thanks for the confirmation on the SQL tool not being part of the normal install package, which I though was correct but I kept being told by a Microsoft Support person that it was!!  The user does not need the tool it was only for me while I am testing the install package.  I have used your Bootstrapper instruction and they work as I expected... very well too!

    As for the "Attach vs Restore Backup" was a result of information given to me by MS.  I was not impugning your knowledge or response it was just a comment and to pass along to all the information I was given by Microsoft's Tech Support SQL team.  Attach & Detach are being retired.

    Thanks


    SquireDude
    Monday, September 5, 2011 3:17 PM
  • Thanks for you reply!

    I'm not pay so much attention on Sql Sever news, so I didn't know that there's this news:

    "Attach & Detach are being retired"

     

    Have a nice day, man!


    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.

    Tuesday, September 6, 2011 2:25 AM
    Moderator
  • Hi SquireDude,

    I am also trying to build a setup program that install SQL Express Server and then attach database file to a taret PC.  I follow your method using the above VB script inside the Custom Action Build Commit section, but fail to run during installation. 

    Pls advise what went wrong at my side.

    Look forward to your help.  Thanks.

    Friday, May 25, 2012 7:32 AM
  • Hi MF,

    Not sure I am the best person to answer your question.  As you can see from this thread it me quite a while to get it write.  Hopefully Mike will pick up on the thread and help you to a solid conclusion as he did for me.

    I can only suggest to go back over each of the step and make sure nothing is missing.  One mistake I made thatn can save you time is to NOT install the SQL Manager 2008 R2... it only needed the SQL Express which is much easier to install and load.

    Good Luck.


    SquireDude

    Friday, May 25, 2012 1:54 PM