SQL Server database project issue using BulkColumn in view

Question

I'm using SQL Server database project to deploy objects to Azure SQL database. I have a view using common table expression with the below code. This object is part of build and run into the following error SQL71005: The reference to the column "BulkColumn" could not be resolved.

SQL

WITH cte   
AS   
(  
SELECT BulkColumn  
  FROM OPENROWSET(  
  BULK 'azurestorage/blobpath.json',  
  DATA_SOURCE = 'azureblobcontainer',  
  SINGLE_CLOB) AS bc  
)  

Could you please help me with a solution by not changing the Build Action -> None

Thank you

Answer 1

A workaround is to embed the query in dynamic SQL

   SELECT @sql = '     WITH cte   
        AS   
        (  
        SELECT BulkColumn  
          FROM OPENROWSET(  
          BULK ''azurestorage/blobpath.json'',  
          DATA_SOURCE = ''azureblobcontainer'',  
          SINGLE_CLOB) AS bc  
        )  

And, yes, it's ugly. It would certainly be better if SSDT could get its act together.

You can report bugs here: https://feedback.azure.com/d365community/forum/04fe6ee0-3b25-ec11-b6e6-000d3a4f0da0.

Answer 2

Hi @Raj D ，

Essentially it is telling you that there is a reference to some object in your procedure that is invalid, it might just be a plain and simple broken procedure and have not got all the objects yet. you may need to check if your stored procedure is valid

https://techcommunity.microsoft.com/t5/sql-server-blog/importing-json-files-into-sql-server-using-openrowset-bulk/ba-p/384480

-------------

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

An MVP colleague suggested a better workaround, define the column alias explicitly:

   WITH cte   
        AS   
        (  
        SELECT BulkColumn  
          FROM OPENROWSET(  
          BULK 'azurestorage/blobpath.json',  
          DATA_SOURCE = 'azureblobcontainer',  
          SINGLE_CLOB) AS bc(BulkColumn)  
        )  

He did not have the possibility to test it, but I would be quite disappointed if it does not work.