SQL71502 Error in SSDT when referencing STRING_SPLIT function.

  • High all,

    I am getting the following warning in SSDT:

    "SQL71502: Procedure: [Execution].[MyStoredProc] 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 [Execution].[STRING_SPLIT]."

    The error is in the SP ( this is a simplified version that shows the error and is not the real SP)

    CREATE PROCEDURE [Execution].[MyStoredProc]

    @String1 NVARCHAR(4000) --Parameter1

    AS

    SET NOCOUNT ON;

    BEGIN TRY

    SELECT

    DISTINCT [Value]

    FROM

    STRING_SPLIT(@String1, ',');

    END TRY

    BEGIN CATCH

    THROW;

    END CATCH;

    We have the MASTER db added as a project reference and have tried fully qualifying the Function as

    SELECT

    DISTINCT [Value]

    FROM

    [dbo].[STRING_SPLIT](@String1, ',');

    and

    SELECT

    DISTINCT [Value]

    FROM

    [master].[dbo].[STRING_SPLIT](@String1, ',');

    Has anyone else encountered this?

    Or is there something I am missing here?

    Many thanks.


    RedLlewy
    "The Plural of Anecdote is not Data"

  • Are you running SQL 2016 or Azure?

    Per MSDN:

    The STRING_SPLIT function is available only under compatibility level 130. If your database compatibility level is lower than 130, SQL Server will not be able to find and execute STRING_SPLIT function. You can change a compatibility level of database using the following command:

    ALTER DATABASE DatabaseName SET COMPATIBILITY_LEVEL = 130

    Note that compatibility level 120 might be default even in new Azure SQL Databases.

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • Thanks for the reply Dixie.

    We are running SQL2016 and the project target platform is SQL2016.

    I've checked the Compatibility level and it is 130.

    The SP deploys and executes without issue - the error is a code analysis warning in SSDT

    (Using Visual Studio Enterprise 2015 V14.0.25123.00 Update 2 and SSDT v14.0.60525.0).

    Functionally everything works as expected - it's just that we are required to clear them as part of our QA process.


    RedLlewy
    "The Plural of Anecdote is not Data"

  • I've got no other ideas at this time. I'm still waiting for a 2016 dev environment so I can't contribute anything more. Sorry.

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • "SQL71502: Procedure: [Execution].[MyStoredProc] 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

    [Execution].[STRING_SPLIT]."

    You don't have a homegrown version of STRING_SPLIT in the [Execution] schema do you? Maybe someone created a synonym?

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • Ignore previous post. I forgot you had tried qualifying the name to dbo.String_Split.

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • Final thought: This isn't necessarily a SQL question. You're really dealing with a quirk of Visual Studio and SSDT. You might try another forum.

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • Thanks for your thoughts Dixie.

    I think you may be right and I need to look elsewhere for an answer.


    RedLlewy
    "The Plural of Anecdote is not Data"

  • RedLlewy - Friday, July 8, 2016 9:59 AM

    Thanks for your thoughts Dixie.I think you may be right and I need to look elsewhere for an answer.

    Open bug on Microsoft Connect. Supposed to have been resolved in an SSDT release last year but wasn't.

    https://connect.microsoft.com/SQLServer/feedback/details/2906200

  • Although STRING_SPLIT is a native function to SQL 2016, it is not very efficient.

    You will get better performance if you use the following SQL STRING SPLITTER

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply