locked
VS 2010 Database Project with multiple Pre/Post deployment scripts.

    Question

  • We currently use Script.PostDeployment.sql to manage our lookup data and test data for development using the following syntax.

    :r .\ReferenceData\LookupData.sql
    
    IF '$(DeploymentConfiguration)' = 'Debug'
    BEGIN
    	:r .\TestData\TestData.sql
    END
    

    It works well up to this point, We use a conditional statement around the test data scripts so that they don't get run on our staging and production severs. The only problem with the above is when doing a release all the scripts for test data are deployed but not excuted because of the IF statement. This makes some people nervous.

    What I would prefer is to have a dev and release version of the deployment files. so that i could do the following.

    --Dev Deployment Script
    :r .\ReferenceData\LookupData.sql
    :r .\TestData\TestData.sql
    
    --Release Deployment Script
    :r .\ReferenceData\LookupData.sql
    Or is there some way to tell the deployment stript  not  to include specific files for different Deployment configurations?
    Thursday, August 26, 2010 3:25 PM

Answers

  • So I was finally able to get this work. I started looking for MSBuild commands to find the code I needed.

    In the  Post-Deployment folder I added 2 files

    • My.Debug.Post.Deployment.sql
    • My.Release.Post.Deployment.sql

    each file has only those scripts needed for that configuration.

    I modified the existing Script.PostDeployment.sql and only had the following line:

    • :r .\My.Post.Deployment.sql

    I think unloaded the database project and modified the project file and added the following

     <Target Name="BeforeBuild">
      <Copy Condition=" '$(Configuration)|$(Platform)' == 'Release|AnyCPU' " SourceFiles="Scripts\Post-Deployment\My.Release.Post.Deployment.sql" DestinationFiles="Scripts\Post-Deployment\My.Post.Deployment.sql" />
      <Copy Condition=" '$(Configuration)|$(Platform)' == 'Debug|AnyCPU' " SourceFiles="Scripts\Post-Deployment\My.Debug.Post.Deployment.sql" DestinationFiles="Scripts\Post-Deployment\My.Post.Deployment.sql" />  
     </Target>
    

    The end result is that when I deploy the database project either the Debug or Release file is copied to My.Post.Deployment.sql and include in the final deployment file so no unnecessary sql scripts are added.

    Not sure if this is the best way to do this but it seems to work.

    Artin

    • Marked as answer by abannayan Thursday, September 09, 2010 12:54 PM
    Thursday, September 09, 2010 12:54 PM
  • No, the actual informmation about which file to execute Scripts.PostDeployment.sql is stored in the dbproj file itself and not the sqldeployment file. One option though is to copy them all over and use the configuration switches of Visual Studio to eliminate the relevant files in the PostBuild step (Build Events). If they adhere to a certain pattern, you could delete them via del *_release*.sql. Does that make sense and would be appropriate for you ?

    -Jens


    Jens K. Suessmeyer http://blogs.msdn.com/Jenss
    Thursday, August 26, 2010 5:24 PM

