What are the challenges you face when working across database platforms? Take the survey
Options

Programmable Objects Disabled in Invoke-DatabaseBuild

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.
Tagged:

Best Answer

  • Options
    Ivo_MillerIvo_Miller Posts: 31 Silver 3
    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
    RedGate Software Developer

Answers

  • Options
    rconradrconrad Posts: 9 New member
    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>

  • Options
    rconradrconrad Posts: 9 New member
    Found it.  I had not specified a server on Invoke-DatabaseBild so it was probably using localdb.  Thanks for your help.
  • Options
    ullullullull Posts: 9 New member
    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
  • Options
    ullullullull Posts: 9 New member
    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> 
Sign In or Register to comment.