Friday 19 January 2018

SSDT Causes a Split!

We use SQL Server Data Tools almost daily and have done so since 2005 when Microsoft release Visual Studio for Database Professionals. It was the first tool to put SQL databases under source control using a fully integrated IDE. Not only that, it also validated our code during Build by deploying it to a SQL Express instance that was deployed as part of the tool. For the first time we could start treating database code in the same way as our .Net assembly code and the like.

So you can imagine the look on my face when making a change to a SQL Server 2016 database project yesterday when SSDT decided to throw an exception because I used the STRING_SPLIT() function in my code. After all these years of trusting the tool to keep me right, it was now telling me lies because it didn't recognize the function. I had tested my code in Management Studio and knew that it was definitely valid so I assumed that SSDT was not fully compliant with the latest SQL version.

I checked my project properties to ensure that the Target platform was set to SQL Server 2016 and Compatibility level set to 130. I did another build just to make sure but sure enough, the exception persisted:

Either the object does not exist or the reference is ambiguous because it could refer to any of the following objects: [dbo].[STRING_SPLIT].[value] or [STRING_SPLIT].[value].

I googled, naturally, and found this: https://connect.microsoft.com/SQLServer/feedback/details/2906200/ssdt-failing-to-compile-if-string-split-is-used

Which to help you out just basically says 'yup, we know it's a problem' and then at the bottom it says they have fixed it in the latest release of SSDT. Phew! I downloaded the latest version for VS 2015 and whaddayaknow, it works!

Later Dudes!