All replies

  • No, the actual informmation about which file to execute Scripts.PostDeployment.sql is stored in the dbproj file itself and not the sqldeployment file. One option though is to copy them all over and use the configuration switches of Visual Studio to eliminate the relevant files in the PostBuild step (Build Events). If they adhere to a certain pattern, you could delete them via del *_release*.sql. Does that make sense and would be appropriate for you ?

    -Jens


    Jens K. Suessmeyer http://blogs.msdn.com/Jenss
    Thursday, August 26, 2010 5:24 PM
  • Thanks Jens,

    I see where you're going with that and I think that might work for me, but I must admit my knowlege of working with the build events is somewhat limited.

    Initially I had tried something like the follow, but it appears you can only have one  solution set of deployment files

    <PostDeploy Include="Scripts\Post-Deployment\Script.PostDeployment.Release.sql" Condition=" '$(Configuration)' == 'Release' ">
       <SubType>Code</SubType>
    </PostDeploy>  
    <PostDeploy Include="Scripts\Post-Deployment\Script.PostDeployment.Debug.sql" Condition=" '$(Configuration)' == 'Debug' ">
       <SubType>Code</SubType>
      </PostDeploy>
    

    I was just reading the Visual Studio 2010 Database Projects Guidance Document and saw the Area documenting Pre and Post Deployment Events, but getting to the next step has me stumped.

    Artin 

    Thursday, August 26, 2010 6:25 PM
  • Dear Artin,

    For more details information about pre and post build events you can see the following links.

    http://blogs.msdn.com/b/gertd/archive/2009/10/13/pre-and-post-deployment-events.aspx

    http://msdn.microsoft.com/en-us/library/aa833213.aspx

    http://msdn.microsoft.com/en-us/library/aa833259.aspx

    In addition, I think you can benefit from the following hands-on practice which describes how to extend database project deployment to modify the deployment plan.

    http://msdn.microsoft.com/en-us/library/ee461507.aspx

    Hopeful it helps you. Let me know if you have any further concerns.

    Thank and Best Regards,

    Vicky Song


    Please remember to mark the replies as answers if they help and unmark them if they provide no help

    Friday, August 27, 2010 6:42 AM
  • Sorry for my delay in replying, I've been unavailable.

    I've been over the links you mention vicky and theysimply don't give enough detail on how to accomplish what I need.

    I believe what Jens is suggesting is how I want to accomplish what I need, but finding a list of commands thats I can use to delete files and move them appropriately has proven difficult.

    If I understand what she's suggesting I would have something like the following

    Script.PostDeployment.sql -- created by the visual studio  project. It would have a link to the following

    : .\My.PostDeployment.sql

    I will then have 2 files in my project

    My.Release.PostDeployment.sql and

    My.Debug.PostDeployment.sql

    The commands I would need in the <PreBuild> event  would rename the file based on my current configuration. Where can i find a list of commands that show me how to delete or rename the files in my solution. so for example if I'm in the Debug configuration My.Debug.PostDeployment.sql will be renamed to My.PostDeployment.sql and therefor be included in the Script.PostDeployment.sql

    My appologies if I'm misunderstand.

    Artin

     

     

    Wednesday, September 08, 2010 5:05 PM
  • Dear Artin,

    Thanks for your post. I did a little more digging for your issue recently, I find a solution and I test it on my PC, it works fine.

    Could you please follow the steps below to have a try?

    (1). You can use the Database.sqlcmdvars file, which is stored in the Properties folder, to define a variable (Variable Name: $(DeploymentConfiguration), Variable Value: DEV).

    (2). In your Script.PostDeploymen.sql

    print N'PostDeployment'

    go

    --DEV Version

    if (N'DEV' = ISNULL(N'$(DeploymentConfiguration)', N'DEV'))

    begin

    :r .\ReferenceData\LookupData.sql

    :r .\TestData\TestData.sql

    end

    else

    begin

    :r .\ReferenceData\LookupData.sql

    end

    go

     

    If you want to change the version from RELEASE to DEV, you would just modify the condition the IF statement:

    if (N'RELEASE' = ISNULL(N'$(DeploymentConfiguration)', N'DEV'))

     

    Hopeful it helps you, let me know if you have any further concerns.

    Thanks and Best Regards,

    Vicky Song


    Please remember to mark the replies as answers if they help and unmark them if they provide no help

    Thursday, September 09, 2010 7:51 AM
  • Hi Vicky,

    Thanks for the reply,  that's basically what I do currently and it does work.  The issue I'm having is that it imports both the dev and release scripts. They don't execute because of the if statement, but it makes people nervous seeing delete statements and test data scripts in the file. So what ends up happening is that the people depolying to live still manually edit the files and remove the scripts for debugging. I'd like to get to a point where one or the other is included.

    From what Jens is describing I can modify the project file so that it will use the Build Events to manage the script files before the depolyment script is built, that way only the Debug files or Release files are included in deployment script.

    Artin

    Thursday, September 09, 2010 12:25 PM
  • So I was finally able to get this work. I started looking for MSBuild commands to find the code I needed.

    In the  Post-Deployment folder I added 2 files

    • My.Debug.Post.Deployment.sql
    • My.Release.Post.Deployment.sql

    each file has only those scripts needed for that configuration.

    I modified the existing Script.PostDeployment.sql and only had the following line:

    • :r .\My.Post.Deployment.sql

    I think unloaded the database project and modified the project file and added the following

     <Target Name="BeforeBuild">
      <Copy Condition=" '$(Configuration)|$(Platform)' == 'Release|AnyCPU' " SourceFiles="Scripts\Post-Deployment\My.Release.Post.Deployment.sql" DestinationFiles="Scripts\Post-Deployment\My.Post.Deployment.sql" />
      <Copy Condition=" '$(Configuration)|$(Platform)' == 'Debug|AnyCPU' " SourceFiles="Scripts\Post-Deployment\My.Debug.Post.Deployment.sql" DestinationFiles="Scripts\Post-Deployment\My.Post.Deployment.sql" />  
     </Target>
    

    The end result is that when I deploy the database project either the Debug or Release file is copied to My.Post.Deployment.sql and include in the final deployment file so no unnecessary sql scripts are added.

    Not sure if this is the best way to do this but it seems to work.

    Artin

    • Marked as answer by abannayan Thursday, September 09, 2010 12:54 PM
    Thursday, September 09, 2010 12:54 PM
  • This is a little unrelated to your question, but I was wondering about the DeploymentConfiguration variable. Did you define this yourself or is it suppose to be automatically available. Thanks!

    Sunday, December 26, 2010 7:59 AM
  • Actually, I get errors when I try to conditionally include scripts with:

    IF
    
    
     '$(DeploymentConfiguration)'
     = 'Debug'
    
    
    BEGIN
    
    	:r .\TestData\TestData.sql
    END

    The errors I get are:

    SQL80001: Incorrect syntax near ':'.

    SQL01261: The syntax check failed 'Unexpected end of file occurred.' in the batch near: ...

    Can you conditionally include scripts like this in VS 2010?

    Sunday, December 26, 2010 8:25 AM
  • You need to define the SQLCMD variable yourself, also make sure the file which you are including must exist, and should not be a zero byte file.

     


    GertD @ www.DBProj.com
    Monday, December 27, 2010 10:00 PM
  • Guys,

    I myself have been annoyed by the missing functionality in Visual Studio 2010. I have looked at various options/alternative to manage and deploy the scripts on the server, but nothing was as easy as it was in Visual Studio 2008.

    Finally, I have created a tool which would allow you to deploy and run the SQL scripts as you used to do in Visual Studio 2008. This project would be made available by March 15, 2010. Its free and can be downloaded at http://sqlexecuter.codeplex.com/

    Will be looking forward for your suggestions and feedback.

    Cheers!!!

    Tushar

    • Proposed as answer by Tushar Thursday, March 17, 2011 3:02 AM
    Saturday, March 12, 2011 11:00 AM
  • Does anyone know exactly where this bit of code needs to be entered into the project file ?

     <Target Name="BeforeBuild">
      <Copy Condition=" '$(Configuration)|$(Platform)' == 'Release|AnyCPU' " SourceFiles="Scripts\Post-Deployment\My.Release.Post.Deployment.sql" DestinationFiles="Scripts\Post-Deployment\My.Post.Deployment.sql" />
      <Copy Condition=" '$(Configuration)|$(Platform)' == 'Debug|AnyCPU' " SourceFiles="Scripts\Post-Deployment\My.Debug.Post.Deployment.sql" DestinationFiles="Scripts\Post-Deployment\My.Post.Deployment.sql" /> 
    </Target>

    Thanks

    Tuesday, June 28, 2011 8:15 AM
  • From

    http://msdn.microsoft.com/en-us/library/ms366724%28v=VS.100%29.aspx

     

    Define the target or targets at the end of your project file, immediately before the <tt></Project></tt> tag.

    Thursday, December 08, 2011 10:42 AM
  • I used the solution that you describe with a slight change. Instead of copying the 'referenced' files, I copied the 'referencing' file - Script.PostDeployment.sql. I reference multiple other scripts from there (in some build configurations) so this reduces the number of files that I need to copy. To make this work, I had one copy of Script.PostDeployment.sql for every configuration and then used them to overwrite Script.PostDeployment.sql where required - dependent on the build configuration.

    MCPD Enterprise Applications


    • Edited by Scott Munro Tuesday, January 22, 2013 9:05 AM
    Tuesday, January 22, 2013 8:59 AM