Microsoft – SQL Server – Identify “uncontained” objects — Using SQLCLR to split strings

Background

This is a quick followup to our last post; that post is titled Microsoft – SQL Server – Identify “uncontained” objects ( https://danieladeniji.wordpress.com/2014/12/13/microsoft-sql-server-identify-uncontained-objects/ ).

In the post mentioned something about a concerning error :

Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.

 

Technical Discussions

The problem stayed on my mind and took me back to a good and informed set of technical discussions:

 

SQL CLR

The discussions mentioned that the best path to splitting strings is to use SQL CLR.  And, so I decided to try out that path.

 

Adam Machanic

Found out from Aaron Bertrand  that Adam Machanic of the WhoisActivehttp://sqlblog.com/blogs/adam_machanic/archive/2012/03/22/released-who-is-active-v11-11.aspx ) fame has a well written SQL CLR function that splits strings, as well.

Downloaded Adam’s code ( from http://sqlblog.com/blogs/adam_machanic/archive/2009/04/28/sqlclr-string-splitting-part-2-even-faster-even-more-scalable.aspx ).

And, started to use it, but quickly found out that it is missing Row Numbers.  Thankfully Surya Pratap laid out a nice trail on how to add “Row Numbers” to the returned table.

 

Edited Code

Here is Adam’s original code with Surya’ hints ( added in ).

 


using System;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public class ResultSet
{

	public readonly int row;

	public readonly string value;

	public ResultSet(int row, string value)
	{

		this.row = row;

		this.value = value;

	}	

}

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction(
       FillRowMethodName = "FillRow_Multi",
       TableDefinition = "item nvarchar(4000)"
       )
    ]
    public static IEnumerator SplitString_Multi(
      [SqlFacet(MaxSize = -1)]
      SqlChars Input,
      [SqlFacet(MaxSize = 255)]
      SqlChars Delimiter
       )
    {
        return (
            (Input.IsNull || Delimiter.IsNull) ?
            new SplitStringMulti(new char[0], new char[0]) :
            new SplitStringMulti(Input.Value, Delimiter.Value));
    }

    //public static void FillRow_Multi(object obj, out SqlString item)
	public static void FillRow_Multi(object obj, out SqlInt32 row, out SqlString item)
    {

        //item = new SqlString((string)obj);

		ResultSet rs = (ResultSet)obj;

		row = new SqlInt32(rs.row);

		item = new SqlString(rs.value);

    }

    public class SplitStringMulti : IEnumerator
    {

		private int row;

        public SplitStringMulti(char[] TheString, char[] Delimiter)
        {
            theString = TheString;
            stringLen = TheString.Length;
            delimiter = Delimiter;
            delimiterLen = (byte)(Delimiter.Length);
            isSingleCharDelim = (delimiterLen == 1);

            lastPos = 0;
            nextPos = delimiterLen * -1;

			//initalise row in "Constructor" and during "Reset" set row to 0 by adding
			row = 0;
        }

        #region IEnumerator Members

        public object Current
        {
            get
            {
                //return new string(theString, lastPos, nextPos - lastPos);

				String strToken = null;
				ResultSet objResultSet = null;
				strToken = new string (theString, lastPos, nextPos - lastPos);

				objResultSet = new ResultSet(++row, strToken );

				return (objResultSet);

            }
        }

        public bool MoveNext()
        {
            if (nextPos >= stringLen)
                return false;
            else
            {
                lastPos = nextPos + delimiterLen;

                for (int i = lastPos; i  0)
                            return true;
                        else
                        {
                            i += (delimiterLen-1);
                            lastPos += delimiterLen;
                        }
                    }
                }

                lastPos = nextPos + delimiterLen;
                nextPos = stringLen;

                if ((nextPos - lastPos) > 0)
                    return true;
                else
                    return false;
            }
        }

        public void Reset()
        {
            lastPos = 0;
            nextPos = delimiterLen * -1;

			//initialise row in "Constructor" and during "Reset" set row to 0 by adding
			row = 0;
        }

        #endregion

        private int lastPos;
        private int nextPos;

        private readonly char[] theString;
        private readonly char[] delimiter;
        private readonly int stringLen;
        private readonly byte delimiterLen;
        private readonly bool isSingleCharDelim;
    }
};

 

