SSDT failing to compile if STRING_SPLIT is used - by Frederic Chapelon

Status : 

 


28
0
Sign in
to vote
ID 2906200 Comments
Status Active Workarounds
Type Bug Repros 9
Opened 7/7/2016 10:20:35 PM
Access Restriction Public

Description

Hi
I have upgraded my visual studio 205 and SSDT to the latest versions as I now use SQL Server 2016
With that move, I am intending to use the new STRING_SPLIT function.
However, my database project does not compile and I am returned a 71501 error
I checked my database project and I am in compatibility 130
What can I do to fix that?

Thanks
Sign in to post a comment.
Posted by Nick Capito UBT on 7/27/2017 at 12:24 PM
Please fix this! We can't upgrade to VS2017 until this is resolved. Basically if we use string_split in a table value function it says it can't resolve the reference.



Error        SQL71501: View: xxxx contains an unresolved reference to an object. Either the object does not exist or the reference is ambiguous because it could refer to any of the following objects: [dbo].[string_split] or [string_split].    xxx\Views\vwxxxx.sql    15    
Posted by Brian Brennan1 on 6/19/2017 at 12:55 PM
I'm also still seeing part of this issue.

VS2017 Pro Version 15.2 (26430.13) Release
SQL Server Data Tools 15.1.61702.140

The build compiles and publishes OK, but IntelliSense still underlines the STRING_SPLIT function and it's parameters in blue and incorrectly produces the following warning:

Severity    Code    Description    Project    File    Line    Suppression State
Warning        SQL71502: Procedure: [Schema].[ProcedureName] contains an unresolved reference to an object. 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].

The code to produce the warning:

SELECT @ServerName = [value] FROM STRING_SPLIT(@FullFilename,'\') WHERE [value] LIKE '%SQL%'

@FullFilename is just a UNC path like '\\FileRepo\SomeSubdirectory\MYSQLBOX\somefilename.sqlaudit'
Posted by Paul-PCE on 5/30/2017 at 6:58 PM
I'm using VS2017 - 15.2 (26430.12) Release, the latest as of this morning, with SSDT 15.1.61702.140.

Still happening.
Posted by Frederic Chapelon on 5/18/2017 at 6:58 PM
Finally!!!!!!!!!!!!!

It works. Thanks for the update.
Now I can build my DB project and finally use that function in our deployed environments..

Thanks

Regards

Fred
Posted by Arvind Shyamsundar on 5/18/2017 at 11:41 AM
@j055: The issue should no longer occur with SSDT and VS2015 (SQL Server Data Tools 14.0.61704.140). Please also ensure that you update your DACFX runtime to the latest (17.0.1) available from https://www.microsoft.com/en-us/download/details.aspx?id=55114 - the x86 version is needed. You might be running an older DACFX runtime somehow.
Posted by j055 on 4/25/2017 at 6:06 AM
I'm using VS 2017 and SSDT 14.0.61704.140. I get the error:
Error SQL71501: Function: [dbo].[ufnGetIntListFromString] has an unresolved reference to object [STRING_SPLIT].[value].

Please advise
Posted by Microsoft on 3/30/2017 at 2:45 AM
Some fixes have been added to SSDT regarding STRING_SPLIT.
If you continue to experience the issue, please try out the next SSDT release.

Thanks
Posted by bhendry on 1/26/2017 at 12:08 PM
I used a technical support incident to inquire with Microsoft about the issue. It is known and there is no date scheduled for resolution. They suggested continuing to monitor this Connect thread for future status updates.
Posted by bhendry on 1/24/2017 at 4:33 PM
The issue is not addressed in the latest release (SSDT 14.0.61021.0). For me in VS2015, SPLIT_STRING will compile with warnings when used in a stored procedure as a table valued function and will not compile at all when used in a user-defined table valued function; it errors and the database project is unable to build.

See the Kevin Cunnane reply to Fred in the release comments at https://blogs.msdn.microsoft.com/ssdt/2016/10/20/sql-server-data-tools-16-5-release/ for the most current response I could find on the issue.

For now, going back to a custom udf until it's resolved. :(
Posted by MKipp on 1/23/2017 at 3:18 AM
I also have the same issue in version SSDT 14.0.61021.0.

It seems that there is a mix-up with the kind of function STRING_SPLIT is (so not a table valued function).
If I use

SELECT @someVar = STRING_SPLIT('1,2,3', ',')

the compiler doesn't give a warning or error, but

SELECT * FROM STRING_SPLIT('1,2,3', ',')

does generate an error. In my case it is error 71502, not 71501.
Posted by geochatz on 1/23/2017 at 2:24 AM
I am also having the same issue. Any news on that?
Posted by aldo79 on 11/1/2016 at 7:35 AM
We are also still having issues with this after downloading the most recent SSDT build. Running SSDT 14.0.61021.0 in VS 2015 14.0.25424.00 Update 3 and still seeing error SQL71501 when we try to build the project and have a reference to the STRING_SPLIT function.
Posted by Frederic Chapelon on 10/28/2016 at 5:07 PM
@texasrebelaggie
Happy that it works for you
However I've downloaded the latest and still have the issue in Visual Studio 2015
the code I use is below.
Would someone be able to tell me if something is wrong in it? use the STRING_SPLIT this way as the function existed for years using xml to split the string and I don't want to change the code calling the function.

CREATE FUNCTION [dbo].[ufn_StringSplit]
(
    @String varchar(max),
    @Delimiter varchar(1)
)
RETURNS TABLE
AS
RETURN
(    
    SELECT value AS ItemValue
    FROM STRING_SPLIT(@String, @Delimiter)
)

Thanks in advance
Posted by texasrebelaggie on 10/28/2016 at 1:39 PM
Howdy. Just downloaded SSDT 14.0.61021.0 (October 26 2016 release) and this appears to be resolved. My stored procedures and functions that use string_split are no longer generating warnings or errors and successfully built and deployed solution.

(https://msdn.microsoft.com/en-us/mt186501)
Posted by Frederic Chapelon on 10/3/2016 at 9:54 PM
Same as before with the last version (as mentioned by other people)

the STRING_SPLIT is still not recognised by SSDT with version 14.0.60923.0
Posted by KorsG on 10/1/2016 at 2:56 AM
I can confirm texasrebelaggie's claim - I also updated to SSDT 14.0.60923.0 and it is still the same error.
Posted by texasrebelaggie on 9/29/2016 at 9:45 AM
As of SSDT 14.0.60923.0, this is still an issue. (September 26, 2016 update)

Is there any way to work around this? I've added error 71501 to "Suppress Transact-SQL warnings" to no avail.
Posted by Microsoft on 9/15/2016 at 12:12 PM
Hi Frederic,

STRING_SPLIT support has been added to SSDT. It should be available in the newest SSDT release.

Thanks