Just the other day one of the readers sent me following email after reading my earlier blog post: STRING_SPLIT Function Performance Comparison. He really liked the new feature of SQL Server 2016 and decided to start using this new feature. However, as soon as he started to use this feature, he received following error. He was very much confused why he was not able to use the STRING_SPLIT function even though he had SQL Server 2016.
He attempted to run following command in SQL Query Editor.
DECLARE @VarString NVARCHAR(400) = 'Mike,John,Miko,Matt'; SELECT value FROM STRING_SPLIT(@VarString, ',');
When he tried to run the command he got following error:
Msg 208, Level 16, State 1, Line 7
Invalid object name ‘STRING_SPLIT’.
He immediately wrote to me asking if I know the reason for the error and if there is any way I can help him to fix the error. Honestly, I immediately knew the reason what was the problem for him as many of my customers often faces similar issues.
WORKAROUND/SOLUTION
The primary reason for the error to show up is the compatibility level. The new function STRING_SPLIT is introduced in SQL Server 2016 and if your database compatibility is of SQL Server 2014 or earlier version, you may end up with this error. If you want to use this function, you need to set your compatibility of the level to SQL Server 2016 or later version of SQL Server.
Here is the quick script which can help you to change your compatibility to SQL Server 2016.
ALTER DATABASE [AdventureWorks2014] SET COMPATIBILITY_LEVEL = 130 -- For SQL Server 2016 GO
You should change the compatibility level value to 140 if you want to set your database compatibility to SQL Server 2017.
Once you change your database compatibility level to either SQL Server 2016 or SQL Server 2017, the script just worked fine and produced following results.
Please note that you should set your database compatibility to the level which is appropriate for your database. Changing the compatibility of the database without proper testing may result in errors.
Reference: Pinal Dave (https://blog.sqlauthority.com)
16 Comments. Leave new
I Have Question
please help
i design table and insert data
like
create table tblProduct
(
ProductID Int,
ProductName Nvarchar(50)
);
go
create table tblPurchase
(
PurchaseDt smalldatetime,
PurchaseNo Int,
ProductID int
);
go
create table tblSales
(
SalesDt Smalldatetime,
SalesNo int,
ProductID int
);
go
insert into tblProduct (ProductID, ProductName) values (1,’P1′);
insert into tblProduct (ProductID, ProductName) values (2,’P2′);
insert into tblProduct (ProductID, ProductName) values (3,’P3′);
insert into tblProduct (ProductID, ProductName) values (4,’P4′);
insert into tblProduct (ProductID, ProductName) values (5,’P5′);
insert into tblPurchase (PurchaseDt,PurchaseNo,ProductID) values (‘2018/04/01’, 101, 1);
insert into tblPurchase (PurchaseDt,PurchaseNo,ProductID) values (‘2018/04/05’, 102, 1);
insert into tblPurchase (PurchaseDt,PurchaseNo,ProductID) values (‘2018/04/06’, 103, 1);
insert into tblPurchase (PurchaseDt,PurchaseNo,ProductID) values (‘2018/04/10’, 104, 1);
insert into tblPurchase (PurchaseDt,PurchaseNo,ProductID) values (‘2018/04/15’, 105, 1);
insert into tblPurchase (PurchaseDt,PurchaseNo,ProductID) values (‘2018/04/16’, 106, 2);
insert into tblPurchase (PurchaseDt,PurchaseNo,ProductID) values (‘2018/04/17’, 107, 2);
insert into tblPurchase (PurchaseDt,PurchaseNo,ProductID) values (‘2018/04/21’, 108, 2);
insert into tblPurchase (PurchaseDt,PurchaseNo,ProductID) values (‘2018/04/22’, 109, 2);
insert into tblSales (SalesDt, SalesNo, ProductID) values (‘2018/05/01’, 201, 1);
insert into tblSales (SalesDt, SalesNo, ProductID) values (‘2018/05/01’, 202, 1);
insert into tblSales (SalesDt, SalesNo, ProductID) values (‘2018/05/01’, 203, 2);
insert into tblSales (SalesDt, SalesNo, ProductID) values (‘2018/05/01’, 204, 2);
insert into tblSales (SalesDt, SalesNo, ProductID) values (‘2018/05/02’, 205, 1);
insert into tblSales (SalesDt, SalesNo, ProductID) values (‘2018/05/02’, 206, 1);
insert into tblSales (SalesDt, SalesNo, ProductID) values (‘2018/05/03’, 207, 1);
insert into tblSales (SalesDt, SalesNo, ProductID) values (‘2018/05/03’, 208, 1);
insert into tblSales (SalesDt, SalesNo, ProductID) values (‘2018/05/03’, 209, 1);
insert into tblSales (SalesDt, SalesNo, ProductID) values (‘2018/05/03’, 210, 1);
—————————————-
SELECT prd.ProductID, prd.ProductName, pur.PurchaseDt, pur.PurchaseNo
FROM tblProduct AS prd Left Join tblPurchase AS pur on (prd.ProductID=pur.ProductID)
WHERE pur.PurchaseNo IS NOT NULL
SELECT prd.ProductID, prd.ProductName, sal.SalesDt, sal.SalesNo
FROM tblProduct AS prd Left Join tblSales AS sal on (prd.ProductID=sal.ProductID)
WHERE sal.SalesNo IS NOT NULL
but i want result like this
output is:
ProductID ProductName PurchaseDt PurchaseNo SalesDt SalesNo
————————————————————————————————————
1 P1 01/04/2018 101 01/05/2018 201
1 P1 05/04/2018 102 01/05/2018 202
1 P1 06/04/2018 103 02/05/2018 205
1 P1 10/04/2018 104 02/05/2018 206
1 P1 15/04/2018 105 03/05/2018 207
1 P1 NULL NULL 03/05/2018 208
1 P1 NULL NULL 03/05/2018 209
1 P1 NULL NULL 03/05/2018 210
2 P2 16/04/2018 106 01/05/2018 203
2 P2 17/04/2018 107 01/05/2018 204
2 P2 21/04/2018 108 NULL NULL
2 P2 22/04/2018 109 NULL NULL
please help me
sir
Hello, can i change the compatibility of my SQL server from lower to higher. I mean i have sql server 2012, will i still be able to change its compatibility to 2016?
It’s a nice question which I actually that of asking…
Hi, I’ve come here with the same problem. However, I’ve already set my compatibility level to 130, and also tried 140 as I’m on SQL 2017. However, I’m still getting the same error. have restarted SQL Server and also SSMS, still the same.
In answer to my earlier comment, turns out I typed ‘split_string’ instead of ‘string_split’, so, if you’re a bit slow, like me, check you’ve typed it right :)
solved!
thanks for saving my time
thank you :)
thank you
I am still getting error .. after setting compatibility level to 130
>>
Msg 208, Level 16, State 1, Line 60
Invalid object name ‘dbo.STRING_SPLIT’.
Completion time: 2021-03-18T20:54:32.1858903-05:00
—————————————————- this shows ok
SELECT compatibility_level
FROM sys.databases WHERE name = ‘Novasoft_jpcollegeonline’;
GO
>> 130
I tried the solution suggested:
ALTER DATABASE [database_Name]
SET COMPATIBILITY_LEVEL = 130 — For SQL Server 2016
GO
It returned:
Msg 15048, Level 16, State 1, Line 1
Valid values of the database compatibility level are 100, 110, or 120.
Hi,
I have scenario using STRING_SPLIT function from OPENQUERY and in this case it showing above discussed error invalid object, but when I checked running this function directly in database its working. Any idea? .Thanks!
Working –
DECLARE @VarString NVARCHAR(400) = ‘004000,004100’
SELECT value FROM STRING_SPLIT(@VarString, ‘,’)
Not working –
SELECT * FROM OPENQUERY([ServerName], ‘SELECT TOP 1 * FROM DatabaseName.TableName AS T WITH (NOLOCK) WHERE T.[Number] IN (SELECT TRIM(Value) FROM STRING_SPLIT(”1100,2000,3000”, ”,”))’)
SQL Server version:
Microsoft SQL Server 2017 (RTM-CU22-GDR) (KB4583457) – 14.0.3370.1 (X64)
Best Regards,
Abhijeet
THANKS for sharing this, and your knowledge in general.
Thanks for help me.
Regards
need to use string split without changing compatibility level
solved this issue using xml
DECLARE @xml as xml,@str as varchar(100),@delimiter as varchar(10)
SET @str=’A,B,C,D,E’
SET @delimiter =’,’
SET @xml = cast((”+replace(@str,@delimiter ,”)+”) as xml)
SELECT N.value(‘.’, ‘varchar(10)’) as value FROM @xml.nodes(‘X’) as T(N)