July 5, 2016 at 7:24 am
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.
July 6, 2016 at 1:36 pm
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
July 7, 2016 at 1:51 am
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.
July 7, 2016 at 7:46 am
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
July 7, 2016 at 9:39 am
"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
July 7, 2016 at 9:40 am
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
July 7, 2016 at 9:50 am
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
July 8, 2016 at 9:59 am
Thanks for your thoughts Dixie.
I think you may be right and I need to look elsewhere for an answer.
January 24, 2017 at 5:50 pm
RedLlewy - Friday, July 8, 2016 9:59 AMThanks 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
January 24, 2017 at 10:24 pm
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