Forum Discussion

AnmolGan81's avatar
AnmolGan81
Advocate II
3 months ago
Solved

Publishing into Target Warehouse using VS Code causing issues

I am trying to publish existing warehouse to another target warehouse using VS Code, when I try to publish it I get below error, some of my tables are using dynamic sql related to INFROMATION_SCHEMA....
data warehouse
Warehouse
  • v-moharafi-msft's avatar
    v-moharafi-msft
    3 months ago

     

    Hi AnmolGan81 ,

     

    Thanks for your Patience.

     

    Yes, post-deployment scripts can be used as part of the SQL Database Project deployment flow in a DevOps pipeline. In this scenario, they are particularly useful because the issue is occurring during design-time validation rather than during runtime execution inside the Fabric Warehouse.

    A practical workaround is to separate the main schema deployment from the stored procedures containing dynamic SQL or unsupported dependency references.

    Edit the .sqlproj file directly and add a <PostDeploy> item inside the <ItemGroup> section:

    <ItemGroup>
    <PostDeploy Include="scripts\after-script.sql" />
</ItemGroup>

Similarly, pre-deployment scripts can be added using:

<ItemGroup>
    <PreDeploy Include="scripts\before-script.sql" />
</ItemGroup>

    When the SQL project is built, these scripts are packaged into the generated DACPAC. During deployment through the DevOps pipeline using tools such as SqlPackage or DACPAC deployment tasks, the execution order is handled automatically:

    1.Pre-deployment script
    2. Schema deployment/publish
    3. Post-deployment script

    Since post-deployment scripts execute after schema publish completes, they may help bypass the dependency validation failures occurring during the build/publish phase.

    Hopefully, separating the procedures containing dynamic references from the primary schema deployment helps resolve the deployment validation issue in your scenario

     

    For more information refer to the documentation:

    add-pre-deployment-and-post-deployment-scripts 

    Best Regards,
    Abdul Rafi

     

Lodha_Jaydeep's avatar
Lodha_Jaydeep
Solution Sage
3 months ago

Hi AnmolGan81,

 

Thanks for reaching fabirc community, will happy to asssit.

 

The error is happening during deployment validation/publish, not necessarily because the procedure will fail at runtime.

The VS Code/Fabric SQL project deployment is checking object dependencies. If a stored procedure contains dynamic SQL that references objects such as:

[INFORMATION_SCHEMA].[COLUMNS]
[SYS].[SP_EXECUTESQL]
 

INFORMATION_SCHEMA.COLUMNS and sys.sp_executesql are system objects, so you normally do not create them in the target warehouse. But the publish process is parsing the stored procedure and treating those references as dependencies that must exist in the target project/database.

 

A few things you can try:

  1. Confirm the procedure runs manually in the target warehouse after deployment.
  2. Deploy the table objects first, then deploy procedures separately.
  3. Exclude or post-deploy the affected stored procedures instead of deploying them as normal project objects.
  4. Move dynamic SQL procedures into a post-deployment script.
  5. Avoid fully qualifying system objects in a way the deployment parser treats as user dependencies.

For example, if INFORMATION_SCHEMA.COLUMNS works when queried directly in the target warehouse, then the problem is likely the deployment analyzer, not the object itself.

Please consider as an accepted solution if it helps or give some kudos.

Thanks

AnmolGan81's avatar
AnmolGan81
Advocate II
3 months ago

Hi thanks for your response,

I cant run the stored procedure into fabric warehouse untill its deployed so cant test it

I cant deploy tables objects first and the deploy sp, because the publish option do the same thing to deploy table object first the sp,so it seems something wrong with microsoft's functionality of publish if we have dynamic sql written on some sp's.

When I exclude these stored procedurs in the VS Project then I can deploy it so it seems these cant be deployed together.

Its weired that we didnt faced such issues during sql datawarehouse project creation and deployment when were into the SQL DW.

 

v-moharafi-msft is this a limitation of the product and something is being planned or we need to figure out workaround as usual?

  • v-moharafi-msft's avatar
    v-moharafi-msft
    Community Support
    3 months ago

    Hi AnmolGan81,

     

    Thank you for validating the behaviour through your testing.

     

    This appears to be related to the SQL Database Project build and publish validation process rather than the runtime execution of the stored procedures themselves.

     

    Microsoft documentation states that SQL Database Projects validate object references and dependencies during the build phase before generating and publishing the dacpac. The documentation also notes that system objects are not included in the project model by default, which can lead to validation errors for references to system objects during build and publish validation.

     

    In your scenario, dynamic SQL references involving sys.sp_executesql and INFORMATION_SCHEMA.COLUMNS appear to be contributing to the dependency validation failure even though those objects are available at runtime.

    Since deployment succeeds when the affected procedures are excluded, but fails when they are included, this seems related to the current dependency validation handling for certain dynamic SQL patterns in Fabric Warehouse SQL Projects rather than deployment order or stored procedure logic itself.

     

    As a possible workaround, you could try moving the affected procedures into post-deployment scripts or simplifying the dynamic SQL temporarily to isolate the specific dependency reference causing the validation failure.

     

    Relevant Microsoft documentation:

    SQL Database Projects Overview
    https://learn.microsoft.com/en-us/sql/tools/sql-database-projects/sql-database-projects

    Database References
    https://learn.microsoft.com/en-us/sql/tools/sql-database-projects/concepts/database-references

    Pre/Post Deployment Scripts
    https://learn.microsoft.com/en-us/sql/tools/sql-database-projects/concepts/pre-post-deployment-scripts

     

    Best Regards,
    Abdul Rafi

     

    • AnmolGan81's avatar
      AnmolGan81
      Advocate II
      3 months ago

      Since our main objective is to deploy those objects using a devops pipeline can you help is in the right direction on how can we deploy post deployment script from devops pipeline into a warehouse?

      • v-moharafi-msft's avatar
        v-moharafi-msft
        Community Support
        3 months ago

         

        Hi AnmolGan81 ,

         

        Thanks for your Patience.

         

        Yes, post-deployment scripts can be used as part of the SQL Database Project deployment flow in a DevOps pipeline. In this scenario, they are particularly useful because the issue is occurring during design-time validation rather than during runtime execution inside the Fabric Warehouse.

        A practical workaround is to separate the main schema deployment from the stored procedures containing dynamic SQL or unsupported dependency references.

        Edit the .sqlproj file directly and add a <PostDeploy> item inside the <ItemGroup> section:

        <ItemGroup>
    <PostDeploy Include="scripts\after-script.sql" />
</ItemGroup>

Similarly, pre-deployment scripts can be added using:

<ItemGroup>
    <PreDeploy Include="scripts\before-script.sql" />
</ItemGroup>

        When the SQL project is built, these scripts are packaged into the generated DACPAC. During deployment through the DevOps pipeline using tools such as SqlPackage or DACPAC deployment tasks, the execution order is handled automatically:

        1.Pre-deployment script
        2. Schema deployment/publish
        3. Post-deployment script

        Since post-deployment scripts execute after schema publish completes, they may help bypass the dependency validation failures occurring during the build/publish phase.

        Hopefully, separating the procedures containing dynamic references from the primary schema deployment helps resolve the deployment validation issue in your scenario

         

        For more information refer to the documentation:

        add-pre-deployment-and-post-deployment-scripts 

        Best Regards,
        Abdul Rafi