Wednesday, November 23, 2011

Running SQLCMD Database Deployment scripts from SQL Management Studio

This is a very common scenario for database projects created in Visual Studio:
  • Create a database project
  • Right click the project and select Properties
  • Set "Deploy action" to "Create a deployment script (.sql)
  • Right click the project and select Deploy
This will generate the .sql file which can be used for deploying the database separately.

However, if you try to run this file from SQL Management Studio, it will result in errors.

This is because the script contains some sqlcmdvars (:setvar) which are specific to SQLCMD. If you run the same script using sqlcmd.exe, it would work fine.
If you want to run it from SQL Management Studio, click Query->SQLCMD Mode and then execute the query.

Tuesday, October 25, 2011

TFS 2010 Gated Checkin - Reconcile workspace after reboot

Normally when you checkin your changes with Gated Checkin enabled, once the files are checked in you see a "Team Foundation Build Notification" pop up to reconcile the workspace.
However, condiser you checkin something at the end of the day and go home thinking that you will reconcile the next day. In the mean time, your system got rebooted or went outside of network. The next day when you login to your system, you dont see the "Team Foundation Build Notification" pop up to reconcile the workspace.
You dont have to wait for the pop-up and can do the following:
  • Open the Build result of your Gated Checkin by double clicking on the Gated Checkin Build Definition in Team Explorer->Team Project->Builds
  • Click Reconcile Workspace


Thursday, October 20, 2011

TFS 2010 - Lab Build Definition appends the configuration to build output path $(BuildLocation)

Consider you have two build definitions:
  • DailyBuild - Uses DefaultTemplate.xaml
  • CIBuild - Uses LabDefaultTemplate.xaml
If you do not specify any configurations explicitly in DailyBuild, the CIBuild would work fine and you will be able to run the Build-Deploy-Test workflow smoothly.

However, as soon as you explicitly specify a configuration in DailyBuild, the CIBuild would start failing because it wont be able to find the binaries in the build drop.
This is because it appends the configuration to build output path in $(BuildLocation)

The solution is to modify the LabDefaultTemplate.xaml
Before:
<If Condition="[LabWorkflowParameters.BuildDetails.IsTeamSystemBuild = True]" DisplayName="Compute build location needed" sap:VirtualizedContainerService.HintSize="858,201">
       <If.Then>
         <Assign DisplayName="Compute build path" sap:VirtualizedContainerService.HintSize="291,100">
           <Assign.To>
             <OutArgument x:TypeArguments="x:String">[BuildLocation]</OutArgument>
           </Assign.To>
           <Assign.Value>
             <InArgument x:TypeArguments="x:String">[If(LabWorkflowParameters.BuildDetails.Configuration Is Nothing, BuildLocation, If(LabWorkflowParameters.BuildDetails.Configuration.IsEmpty Or (SelectedBuildDetail.Information.GetNodesByType(Microsoft.TeamFoundation.Build.Common.InformationTypes.ConfigurationSummary, True)).Count = 1, BuildLocation, If(LabWorkflowParameters.BuildDetails.Configuration.IsPlatformEmptyOrAnyCpu, BuildLocation + "\" + LabWorkflowParameters.BuildDetails.Configuration.Configuration, BuildLocation + "\" + LabWorkflowParameters.BuildDetails.Configuration.Platform + "\" + LabWorkflowParameters.BuildDetails.Configuration.Configuration)))]</InArgument>
           </Assign.Value>
         </Assign>
       </If.Then>
     </If>
 After:
 <If Condition="[LabWorkflowParameters.BuildDetails.IsTeamSystemBuild = True]" DisplayName="Compute build location needed" sap:VirtualizedContainerService.HintSize="858,201">
       <If.Then>
         <Assign DisplayName="Compute build path" sap:VirtualizedContainerService.HintSize="291,100">
           <Assign.To>
             <OutArgument x:TypeArguments="x:String">[BuildLocation]</OutArgument>
           </Assign.To>
           <Assign.Value>
             <InArgument x:TypeArguments="x:String">[BuildLocation]</InArgument>
           </Assign.Value>
         </Assign>
       </If.Then>
     </If>

Wednesday, October 12, 2011

SQLCMD - QUOTED_IDENTIFIER is OFF

If you want to deploy the Differential script on a Database in an automated way, the most common way is to use SQLCMD. For example:

sqlcmd -S Server-d Database1 -U UserName -P Password -i DatabaseBuild-ProdDiff.sql

However, when you run this script, QUOTED_IDENTIFIER is set to OFF

When running the stored procedures, you might see errors like:
Message: UPDATE failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'.

The reason for this is that QUOTED_IDENTIFIER is not set to ON
To set it to ON, you should pass the -I argument, For example:
sqlcmd -I -S Server-d Database1 -U UserName -P Password -i DatabaseBuild-ProdDiff.sql

Thursday, September 29, 2011

Windows Azure Tools 1.4 - Error: Certificate: ‘CertName’ with Thumbprint: xxx for Role: RoleName has not been uploaded to the hosted service: ServiceName.

Consider your Windows Azure role has a configuation like:

<Certificates>
  <Certificate name="CertName" thumbprint="xxx" thumbprintAlgorithm="sha1" />
</Certificates>

When you try to deploy to cloud from Visual Studio by right clicking the Windows Azure project and selecting Publish, you might see a log like this:
5:20:37 PM - Preparing...
5:20:37 PM - Connecting...
5:20:38 PM - Error: Certificate: ‘CertName’ with Thumbprint: xxx for Role: RoleName has not been uploaded to the hosted service: ServiceName.


The reason for this error could be that the Certificate Thumbprint which is displayed on the Windows Azure Portal is in UpperCase and the one specified in your configuration is in LowerCase or vice versa.

The fix for this is to copy the Thumbprint from Windows Azure Portal  and paste it in your configuration so that the case match at both places.