SQL Server System Dynamic Management Views

System Dynamic Management Views


Returns size and fragmentation information for the data and indexes of the specified table or view in SQL Server, sys.dm_db_index_physical_stats.


Returns information about all the waits encountered by threads that executed, sys.dm_os_wait_stats.


Returns information about the wait queue of tasks that are waiting on some resource., sys.dm_os_waiting_tasks


Returns I/O statistics for data and log files. This dynamic management view replaces the fn_virtualfilestats function, sys.dm_io_virtual_file_stats.


Returns information about the connections established to this instance of SQL Server and the details of each connection, sys.dm_exec_connections


Returns a row per performance counter maintained by the server. For information about each performance counter, sys.dm_os_performance_counters.


Returns information about each request that is executing in SQL Server. For more information about requests, sys.dm_exec_requests.


Returns one row per authenticated session on SQL Server. sys.dm_exec_sessions is a server-scope view that shows information about all active user connections and internal tasks, sys.dm_exec_sessions.



SELECT T.[text], P.[query_plan], S.[program_name], S.[host_name]S.[client_interface_name], S.[login_name], R.* FROM sys.dm_exec_requestsINNER
JOIN sys.dm_exec_sessions
ON S.session_id = R.session_id CROSS APPLY
sys.dm_exec_sql_text(sql_handle) AS
CROSS APPLY sys.dm_exec_query_plan(plan_handleAs P

Posted in .Net, Cloud, Community, Computers and Internet, Data Platform | Tagged | Leave a comment

SharePoint get full file path

This post shows you how to get the full file path. In order to do that you need to add Document Location in Quick Access area on top of your Office App. Follow the video below to add the Document Location.


Reference: https://www.techrepublic.com/article/how-to-display-an-office-files-full-location-in-the-quick-access-toolbar/

Posted in .Net, Cloud, Community, Computers and Internet, Microsoft Office, Office 365 | Tagged | Leave a comment

Azure SQL Database

You can get started with SQL Azure here.

Azure SQL Database documentation


What is Azure SQL Data Warehouse?


Cheat sheet for Azure SQL Data Warehouse


Posted in .Net, Cloud, Community, Computers and Internet, Data Platform | Tagged | Leave a comment

C# getting started

To ask wherever ask me “how do I start with C#”

.NET Documentation


C# Guide


Get started with C#


A Tour of the C# Language


C# Reference


Posted in .Net, Cloud, Community, Computers and Internet | Tagged | Leave a comment

using Microsoft.Office.Interop.Excel object cannot be deleted

This post shows you how to dispose and get rid the object when you exit the application. Previously in https://chanmingman.wordpress.com/2017/11/06/convert-excel-sheet-to-csv-file/. The application exited but the Excel object still exist. You have to implement IDisposable to get rid of the unmanaged object, Excel.

using System;

using Microsoft.Office.Interop.Excel;

using System.Collections.Generic;

using System.IO;

using System.Reflection;

using System.Runtime.InteropServices;

using Microsoft.Win32.SafeHandles;

namespace ConAppExcelDelimiter


    class ReadExcelFile : IDisposable


        bool disposed = false;

        SafeHandle handle = new SafeFileHandle(IntPtr.Zero, true);

        static Microsoft.Office.Interop.Excel.Application app = new Application(); //= new Excel.Application();

        static Microsoft.Office.Interop.Excel.Workbook book;

        public static void ReadExcel(String strFileName, int RowCount, int ColCount, string strDelimiter, int intSheet)


            StreamWriter sw = new StreamWriter(@”c:\temp\QC\input\Test” + intSheet + “.csv”);

            String strCell = “”;

            int col;

            Object Opt = Missing.Value;

            Microsoft.Office.Interop.Excel.Worksheet xlsSheet;

            Microsoft.Office.Interop.Excel.Range oRng;

            book = app.Workbooks.Open(strFileName, Opt, Opt, Opt, Opt, Opt, Opt, Opt, Opt, Opt, Opt, Opt, Opt, Opt, Opt);

            xlsSheet = (Microsoft.Office.Interop.Excel.Worksheet)book.Sheets[intSheet];

            oRng = (Microsoft.Office.Interop.Excel.Range)xlsSheet.Cells[1, 1];

            for (int row = 0; row < RowCount; row++)


                strCell = “”;

                for (col = 0; col < ColCount; col++)


                    oRng = (Microsoft.Office.Interop.Excel.Range)xlsSheet.Cells[row + 1, col + 1];

                    if (col < ColCount – 1)

                    { strCell = strCell + oRng.Text.ToString() + strDelimiter; }


                    { strCell = strCell + oRng.Text.ToString();}





                book.Close(false, false, Missing.Value);


                book = null;

                app = null;


            protected virtual void Dispose(bool disposing)


                if (disposed)


                if (disposing)

                { handle.Dispose();}

                disposed = true;


            public void Dispose()







Reference https://docs.microsoft.com/en-us/dotnet/standard/garbage-collection/implementing-dispose/?WT.mc_id=DP-MVP-36769

Posted in .Net, Cloud, Community, Computers and Internet, Data Platform | Tagged | Leave a comment

SSIS Remove Time from Datatime

This blog shows you how to remove time from SQL Server source with Datetime column. The pre-requisite is you need to have basic knowledge on SSIS. You can see from the picture below. I have a column from SQL Server data source called t_put. In order to remove the time only left with the date I do (DT_DBDATE)t_put).

Somehow this will give you error in t_put has a null value. In order to safeguard for the null value you can using this statement, !ISNULL(t_put) ? (DT_DBDATE)t_put : NULL(DT_DBDATE).

Posted in .Net, Cloud, Community, Computers and Internet, Data Platform | Tagged | Leave a comment

SSIS Getting Started Resources

SSIS is still once of the best integration tool in the market, https://www.gartner.com/reviews/market/data-integration-tools. Below has some resources you can utilize on.

Getting started


Install SSDT


Integration Services (SSIS) Tutorials


Posted in .Net, Cloud, Community, Computers and Internet, Data Platform | Tagged | Leave a comment