none
how to run attached sql database script at client side pc

    Question

  • I have attached .sql script(sql server 2008) to my vs 2008 form application.now how can I run this script to client side pc to restore database to client pc. I have changes all bootstraper packages to install .net framework & sql server for prerequisites.Then now how can I restore my local database to client pc. and I got one problem when I goes to install my application in which i have added sql bootstraper package & created setup successfully on my local pc but when I install setup on client pc I does not install sql server express on client pc before installing my application. please help me.

    • Moved by Jonathan KehayiasMVP Saturday, October 08, 2011 4:42 PM VS Setup Project Question (From:SQL Server Database Engine)
    • Moved by Alexander Sun Monday, October 10, 2011 10:35 AM ClickOnce issue (From:Visual Studio Setup and Installation)
    Saturday, October 08, 2011 8:59 AM

Answers

  • Visual Studio setup projects don't support SQ L scripts directly. However, you can use a custom action to execute your script. Here are some articles which may help you:

    http://msdn.microsoft.com/en-us/library/49b92ztk(VS.80).aspx

    http://msdn.microsoft.com/en-us/library/bd8h80ez(VS.71).aspx

    Please note that there is no built-in support for this, so you need to do everything yourself.

    Regarding the SQL Server prerequisite, can you give me more details? How did you configure the prerequisite? Which SQL Server version did you select? Does the target machine have any SQL Server version installed?


    Cosmin Pirvu
    My blog | Contact me
    Please remember to mark the replies as answers if they help.
    Monday, October 10, 2011 1:02 PM
  • You can use the Custom Action to execute the Sql Script as they suggested, but I suggest you use the "attach" way to deploy the database, it will be easier to deploy the database with your application.

    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



    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, October 10, 2011 2:43 PM

All replies

  • The Database Engine forum isn't the correct location for this question, it should probably be in one of the Visual Studio forums instead.  My answer from a Database Engine perspective would be for the installer to execute a sqlcmd command line call to execute the script, but as a former appdev, I know you can do this from the Setup project definition as well, I just have done it in years, and don't have a similar environment to play with to figure it out.

    Jonathan Kehayias | Principal Consultant, SQLSkills.com
    SQL Server MVP | Microsoft Certified Master: SQL Server 2008
    Author of Troubleshooting SQL Server: A Guide for Accidental DBAs
    Feel free to contact me through My Blog or Twitter. Become a SQLskills Insider!
    Please click the Mark as Answer button if a post solves your problem!

    Saturday, October 08, 2011 4:41 PM
  • Hi Maheshpardeshi,

     

    Welcome to the MSDN forum.

     

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

     

    Sorry for any inconvenience.

     

    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.

    Monday, October 10, 2011 10:35 AM
  • Visual Studio setup projects don't support SQ L scripts directly. However, you can use a custom action to execute your script. Here are some articles which may help you:

    http://msdn.microsoft.com/en-us/library/49b92ztk(VS.80).aspx

    http://msdn.microsoft.com/en-us/library/bd8h80ez(VS.71).aspx

    Please note that there is no built-in support for this, so you need to do everything yourself.

    Regarding the SQL Server prerequisite, can you give me more details? How did you configure the prerequisite? Which SQL Server version did you select? Does the target machine have any SQL Server version installed?


    Cosmin Pirvu
    My blog | Contact me
    Please remember to mark the replies as answers if they help.
    Monday, October 10, 2011 1:02 PM
  • You can use the Custom Action to execute the Sql Script as they suggested, but I suggest you use the "attach" way to deploy the database, it will be easier to deploy the database with your application.

    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



    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, October 10, 2011 2:43 PM