Get all Employees who joined on last day of any month.

Table

Table script -

CREATE TABLE [dbo].[tblEmployee](

[Id] [int] IDENTITY(1,1) NOT NULL,

[Name] [varchar](50) NULL,

[Email] [varchar](50) NULL,

[Password] [varchar](50) NULL,

[Mobile] [varchar](15) NULL,

[Salary] [numeric](18, 2) NULL,

[DOJ] [date] NOT NULL CONSTRAINT [DF_tblEmployee_DOJ]  DEFAULT (getdate()),

 CONSTRAINT [PK_tblEmployee] PRIMARY KEY CLUSTERED 

(

[Id] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

SET IDENTITY_INSERT [dbo].[tblEmployee] ON 

GO

INSERT [dbo].[tblEmployee] ([Id], [Name], [Email], [Password], [Mobile], [Salary], [DOJ]) VALUES (1, N'Sunil', N'sk@gmail.com', N'12345678', N'989898989', CAST(50000.00 AS Numeric(18, 2)), CAST(N'2022-01-31' AS Date))

GO

INSERT [dbo].[tblEmployee] ([Id], [Name], [Email], [Password], [Mobile], [Salary], [DOJ]) VALUES (2, N'Anil', N'anil@gmail.com', N'4534534', N'987977867', CAST(40000.00 AS Numeric(18, 2)), CAST(N'2022-01-28' AS Date))

GO

INSERT [dbo].[tblEmployee] ([Id], [Name], [Email], [Password], [Mobile], [Salary], [DOJ]) VALUES (3, N'Anil', N'ak@gmail.com', N'565ef44', N'986858576', CAST(85000.00 AS Numeric(18, 2)), CAST(N'2022-02-25' AS Date))

GO

INSERT [dbo].[tblEmployee] ([Id], [Name], [Email], [Password], [Mobile], [Salary], [DOJ]) VALUES (4, N'Sunil', N'sunil@gmail.com', N'5345', N'8995675656', CAST(55000.00 AS Numeric(18, 2)), CAST(N'2022-02-28' AS Date))

GO

INSERT [dbo].[tblEmployee] ([Id], [Name], [Email], [Password], [Mobile], [Salary], [DOJ]) VALUES (5, N'Anurag', N'ag@gmail.com', N'76864554', N'789987987', CAST(60000.00 AS Numeric(18, 2)), CAST(N'2022-03-31' AS Date))

GO

INSERT [dbo].[tblEmployee] ([Id], [Name], [Email], [Password], [Mobile], [Salary], [DOJ]) VALUES (6, N'Sony', N'sony@gmail.com', N'576576', N'979787777', CAST(45000.00 AS Numeric(18, 2)), CAST(N'2022-04-10' AS Date))

GO

INSERT [dbo].[tblEmployee] ([Id], [Name], [Email], [Password], [Mobile], [Salary], [DOJ]) VALUES (7, N'Ram', N'ram@gmail.com', N'5345344', N'897897788', CAST(55000.00 AS Numeric(18, 2)), CAST(N'2022-04-30' AS Date))

GO

INSERT [dbo].[tblEmployee] ([Id], [Name], [Email], [Password], [Mobile], [Salary], [DOJ]) VALUES (8, N'Ram', N'ram@hgh.dks', N'546446', N'797899877', CAST(35000.00 AS Numeric(18, 2)), CAST(N'2022-05-29' AS Date))

GO

INSERT [dbo].[tblEmployee] ([Id], [Name], [Email], [Password], [Mobile], [Salary], [DOJ]) VALUES (9, N'Anil', N'saf@sdfsdf', N'4353535', N'798987989', CAST(25000.00 AS Numeric(18, 2)), CAST(N'2022-06-30' AS Date))

GO

SET IDENTITY_INSERT [dbo].[tblEmployee] OFF

GO

SELECT *

FROM tblEmployee

WHERE MONTH(DateAdd(Day,1,DOJ))<>MONTH(DOJ)

 Output of above query is- 

How to Find Blocking Queries in SQL Server

• -- Create table TableA
• CREATE TABLE TableA
• (
• ID INT,
• Name NVARCHAR(50)
• )
• Go
• -- Insert some test data
• INSERT INTO TableA values (101, 'Anurag')
• INSERT INTO TableA values (102, 'Mohanty')
• Go

BEGIN TRANSACTION

UPDATE TableA SET Name = ‘Anurag Updated’ WHERE Id = 101

Now from a different window, try to execute any of the following commands and you will notice that all the queries are blocked. 

SELECT Count(*) FROM TableA

DELETE FROM TableA WHERE Id = 101

TRUNCATE TABLE TableA

DROP TABLE TableA

The reason is there is an open transaction. So, once the open transaction is completed, then only you will be able to execute the above queries.

How to identify all the active transactions which are not completed?  

One way to do identify this is by using the DBCC OpenTran command. The DBCC OpenTran statement will display only the oldest active transaction. It is not going to show you all the open transactions.
DBCC OpenTran 

The following SQL script you can use to identify all the active transactions.

• SELECT
• [s_tst].[session_id],
• [s_es].[login_name] AS [Login Name],
• DB_NAME (s_tdt.database_id) AS [Database],
• [s_tdt].[database_transaction_begin_time] AS [Begin Time],
• [s_tdt].[database_transaction_log_bytes_used] AS [Log Bytes],
• [s_tdt].[database_transaction_log_bytes_reserved] AS [Log Rsvd],
• [s_est].text AS [Last T-SQL Text],
• [s_eqp].[query_plan] AS [Last Plan]
• FROM
• sys.dm_tran_database_transactions [s_tdt]
• JOIN
• sys.dm_tran_session_transactions [s_tst]
• ON
• [s_tst].[transaction_id] = [s_tdt].[transaction_id]
• JOIN
• sys.[dm_exec_sessions] [s_es]
• ON
• [s_es].[session_id] = [s_tst].[session_id]
• JOIN
• sys.dm_exec_connections [s_ec]
• ON
• [s_ec].[session_id] = [s_tst].[session_id]
• LEFT OUTER JOIN
• sys.dm_exec_requests [s_er]
• ON
• [s_er].[session_id] = [s_tst].[session_id]
• CROSS APPLY
• sys.dm_exec_sql_text ([s_ec].[most_recent_sql_handle]) AS [s_est]
• OUTER APPLY
• sys.dm_exec_query_plan ([s_er].[plan_handle]) AS [s_eqp]
• ORDER BY
• [Begin Time] DESC;
• GO

When you execute the above SQL Script, it will give you the following output.

The above SQL Script gives you a lot of useful information about open transactions such as

1. Session Id
2. Login Name
3. Database Name
4. Transaction Begin Time
5. The actual query that is executed 

So once you got the above information, then you can ask the respective developer to either commit or rollback the transactions that have left open unintentionally so that you can proceed further.

That’s cool. But if for some reason the person who initiated the transaction is not available, then also you have the option to KILL the associated process. However, this may have unintended consequences, so use it with extreme caution. 

There are 2 ways available in SQL Server to kill the process are as follows.

Killing the process using SQL Server Activity Monitor:

1. Right Click on the Server Name in Object Explorer and then select the “Activity Monitor” option.
2. In the “Activity Monitor” window expand the Processes section
3. Finally, right click on the associated “Session ID” and select “Kill Process” from the context menu as shown in the below image.

Killing the process using SQL command: 

KILL Process_ID 

For example:

KILL 42

What happens when you kill a session?

All the work that has been done by the transaction will be rolled back. The database must be put back in the state it was before the transaction was started. 

How to create XML from DataTable in C#?

public void CreateFromDataTableXML()

        {

            string FileName = "";

            DataTable mDataTable = new DataTable();

            mDataTable = null;//Assign Data From DataBase

            if (mDataTable != null && mDataTable.Rows.Count > 0)

            {

                mDataTable.TableName = "EmployeeDetails";

                string Path = @"\\ServerPath\EmployeeXML\";

                DataSet mDataSet = new DataSet();

                mDataSet.Tables.Add(mDataTable);

                try

                {

                    FileName =  "Employee_" + DateTime.Now.ToString("dd-MMM-yyyy-hhsstt").ToString();

                    mDataSet.WriteXml(Path + FileName + ".xml");

                }

                catch

                {

                    FileName = String.Empty;

                }

                if (!string.IsNullOrWhiteSpace(FileName))

                {

                    //Add Your Business Logic Here

                }

            }

        }