A union of curiosity and data science

Knowledgebase and brain dump of a database engineer


sp_blocked - procedure for viewing locks and blocks

create procedure sp_blocked
as
begin
 
       SELECT *
       FROM sys.dm_exec_requests
       WHERE blocking_session_id <> 0;
 
       SELECT session_id, wait_duration_ms, wait_type, blocking_session_id
       FROM sys.dm_os_waiting_tasks
       WHERE blocking_session_id <> 0
 
       SELECT resource_type, request_session_id, resource_database_id, resource_associated_entity_id
       , resource_subtype, resource_description, request_status, request_owner_type, request_mode
       FROM sys.dm_tran_locks
       WHERE resource_type IN ('PAGE', 'KEY', 'EXTENT', 'RID');
 
       SELECT tl.request_session_id, wt.blocking_session_id, DB_NAME(tl.resource_database_id) AS DatabaseName
       , tl.resource_type, tl.request_mode, tl.resource_associated_entity_id
       FROM sys.dm_tran_locks as tl
       INNER JOIN sys.dm_os_waiting_tasks as wt
       ON tl.lock_owner_address = wt.resource_address;
 
end

 

Create SQL Server CLR Function (Date Time Conversion) UTC to PDST

 

Step 1. Enable the CLR integration with SQL 
sp_configure 'show advanced options', 1
GO

RECONFIGURE

GO

sp_configure
'clr enabled', 1
GO

RECONFIGURE
Step 2. Make sure SQL Server Data Tools is Installed.
   a. Create a SQL Server Project
   b. add new item to the project. User Defined Function  
 
 
 
 // Create Your Code. This snippet converts utc to something else....
 
 

using System;

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

 public partial class UserDefinedFunctions{

    [Microsoft.SqlServer.Server.SqlFunction]

    public static SqlString convert_timezone(DateTime datetime, String source_time_zone = "UTC", String destination_time_zone = "Pacific Standard Time")

   {        //set defaults

  return new SqlString(TimeZoneInfo.ConvertTime(datetime, TimeZoneInfo.FindSystemTimeZoneById(source_time_zone), TimeZoneInfo.FindSystemTimeZoneById(destination_time_zone)).ToString());
   }

}

 

 

 
 
 
 
 Build the Code.
 
 Connect to your DB:
 
 
 Generate the Script:
 
 
 
Run the script on your Database Server or use the Publish functionality of Visual Studio
 
 
test your sql function in SSMS:
 Select dbo .convert_timezone('2013-11-21 08:00:00.000','GMT Standard Time','Pacific Standard Time')
 
 
Errors and Debugging..... 
 
You may come across the following issue due to CLR permissions... 
 

Msg 6522, Level 16, State 2, Line 3

A .NET Framework error occurred during execution of user-defined routine or aggregate "convert_timezone":

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

 
 
The first thing you want to do is set the DB Trust.... Run the following SQL: 
 
ALTER DATABASE <YOUR DB> SET TRUSTWORTHY ON
 
 
 Set the project to "Unsafe".
 
View the Project Propeties... The following window should appear. 
Set the Permission level to "UNSAFE". 
 
 
 
 
Redeploy,   Test your function again.
 
 
 
The beauty of this....... When converting from GMT , you can take daylight savings time into account without knowing when the "1 hour" switch took place. For instance. The following examples are all Midnight GMT for 4 consecutive days  with PDST (Pacific Daylight Savigs Time) taken into consideration on the 3rd.
 
 
 
-Don
 
 

C# Loop through sql server database recordset with SqlClient

Code : 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;

namespace MyNamespace
{
    class Program
    {
            static void Main(string[] args) {

            string connetionString = "Data Source=<servername>;Initial Catalog=<database>;User ID=<user_id>;Password=<password>";
            SqlConnection connection = new SqlConnection(connetionString);
            connection.Open();

            string sql = "select top 10 * from information_schema.tables";

            SqlCommand command = new SqlCommand(sql, connection);
            command.CommandTimeout = 600;
            SqlDataReader dataReader = command.ExecuteReader();

            while (dataReader.Read())
            {
              Console.WriteLine(dataReader["table_name"].ToString());
            }
            dataReader.Close();
            command.Dispose();
            connection.Close();
            
            Console.ReadKey();
        }