locked
Unresolved reference to object sp_executesql in dwh database project RRS feed

  • Question

  • Hi,

    I am using the (last week released) VS2019 (build 16.3) with a Azure Data warehouse database project. In it I created a stored procedure which uses sp_executesql to execute a sql string. The SP works fine in azure dwh but when I have the SP object in my VS db project I get the error: Severity Code Description Project File Line Suppression State
    Error  SQL71502: Procedure: [dbo].[uspXXX] has an unresolved reference to object [dbo].[sp_executesql].

    Normally you would solve this by adding a database reference to the master db to your project like described here: https://code-examples.net/en/q/1141f9d. I have tested this and this works for ALL database target platforms EXCEPT MS Azure SQL Data Warehouse. So my suspicion is that this (and other?) system sp's are not added to the master.dacpac reference for AzureDwh. I think it should be added because the sp IS supported. Consequence of this, is that I can't build, and therefore, not release my database project.

    Hope this can be fixed and any ideas for a workaround would obviously be greatly appreciated.

    Kind regards,
    Thijs

    Thursday, September 26, 2019 8:14 AM

All replies

  • Hi Thijs

    We are checking this and will get back to you as soon as possible.

    Thanks
    Navtej S

    Friday, September 27, 2019 4:12 PM
  • Hi Thijs, 

    I'm trying to reproduce your issue. I've downloaded the latest VS (16.3), added master as a database reference, and created a simple stored procedure with the following:

    CREATE PROC [dbo].[sp_executesqlRepro]
    AS
    BEGIN
    EXECUTE sp_executesql   
              N'SELECT * from sys.tables';
    END

    It builds successful for me. Can you send me your SP definition?

    Friday, September 27, 2019 9:26 PM
  • Hi Navtej,

    Thanks for checking this out!

    Don't you get the 'unresolved reference' message as warning either? In the project properties on the 'build' tab I have checked the box 'Treat Transact-SQL warnings as errors'. This settings is mandatory and enforced at our company. If I uncheck it, the errors are gone and replaced by warnings. I can build this way but I still can't release it without this setting because of the company policy. And to be honest, I would like to get rid of the warnings as well.

    To test it a bit further, I have created a blank new solution with only a db project and your sp. I get the same reference warning as I get in my own project. Adding the master as reference database has no effect. Once I check the 'Treat Transact-SQL warnings as errors' checkbox I (obviously) can't build the project anymore.

    I have also tried opening this project with VS 2019 Preview 16.4.0 (preview 1.0) but this has the same result.

    Hope this helps investigating the problem a bit further. Do you still want me to sent you my own sp's?

    Kind regards, Thijs

    Monday, September 30, 2019 6:49 AM
  • Thanks Thijs.

    Yes, I get the warning as well. I have created an defect for our team to take a look at. 

    Thanks,

    Kevin

    Tuesday, October 1, 2019 10:11 PM
  • Hi Thijs

    As confirmed by our Product team member, they have created a defect on their end regarding the same. It will be prioritized accordingly.

    We will have a better answer for you in future but current answer is that we have created this defect and looking into it.

    Thanks
    Navtej S

    Wednesday, October 2, 2019 3:33 PM
  • Hi Navtej,

    Thanks for your reply and taking time to look into this. Please let me know if you want me to re-test this issue in any of the upcoming vs2019 preview releases.

    Kind regards

    Thursday, October 3, 2019 4:48 AM
  • Hi Thijs

    We will definitely get back to you once we have a solution rolled out in a future release.

    Thanks
    Navtej S

    Thursday, October 3, 2019 4:28 PM
  • Same problem here..Building a SQL DW project and a warning occurs
    Tuesday, November 19, 2019 8:03 AM
  • Hi Hennie

    The created defect is still under review and no solution as of now. 

    Thanks
    Navtej S

    Tuesday, November 19, 2019 4:04 PM