SQL Server System Dynamic Management Views

System Dynamic Management Views

https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/system-dynamic-management-views?view=sql-server-ver15?WT.mc_id=DP-MVP-36769

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.

https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-index-physical-stats-transact-sql?view=sql-server-ver15?WT.mc_id=DP-MVP-36769

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

https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-os-wait-stats-transact-sql?view=sql-server-ver15?WT.mc_id=DP-MVP-36769

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

https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-os-waiting-tasks-transact-sql?view=sql-server-ver15?WT.mc_id=DP-MVP-36769

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

https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-io-virtual-file-stats-transact-sql?view=sql-server-ver15?WT.mc_id=DP-MVP-36769

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

https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-connections-transact-sql?view=sql-server-ver15?WT.mc_id=DP-MVP-36769

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

https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-os-performance-counters-transact-sql?view=sql-server-ver15?WT.mc_id=DP-MVP-36769

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

https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-requests-transact-sql?view=sql-server-ver15?WT.mc_id=DP-MVP-36769

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.

https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-sessions-transact-sql?view=sql-server-ver15?WT.mc_id=DP-MVP-36769

Example,

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

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.

https://youtu.be/TFquTnWrirs

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

Azure SQL Database

You can get started with SQL Azure here.

Azure SQL Database documentation

https://docs.microsoft.com/en-us/azure/sql-database/?WT.mc_id=DP-MVP-36769

What is Azure SQL Data Warehouse?

https://docs.microsoft.com/en-us/azure/sql-data-warehouse/sql-data-warehouse-overview-what-is/?WT.mc_id=DP-MVP-36769

Cheat sheet for Azure SQL Data Warehouse

https://docs.microsoft.com/en-us/azure/sql-data-warehouse/cheat-sheet/?WT.mc_id=DP-MVP-36769

C# getting started

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

.NET Documentation

https://docs.microsoft.com/en-us/dotnet/?WT.mc_id=DP-MVP-36769

C# Guide

https://docs.microsoft.com/en-us/dotnet/csharp/?WT.mc_id=DP-MVP-36769

Get started with C#

https://docs.microsoft.com/en-us/dotnet/csharp/getting-started/?WT.mc_id=DP-MVP-36769

A Tour of the C# Language

https://docs.microsoft.com/en-us/dotnet/csharp/tour-of-csharp/?WT.mc_id=DP-MVP-36769

C# Reference

https://docs.microsoft.com/en-us/dotnet/csharp/language-reference/?WT.mc_id=DP-MVP-36769

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; }

                    else

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

                }

                sw.WriteLine(strCell);

                Console.WriteLine(strCell);

                w.Close();

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

                app.Quit();

                book = null;

                app = null;

            }

            protected virtual void Dispose(bool disposing)

            {

                if (disposed)

                    return;

                if (disposing)

                { handle.Dispose();}

                disposed = true;

            }

            public void Dispose()

            {

                this.Dispose(true);

                GC.SuppressFinalize(this);

            }

        }

    }

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

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).

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

https://docs.microsoft.com/en-us/sql/integration-services/sql-server-integration-services?view=sql-server-2017/?WT.mc_id=DP-MVP-36769

Install SSDT

https://docs.microsoft.com/en-us/sql/integration-services/install-windows/install-integration-services?view=sql-server-2017/?WT.mc_id=DP-MVP-36769

Integration Services (SSIS) Tutorials

https://docs.microsoft.com/en-us/sql/integration-services/lesson-1-create-a-project-and-basic-package-with-ssis?view=sql-server-2017/?WT.mc_id=DP-MVP-36769