STRING_SPLIT issue thru a pass-through SSRS 2019 problem

Question

STRING_SPLIT problem on a 2019 SSRS server going after SQL 2019.

We get the standard message:

Query execution failed for dataset xyz ... rsExecutingCommand
Invalid Object Name ‘STRING_SPLIT’,

The query xyz works perfectly fine directly against SQL 2019.from SSMS.
But when the query hits the SSRS 2019 (with default compatibility level), it keeps failing.
A bug?

Question #1: Is there anything in SSRS that does not like and welcome STRING_SPLIT?

Question #2: I think we used to use FOR XML + something something for this type of functionality. Would anybody remember what the equivalent STRING_SPLIT function prior to SQL 2016. It was something like “FOR ....xml”

Answer 1

Hi @travisGatesMcGee@hotmail.com ,

Question #1: Is there anything in SSRS that does not like and welcome STRING_SPLIT?

You can take a look at this case, In SSRS if your parameter is multi value. It is already considered as an array.

Question #2: the equivalent STRING_SPLIT function prior to SQL 2016

Here is a relevant case

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

made up the 160 .... sorry, dealing with 2005-2019 hundreds of machines. If I can run the big query with STRING_SPLIT directly at the server\database; but the same query does not work when it is in the SSRS going against the same SQL machine .... there is something wrong. Of course, checked the compatibility level; it was fine (untouched).

Anyway, too late ... told the users, it is due to an unknown issue ... they are fixing the query. Gave up.

Answer 3

If I can run the big query with STRING_SPLIT directly at the server\database; but the same query does not work when it is in the SSRS going against the same SQL machine .... there is something wrong.

Yes, something is wrong. And I am quite confident that what is wrong is the assumption that it is the same server\instance\database.

Answer 4

Please also check:

    --Create test data
    CREATE TABLE SourcetableB(ID INT,String VARCHAR(MAX))
    INSERT INTO SourcetableB VALUES(1,'abc,def,pqr,xyz'),(2,'pqr,xyz,ghi,abc')
    --1.Create user-defined functions
    CREATE FUNCTION SplitStr(@Sourcestr VARCHAR(8000), @Seprate VARCHAR(100))     
    RETURNS @result TABLE(F1 VARCHAR(100))     
      AS       
       BEGIN     
       DECLARE @sql AS VARCHAR(100)     
      SET @Sourcestr=@Sourcestr+@Seprate       
      WHILE(@Sourcestr<>'')     
      BEGIN     
        SET @sql=left(@Sourcestr,CHARINDEX(',',@Sourcestr,1)-1)     
        INSERT @result VALUES(@sql)     
         SET @Sourcestr=STUFF(@Sourcestr,1,CHARINDEX(',',@Sourcestr,1),'')     
       END     
       RETURN  
       END
     GO

    SELECT * 
    FROM SourcetableB s 
    CROSS APPLY SplitStr(S.string,',') V; 

    --2.Use for xml path
    --Example 1
    DECLARE @str VARCHAR(1000) 
    DECLARE @x xml 
    SET @str='aaa,bbb,ccc' 

    SELECT id  
    FROM (SELECT [value] = CONVERT(XML , '<v>' + REPLACE(@str , ',' , '</v><v>')+ '</v>') 
    ) A 
    OUTER APPLY ( SELECT id = N.v.value('.' , 'varchar(100)') 
    FROM A.[value].nodes('/v') N (v)) B;

    --Example 2
    DECLARE @str varchar(1000)
    DECLARE @idoc int;
    DECLARE @doc xml;

    set @str='aaa,bbb,ccc'
    set @doc=cast('<Root>
    <item><ID>'+replace(@str,',','
    </ID></item><item><ID>')+'</ID></item></Root>' as xml) 
    EXEC sp_xml_preparedocument @Idoc OUTPUT, @doc

    SELECT *   FROM OPENXML (@Idoc, '/Root/item',2)
    WITH (
    [ID] varchar(10)
    ); 

    --Example 3
    -- DDL and sample data population, end
     DECLARE @tbl TABLE (Sl INT, Locations VARCHAR(100), Ratings VARCHAR(100));
     INSERT INTO @tbl VALUES
     (132 ,'ABC/DEF/GHE', 'L/M/H'),
     (332, 'ABC/GHE', 'M/H');

     DECLARE @separator CHAR(1) = '/';

     ;WITH rs AS
     (
         SELECT Sl, Locations
      , CAST('<root><r>' + 
      REPLACE(CAST(Locations AS NVARCHAR(MAX)), @separator, '</r><r>') + '</r></root>' AS XML) AS loc_xml
      , CAST('<root><r>' + 
      REPLACE(CAST(Ratings AS NVARCHAR(MAX)), @separator, '</r><r>') + '</r></root>' AS XML) AS rat_xml
         FROM @tbl
     ), t1 AS 
     (
      SELECT Sl, Locations
         , c.value('(./text())[1]','VARCHAR(100)') AS [Location]
         , ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS seq
      FROM rs
         CROSS APPLY loc_xml.nodes('/root/r') t(c)
     )
     , t2 AS
     (
      SELECT Sl
         , c.value('(./text())[1]','VARCHAR(100)') AS [Rating]
         , ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS seq
      FROM rs
         CROSS APPLY rat_xml.nodes('/root/r') t(c)
     )
     SELECT t1.Sl, t1.Locations
      , t1.[Location]
      , t2.[Rating]
     FROM t1 INNER JOIN t2 ON t1.Sl = t2.Sl
      AND t1.seq = t2.seq;

Regards,
Echo

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Answer 5

FOR XML was instead of string_agg.

There is nothing special with SSRS. I would take it that you are not connected to the database or the instance you think you are. Keep in mind that for string_split to be available, the database must be in compatibility_level 130 or higher.