Programmable Objects Disabled in Invoke-DatabaseBuild

rconrad

I'm getting the warning below when I build my project using Invoke-DatabaseBuild and indeed the programmable objects are not included, but when I build in VS they work fine.  I cannot find any place that I have disabled programmable objects in my project.  Where do I need to look?

WARNING: DbBuildTask: Warning C:\Code\UtilityBilling\Databases\SCA\Client\ClientDB\Programmable Objects\dbo\Functions\enum_Command_AutoDraft.sql(0,0): Programmable Objects are disabled but there are 1979 script(s) present in the Programmable Objects folder (eg. "Programmable Objects\dbo\Functions\enum_Command_AutoDraft.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.

Ivo_Miller

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

rconrad

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

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

ullull

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

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>