SQL CLR Error: .NET Framework is disabled. Enable "clr enabled" configuration option.

Created new SQL Server project in Visual studio, to generate DLL file having SQL function, when we tried to use published function we ended with below error message:

Execution of user code in the .NET Framework is disabled. Enable "clr enabled" configuration option.

To fix the above issue enable CLR in sql server:


sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO

After applying the code we tried to execute the created function, but ended in below error:

A .NET Framework error occurred during execution of user-defined routine or aggregate "<functionname>":
System.Security.HostProtectionException: Attempted to perform an operation that was forbidden by the CLR host.

The protected resources (only available with full trust) were: All
The demanded resources were: MayLeakOnAbort

System.Security.HostProtectionException:
   at UserDefinedFunctions.<functionname>(SqlDateTime pstTime, String Timezone)

To overcome the above issue, run below commands:

ALTER ASSEMBLY <functionname> WITH PERMISSION_SET = Safe

ALTER DATABASE <DBNAME>
SET TRUSTWORTHY ON


ALTER ASSEMBLY Timezone WITH PERMISSION_SET = UnSafe

Replace HTML Tags from SQL Text Output

We may come across some text fields in SQL table having values like '..xyz<p>asdff...', in that case we need to replace HTML tags.

To achieve that create below function and use them in SQL procedures:

CREATE FUNCTION [dbo].[RemoveHTMLTag] ( @HTMLText VARCHAR(MAX) )
RETURNS VARCHAR(MAX)
AS BEGIN
    DECLARE @Start INT
    DECLARE @End INT
    DECLARE @Length INT
    SET @Start = CHARINDEX('<', @HTMLText)
    SET @End = CHARINDEX('>', @HTMLText, CHARINDEX('<', @HTMLText))
    SET @Length = ( @End - @Start ) + 1
    WHILE @Start > 0 AND @End > 0 AND @Length > 0
        BEGIN
            SET @HTMLText = STUFF(@HTMLText, @Start, @Length, '')
            SET @Start = CHARINDEX('<', @HTMLText)
            SET @End = CHARINDEX('>', @HTMLText, CHARINDEX('<', @HTMLText))
            SET @Length = ( @End - @Start ) + 1
        END
    RETURN REPLACE(REPLACE(REPLACE(LTRIM(RTRIM(@HTMLText)), '$', '. '), '£', '. '), '€', '. ')
   END

In Procedure:

SELECT dbo.RemoveHTMLTag(ColumnName) FROM Table

Insert a Comma Separator Between ResultSet of a SQL Table Column

Consider we have a table dbo.User, and we need to get all UserIDs separated by comma the apply below script:

Example:

Select Top 5 UserID From dbo.User

gives output as below

UserID
1
2
3
4
5

DECLARE @UserID VARCHAR(MAX)

SELECT @USERID = COALESCE(@USERID+',' , '') + UserID
FROM dbo.User
SELECT @USERID

This yields output as 1,2,3,4,5,....

T-SQL Script to Rebuild Indexes in a Database

T-SQL Script to rebuild indexes in a database:

SET nocount ON;

DECLARE @objectid INT;
DECLARE @indexid INT;
DECLARE @partitioncount BIGINT;
DECLARE @schemaname NVARCHAR(130);
DECLARE @objectname NVARCHAR(130);
DECLARE @indexname NVARCHAR(130);
DECLARE @partitionnum BIGINT;
DECLARE @partitions BIGINT;
DECLARE @frag FLOAT;
DECLARE @command NVARCHAR(4000);
DECLARE @dbid SMALLINT;

-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function
-- and convert object and index IDs to names.
SET @dbid = Db_id();

SELECT [object_id]                  AS objectid,
       index_id                     AS indexid,
       partition_number             AS partitionnum,
       avg_fragmentation_in_percent AS frag,
       page_count
INTO   #work_to_do
FROM   sys.Dm_db_index_physical_stats (@dbid, NULL, NULL, NULL, N'LIMITED')
WHERE  avg_fragmentation_in_percent > 10.0 -- Allow limited fragmentation
       AND index_id > 0 -- Ignore heaps
       AND page_count > 25; -- Ignore small tables