And, to compile it

 


   rem http://msdn.microsoft.com/en-us/library/78f4aasd.aspx

   rem C:\windows\Microsoft.NET\Framework\v4.0.30319\csc /out:CLRUtilities.dll /target:library SplitStringMulti.cs
   C:\windows\Microsoft.NET\Framework\v4.0.30319\csc /out:CLRUtilitiesIdentity.dll /target:library SplitStringMultiIdentity.cs

To use the CLR in SQL Server

 


   use master
   go

   declare @filename sysname
   set @filename = 'C:\AdamMachnic\CLRUtilitiesIdentity.dll'

   /*
     DROP FUNCTION dbo.SplitStrings_CLR
     DROP ASSEMBLY CLRUtilities
   */

   CREATE ASSEMBLY CLRUtilities FROM @filename
   WITH PERMISSION_SET = SAFE;
   GO

   CREATE FUNCTION dbo.SplitStrings_CLR
   (
      @List NVARCHAR(MAX),
     @Delimiter NVARCHAR(255)
   )
   RETURNS TABLE ( Row int, Item NVARCHAR(4000) )
   EXTERNAL NAME CLRUtilities.UserDefinedFunctions.SplitString_Multi;
   GO

   exec sp_configure 'show advanced options',1
   reconfigure with override
   go

   exec sp_configure 'clr enabled',1
   reconfigure with override
   go

 

Try Things Out

 

Here is our SQL Code for trying things out.

 


     declare @listofFruits nvarchar(600)
     declare @listSeparator nvarchar(30)

     set @listofFruits = 'Apple, Orange, Pear, Banana, Mango'
     set @listSeparator = ','

     select *
     from   [master].dbo.SplitStrings_CLR(@listofFruits, @listSeparator)

 

Output:

ListofFruits

 

Utilize Code

 

Here is the SQL Code where we utilize Adam’s code.

 

/*
    --Adam Machanic
    http://sqlblog.com/blogs/adam_machanic/archive/2009/04/28/sqlclr-string-splitting-part-2-even-faster-even-more-scalable.aspx
    [master]dbo.SplitStrings_CLR

*/
declare @newline nvarchar(30)

set @newline = char(13) + char(10)

select

          tblUCE.class
        , tblUCE.class_desc
        , tblUCE.major_id as objectID
        , case

                when (tblUCE.class = 1) then
                        object_schema_name(tblUCE.major_id)
                        + '.' + object_name(tblUCE.major_id)
                else cast(tblUCE.major_id as sysname)

           end as [entity] 

        , tblUCE.statement_line_number

        , len(tblSM.[definition]) as lengthOFDefinition

        ,  tblSM.[definition]

        , tblSQLText.[Item] as sqlLine

from   sys.dm_db_uncontained_entities tblUCE

          inner join sys.sql_modules tblSM

                on tblUCE.major_id = tblSM.object_id

          inner join sys.objects tblO

               on tblSM.object_id = tblO.object_id

         cross apply [master].dbo.SplitStrings_CLR(tblSM.[definition], @newLine)
             tblSQLText

--filter out encryped objects
where tblSM.[definition] is not null

--filter out Microsoft shipped\owned objects
and   tblO.is_ms_shipped = 0

--match line numbers
and tblUCE.statement_line_number = tblSQLText.[Row]

order by 

          case

                when (tblUCE.class = 1) then
                        object_schema_name(tblUCE.major_id)
                        + '.' + object_name(tblUCE.major_id)
                else cast(tblUCE.major_id as sysname)

          end
         ,  tblUCE.statement_line_number desc

Things worked out.  No errors this time.

 

Source Control

GitHub

Files posted to GitHub @ https://github.com/DanielAdeniji/StringSplitByAdamMachanic

 

Dedicated

Dedicated to the public square commiters — Erland Sommarskog, Steve Jones, Aaron Bertrand, Adam Machanic, and Jeff Moden.

 

Listening To

And, listening to that old song

John Michael Montgomery – Letters from Home
https://www.youtube.com/watch?v=X75sVw0xH2c

3 thoughts on “Microsoft – SQL Server – Identify “uncontained” objects — Using SQLCLR to split strings

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s