Postbuild and prebuild scripts

Postbuild and prebuild scripts

• Pooja Katiyar

  Dear Wei Jia Jun,

  Let me start from the end: Using pre and post deployment scripts is the best way in the product to define and deploy the permissions.

  When database project is built, we first put the pre-deployment script, then script for database objects, then post-deployment script. If you get your post-deployment scripts before database objects - that may be a bug we don't know about.

  Can you, please, try the following:

  1) Make sure that your PostDeployment.script.sql file has a BuildAction property set to PostDeploy (you can check it in the properties window);

  2) Make sure that it contains lines like :r .\filename.sql , where filename is your permissions or certificates file;

  3) Make sure all of those <filename.sql> files have BuildAction property set to NotInBuild (you can only have 1 file being PreDeploy and 1 PostDeploy).

  In addition, if you have a sample script that shows the problem and you can send it and your project to me - that would be great.

  Best Regards,

  
  
  

• CalifGirl

  Thank you. One more question - what are the target database and target connections settings Can you, please specify that you want to recreate the database (on the Build Project Property page) and see what shows up in the script

  Also, thank you for the offer to see your project - I would like to do that. I could not find your email, but can you, please send it to me to dmitriy.nikonov at microsoft dot com

  CTP6 will be available today/tomorrow, may be trying it on CTP6 will fix the problem

  Best Regards,

  
  
  

• Euclidez

  Not a problem, below is the project setting for this configuration manager. Also, Always recreate database is checked and the results are the same as if it is not checked. I will go ahead and mail you the project for you to view and am very, very excited to hear that CTP 6 is to be released and will try this project on that CTP as soon as it becomes avaliable. Thanks for your help with this situation.

  Target Connection: Data Source = (local); Integrated Security=True; Pooling=False

  Target Database Name: TransferSecurity

  
  

• Glenn Ramsey

  One more question, in order for me to verify the issue, I need to know:

  are you using English or localized version (i.e. Japanese) of CTP5

  
  
  

• Uncle Ted

  I have downloaded CTP 6 and update the database project to work with CTP 6 and this issue does not seem to exists. Thankx for all the help.
  
  

• schewardnadse

  Ok, in response to the 3 items that you wanted me to double-check
  
  1) yes, the script.PostDeployment.sql does have a BuildAction of Postdeploy
  
  2) The script.PostDeployment.sql file contents are as follows
  SQLCMD :r .\Permissions.sql
  SQLCMD :r .\RulesAndDefaults.sql
  SQLCMD :r .\Signatures.sql
  
  3) and each of those files do have a BuildAction of NotInBuild (like you said, only one can be Postdeploy)
  
  I am also using the English version of CTP5 the release that was avaliable for download on October 3rd.
  
  below I will give you a sample of what the files look like and the problem that I am having when I run the deploy. (including the PreDeployment scripts)
  
  It just looks to me that they are combining the pre and post deployment scripts into one large script and running them before the rest of the database objects have been created. Now I changed some of the names but if you would like to get the project please just email me and I will send you a copy that you can try out.
  
  EncryptionKeysAndCertificates.sql
  CREATE MASTER KEY ENCRYPTION BY PASSWORD = ''
  
  CREATE CERTIFICATE [SomeCertName]
  WITH SUBJECT = 'Some Cert',
  EXPIRY_DATE = '1/1/2007';
  
  Script.PreDeployement.sql
  SQLCMD :r .\Logins.sql
  SQLCMD :r .\EncryptionKeysAndCertificates.sql
  SQLCMD :r .\LinkedServers.sql
  SQLCMD :r .\CustomErrors.sql
  
  Permissions.sql
  GRANT EXECUTE ON [Schema].[Procedure1] TO [User]
  GRANT EXECUTE ON [Schema].[Procedure2] TO [User]
  GRANT EXECUTE ON [Schema].[Procedure3] TO [User]
  ....
  
  Final Script Autogenerated on deployment
  ... (Compter generated script)
  USE [Databasename]
  GO
  
  CREATE MASTER KEY ENCRYPTION BY PASSWORD = ''
  
  CREATE CERTIFICATE [SomeCertName]
  WITH SUBJECT = 'Some Cert',
  EXPIRY_DATE = '1/1/2007';
  GO
  
  GO
  SET XACT_ABORT ON
  GO
  SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
  GO
  SET ANSI_NULLS ON
  GO
  SET QUOTED_IDENTIFIER ON
  GO
  SET ARITHABORT ON
  GO
  
  GO
  
  
  
  GRANT EXECUTE ON [Schema].[Procedure1] TO [User]
  GRANT EXECUTE ON [Schema].[Procedure2] TO [User]
  GRANT EXECUTE ON [Schema].[Procedure3] TO [User]
  ...
  
  GO
  
  
  
  
  
  
  
  
  
  
  
  
• Postbuild and prebuild scripts