Programmable Objects Disabled in Invoke-DatabaseBuild

• 
  Hi,
  
  It is worth double checking that you are using equivalent versions of SQL Change Automation for the PowerShell cmdlets and Visual Studio.
  Until version 4.3, programmable objects were enabled by setting the DeployChangesForProgrammableObjects property to true. New projects created using version 4.3 will instead use the new ProgrammableObjectHandling option (see here for details). Newer versions of the product will continue to support the old property, but if you create a new project using version 4.3 in Visual Studio, you will need to update the version of SQL Change Automation PowerShell to 4.3, or manually add <DeployChangesForProgrammableObjects>True</DeployChangesForProgrammableObjects> to your project file.
  
  All the best,
  
  Ivo Miller

  Ivo_Miller August 04, 2020 11:01

  - 0 +
• 
  The project was originally version 3 and then upgraded to version 4.  I was running a version 3 powershell module.  I upgraded the SqlChangeAutomation module to the latest 4.2.20189.21070 which did not fix the problem.  Then I added the DeployChangesForProgrammableObjects element to the project and that did fix the problem.  Seems like I shouldn't need the DeployChangesForProgrammableObjects since I already had ProgrammableObjectHandling.
  
  I am now getting an error that TRIM() is not recognized.  (If you prefer I can post this as a new question)
  

  
  WARNING: DbBuildTask: ERROR C:\Code\UtilityBilling\Databases\SCA\Client\ClientDB\Programmable Objects\dbo\Stored Procedures\pgb_custo

  mer_SSNSearch.sql(0,0): 'TRIM' is not a recognized built-in function name.
  
  

  I updated the DSP to Microsoft.Data.Tools.Schema.Sql.Sql150DatabaseSchemaProvider which I thought should correct that.
  
  Here is a snippet from the project file. 
  
  

    <PropertyGroup>

      <Configuration Condition=" '$(Configuration)' == '' ">Debug</Configuration>

      <Platform Condition=" '$(Platform)' == '' ">AnyCPU</Platform>

      <Name>ClientDB2</Name>

      <SchemaVersion>2.0</SchemaVersion>

      <ProjectVersion>4.1</ProjectVersion>

      <ProjectGuid>{fd29acf3-02a8-4a8b-bb18-fe70d8ab409f}</ProjectGuid>

      <DSP>Microsoft.Data.Tools.Schema.Sql.Sql150DatabaseSchemaProvider</DSP>

      <OutputType>Database</OutputType>

      <RootPath>

      </RootPath>

      <RootNamespace>ClientDB</RootNamespace>

      <AssemblyName>ClientDB</AssemblyName>

      <ModelCollation>1033, CI</ModelCollation>

      <DefaultFileStructure>BySchemaAndSchemaType</DefaultFileStructure>

      <DeployToDatabase>True</DeployToDatabase>

      <TargetFrameworkVersion>v4.5.2</TargetFrameworkVersion>

      <TargetLanguage>CS</TargetLanguage>

      <AppDesignerFolder>Properties</AppDesignerFolder>

      <SqlServerVerification>False</SqlServerVerification>

      <IncludeSchemaNameInFileName>True</IncludeSchemaNameInFileName>

      <IsSqlChangeAutomationProject>True</IsSqlChangeAutomationProject>

      <MinimumEngineVersion>4.2</MinimumEngineVersion>

      <SqlChangeAutomationSchemaVersion>1.12</SqlChangeAutomationSchemaVersion>

      <DeployOnceSubFolder>Migrations</DeployOnceSubFolder>

      <DeployChangesForProgrammableObjects>True</DeployChangesForProgrammableObjects>

      <DeployChangesSubFolder>Programmable Objects</DeployChangesSubFolder>

      <DeployChangesImportSchemaFolders>True</DeployChangesImportSchemaFolders>

      <PreDeploymentSubFolder>Pre-Deployment</PreDeploymentSubFolder>

      <PostDeploymentSubFolder>Post-Deployment</PostDeploymentSubFolder>

      <GenerateSqlPackage>False</GenerateSqlPackage>

      <MigrationOrdering>FilePath</MigrationOrdering>

      <EvaluateExecutionOrderOnImport>True</EvaluateExecutionOrderOnImport>

      <Provisioner>CustomScripts</Provisioner>

      <TargetDatabaseSet>True</TargetDatabaseSet>

      <BuildOptionSkipShadowDeployVS>True</BuildOptionSkipShadowDeployVS>

      <DeployChangesInitialImportDone>True</DeployChangesInitialImportDone>

      <SccProjectName>SAK</SccProjectName>

      <SccProvider>SAK</SccProvider>

      <SccAuxPath>SAK</SccAuxPath>

      <SccLocalPath>SAK</SccLocalPath>

      <ProgrammableObjectHandling>UseRepeatableScriptsForAllObjects</ProgrammableObjectHandling>

    </PropertyGroup>

  
  

  rconrad August 04, 2020 14:28

  - 0 +
• 
  Found it.  I had not specified a server on Invoke-DatabaseBild so it was probably using localdb.  Thanks for your help.

  rconrad August 04, 2020 17:00

  - 0 +
• 
  I am having the same issue. When I run Invoke-DatabaseBuild, I see: 
  
  WARNING:  DbBuildTask: Warning C:\Users\ullull\source\repos\_SSISDB\_Database\CSI_DW_Migrations\Programmable Objects\ETL\Functions\DelimitedString_Tokenize.sql(0,0): Programmable Objects are d
  isabled but there are 1 script(s) present in the Programmable Objects folder (eg. "Programmable Objects\ETL\Functions\DelimitedString_Tokenize.sql"). These scripts will not be included in the
  build. If you wish to include them in the build, please enable Programmable Objects in Project Settings. 
  
  I am new to SCA and do not know why my programmable objects are being excluded. It is not clear where Project Settings are defined.  If it's in the .sqlproj file, it is already configured with 
  
  <ProgrammableObjectHandling>UseRepeatableScriptsForAllObjects</ProgrammableObjectHandling>
  
  So that's not it.  So my question remains: why are programmable objects being excluded and what do I need to change to include them?  I do not follow the poster's comment about specifying a server on Invoke -DatabaseBuild.  I did exactly that to no effect:
  
  $ValidatedProject = $MyProject | Invoke-DatabaseBuild -TemporaryDatabaseServer $Db02Conn
  

  ullull September 03, 2020 19:24

  - 0 +
• 
  After re-reading this carefully, I see that my issue was in part due to my being on the older version of SCA (4.2.20189). Adding the option below solved the issue:
  <DeployChangesForProgrammableObjects>True</DeployChangesForProgrammableObjects> 
  

  ullull September 26, 2020 15:02

  - 0 +

Add comment

Please sign in to leave a comment.