-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR FOR
  SELECT objectid,
         indexid,
         partitionnum,
         frag
  FROM   #work_to_do;

-- Open the cursor.
OPEN partitions;

-- Loop through the partitions.
WHILE ( 1 = 1 )
  BEGIN
      FETCH next FROM partitions INTO @objectid, @indexid, @partitionnum, @frag;

      IF @@FETCH_STATUS < 0
        BREAK;

      SELECT @objectname = Quotename(o.name),
             @schemaname = Quotename(s.name)
      FROM   sys.objects AS o
             JOIN sys.schemas AS s
               ON s.schema_id = o.schema_id
      WHERE  o.object_id = @objectid;

      SELECT @indexname = Quotename(name)
      FROM   sys.indexes
      WHERE  object_id = @objectid
             AND index_id = @indexid;

      SELECT @partitioncount = Count (*)
      FROM   sys.partitions
      WHERE  object_id = @objectid
             AND index_id = @indexid;

      -- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.
      IF @frag < 30.0          SET @command = N'ALTER INDEX ' + @indexname + N' ON '                         + @schemaname + N'.' + @objectname + N' REORGANIZE';        IF @frag >= 30.0
        SET @command = N'ALTER INDEX ' + @indexname + N' ON '
                       + @schemaname + N'.' + @objectname + N' REBUILD';

      IF @partitioncount > 1
        SET @command = @command + N' PARTITION='
                       + Cast(@partitionnum AS NVARCHAR(10));

      EXEC (@command);

      PRINT N'Executed: ' + @command;
  END

-- Close and deallocate the cursor.
CLOSE partitions;

DEALLOCATE partitions;

-- Drop the temporary table.
DROP TABLE #work_to_do;

go

Using T-SQL Equal 'LIKE' Operator in SSRS

I was preparing a SSRS report to get count of users title starts with 'QA'  (example QA Lead, QA Analyst, etc) in my report, and atlast found the solution

=SUM(IIF(Fields!UserTitle.Value LIKE "QA*", 1, 0))

T-SQL to identify long running queries

Below queries helps to identify long running query and hanging queries:

USE MASTER
GO
SELECT SPID,ER.percent_complete,
/* This piece of code has been taken from article. Nice code to get time criteria's
http://beyondrelational.com/blogs/geniiius/archive/2011/11/01/backup-restore-checkdb-shrinkfile-progress.aspx
*/
    CAST(((DATEDIFF(s,start_time,GetDate()))/3600) as varchar) + ' hour(s), '
        + CAST((DATEDIFF(s,start_time,GetDate())%3600)/60 as varchar) + 'min, '
        + CAST((DATEDIFF(s,start_time,GetDate())%60) as varchar) + ' sec' as running_time,
    CAST((estimated_completion_time/3600000) as varchar) + ' hour(s), '
        + CAST((estimated_completion_time %3600000)/60000 as varchar) + 'min, '
        + CAST((estimated_completion_time %60000)/1000 as varchar) + ' sec' as est_time_to_go,
    DATEADD(second,estimated_completion_time/1000, getdate()) as est_completion_time,
/* End of Article Code */    
ER.command,ER.blocking_session_id, SP.DBID,LASTWAITTYPE,
DB_NAME(SP.DBID) AS DBNAME,
SUBSTRING(est.text, (ER.statement_start_offset/2)+1,
        ((CASE ER.statement_end_offset
         WHEN -1 THEN DATALENGTH(est.text)
         ELSE ER.statement_end_offset
         END - ER.statement_start_offset)/2) + 1) AS QueryText,
TEXT,CPU,HOSTNAME,LOGIN_TIME,LOGINAME,
SP.status,PROGRAM_NAME,NT_DOMAIN, NT_USERNAME
FROM SYSPROCESSES SP
INNER JOIN sys.dm_exec_requests ER
ON sp.spid = ER.session_id
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(er.sql_handle) EST
ORDER BY CPU DESC