ReadyRoll project - Create external data source fails (BLOB_STORAGE)

Allaire

Hello colleagues,

I'm trying to create an external data source using "Create external data source" topic.
Locally, it works just fine and when I execute the script to bulk insert the file from Azure Storage, it works (even though SSMS and Visual Studio highlights 'BLOB_STORAGE' with red):

IF NOT EXISTS (SELECT * FROM sys.external_data_sources WHERE name = 'AzureBlobStorage')
CREATE EXTERNAL DATA SOURCE AzureBlobStorage
WITH ( TYPE = BLOB_STORAGE, LOCATION = 'https://myblob.blob.core.windows.net');

--BULK INSERT from Blob Storage
BULK INSERT [dbo].[Document]
FROM 'files/Documents.csv'
WITH (DATA_SOURCE = 'AzureBlobStorage', KEEPIDENTITY, FIELDTERMINATOR = '|', FIRSTROW = 2, ROWTERMINATOR = '\n', KEEPNULLS);

Documentation states that SQL Server (on-premise) and SQL Server in Azure support all that kind of things starting with version 2016. I'm using SQL Server 2017 Developer Edition, SSMS version 17.3, ReadyRoll - latest update (1.14.20).
The reason of my question is that I'm trying to develop a CI pipeline for database promotion to Azure using Visual Studio ReadyRoll project for that purpose. It compiles when I set 'Target Platform' to SQL Server 2017 in the project properties (but keep highlighting BLOB_STORAGE as an error, just like SSMS does, but T-SQL statement is correct!).
T-SQL expression I provided above works just fine locally and creates External data source, the data is successfully bulk-inserted from Azure Storage. It works up until the moment I commit the code to VSTS and when the build pipeline is executed I receive an error "SQL80001: Incorrect syntax near 'BLOB_STORAGE'. Expecting CEDS_HADOOP, CEDS_RDBMS, CEDS_SHARDMAPMANAGER, or CEDS_SHAREDMEMORY".
It seems that "Target platform" flag I set in the project properties does not affect anything:


Any ideas?

AdamB

Another possible workaround is to stop providing your builds with the "ShadowServer" property - this will mean that your scripts are not parsed as part of the build process. The downside is that legitimate SQL errors will also not be caught and no snapshot of the database schema will be saved to help with the generation of diff and drift reports.

A bit more information: the reason the builds pass in Visual Studio is that by default ReadyRoll doesn't verify the scripts as part of a Visual Studio build. If you untick the option to "Skip Shadow database deployment when building inside Visual Studio" in the Debug pane of the Project Properties, then the Visual Studio builds will likely also start failing.

AdamB

Hi,

From the above information it's not clear whether the syntax error is occurring during deployment of the shadow or the target - the full build log may provide this information (you may be able to tell by looking at it yourself, or you can post the relevant section here). Is the shadow database you use in VSTS the same version of SQL Server as the target, and the same version as you use locally?

Allaire

Hi @AdamB,

Thanks for your reply. In fact, it doesn't build in VSTS at all. When I build the project in Visual Studio (with 2017 SQL as a target platform) - it builds just fine. I realized VSTS only uses MSBuild and tried the same locally - it failed as well (the error is all the same - error : Incorrect syntax near BLOB_STORAGE).
The question - how to make it buildable.

p.s. all said above is easy to reproduce. It may not have direct connection to RR product. Well, it turned out it is a bug is SQL Server Engine and I already submitted a ticket. Installing new SSDT package (which is in preview now) didn't help too. The question - is there any workarounds?

AdamB

Without seeing the build logs, I don't have any suggestions. If you can send the full output of the build to support@red-gate.com and mention the URL of this forum post in the ticket, we can see if there's anything we can do.

Allaire

Just sent an email along with build log.

AdamB

It seems like there is a problem with the script parser - we will look into whether there are any updates available to it that address this issue. In the meantime, a possible workaround is to wrap the BULK INSERT statements in dynamic SQL, e.g. "EXEC('BULK INSERT...');". Let us know if this helps.

AdamB

On further investigation it looks like this might not fix the problem - I'll get back to you if I make any more progress.

AdamB

Since this post was created a new version of the parsing library we use has been released - this should be included in next week's release. You may still see the red underlining when you don't expect it, but the script should parse.

Once this update is released, you may need to make some changes to ensure that the shadow server you use supports this syntax. At present ReadyRoll defaults to using LocalDB, which uses SQL Server 13.0 - you will need to provide a SQL Server 2017 instance as the "ShadowServer" property; SQL Server 2017 Express can provide such an instance.

Allaire

Hi Adam,
Thanks a lot for your answer, it's been very helpful. Until this bug is resolved by MS, I decided to go with dynamic expressions as you suggested and it works just fine. VSTS builds solution without any issues as well. Looking forward for new RR release

Allaire

Hi Adam,

I'm having another issue related to this one I explained here: https://forum.red-gate.com/discussion/82232/an-error-occurred-while-attempting-to-deploy-project-scripts-eds-cache/p1?new=1
Not sure if this is related to RR or another SQL Server bug. Could you help to resolve?