SQL SERVER – Fix Error: Invalid object name STRING_SPLIT

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.

SQL SERVER - Fix Error: Invalid object name STRING_SPLIT split_string-c-800x453

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)

Database Compatible Level, SQL Error Messages, SQL Function, SQL Scripts, SQL Server, SQL String
Previous Post
SQL SERVER – Installation Error – Unable to Open Windows Installer File – 2147286960
Next Post
SQL SERVER – Error: Could not Load File or Assembly Microsoft. SqlServer. management. sdk. sfc Version 12.0.0.0

Related Posts

16 Comments. Leave new

  • Basant Maharana
    May 4, 2018 1:45 pm

    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

    Reply
  • 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?

    Reply
  • 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.

    Reply
  • 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 :)

    Reply
  • solved!
    thanks for saving my time

    Reply
  • Hrishabh Gupta
    April 3, 2020 9:58 pm

    thank you :)

    Reply
  • thank you

    Reply
  • 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

    Reply
  • 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.

    Reply
  • 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

    Reply
  • Keyser Söze
    June 22, 2022 3:11 pm

    THANKS for sharing this, and your knowledge in general.

    Reply
  • Thanks for help me.
    Regards

    Reply
  • need to use string split without changing compatibility level

    Reply
    • 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)

      Reply

Leave a Reply