Publishing into Target Warehouse using VS Code causing issues

[AnmolGan81]

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.COLUMNS or sys.sp_executesql, below is what I am getting when I try to publish this and all the tables or sp's which have dynamic sql are getting failed causing whole publishing into target to fail if anyone faced the same please post.

Errors
This deployment may encounter errors during execution because "some stored procedure I used" 

depends on [sys].[sp_executesql] and [sys].[sp_executesql] does not exist in the target database.

This deployment may encounter errors during execution because "some stored procedure I used"  depends on [INFORMATION_SCHEMA].[COLUMNS] and [INFORMATION_SCHEMA].[COLUMNS] does not exist in the target database.

[v-moharafi-msft]

Hi  @AnmolGan81 ,

Thank you for reaching out to Microsoft Fabric Community and Thanks to @Lodha_Jaydeep   for Sharing valuable insights.

Just wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. 

 Best Regards,

Abdul Rafi.

[Lodha_Jaydeep]

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]

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]

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-scrip...

Best Regards,
Abdul Rafi

[AnmolGan81]

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?