Category: SQL Server

Retry SQL Code for X times within SQL Script

In some genuine cases, we may experience a failure even when there are no issues with SQL Script or SQL Codes which may be due to large no of databases, high server utilization and parallel process/execution in real time. It can be rectified easily by rerunning it.

For such scenario we need a option to retry/re-execute that particular piece of code for X times (Say 3 times) for successful completion without any manual intervention/validations.

Declare @Counterloop int =0, @IsSuccess int = 0

while (@Counterloop <=3 and @IsSuccess = 0)
Begin
	Begin Try

		--Piece of code for Retry/Re-Execution - START--
		select 1/0
		---EXEC Rexcute_Retry_SP
		--Piece of code for Retry/Re-Execution - END--

		set @IsSuccess=1

	END TRY
	BEGIN Catch
		IF (@Counterloop<=2) Begin
			set @Counterloop=@Counterloop+1
			print 'counter value is'+ cast(@Counterloop as varchar)
		END
		ELSE Throw;
	END Catch
END

Output of Successful Run with no Retries:-

Fig 1:- SQL Code executed successfully in the first attempt itself

Output of Failure Run After ‘X’ times Retry:-

Fig 2:- SQL Code retries for 3 times and exited with Error Message in Final Try

Happy Learning and Exploring SQL, Please do share this post with your friends if you like it. !!

SQL Behaviour – Assignment of value to Variable

We are going to see the SQL Behaviour while assigning value to variable, we thought of writing this post since recently we came across issues in real time during data assignment and processing.

Case 1:- First let’s see the example of direct value assignment in SQL,

declare @valueAssign nvarchar(50)='', @valueIntAssign int
set @valueAssign='YYYY'
set @valueIntAssign=1111
select @valueAssign as Fullname_DirectAssign
select @valueIntAssign as Number_DirectAssign

Output:-

Case 2:- Now we will see the Random value assignment in SQL, there are 3 rows in the table but it randomly picked the data from 3rd row during the execution.

declare @valueAssign nvarchar(50)='', @valueIntAssign int
set @valueAssign='YYYY'
set @valueIntAssign=1111
select @valueAssign as Fullname_DirectAssign
select @valueIntAssign as Number_DirectAssign

drop table if exists DataValueAssign

create table DataValueAssign(
id int,
fullname nvarchar(50),
Age int
)
insert into DataValueAssign values (1,'AAAA',19),(2,'CCCC',54),(3,'BBBB',34)
---Data Value Assigned Randomly to the Variable--
select @valueAssign=fullname,@valueIntAssign=age from DataValueAssign
select @valueAssign as Fullname_Randomly, @valueIntAssign as Age_Randomly

Output:-

Case 3:- Now let’s see how we can handle this Random value assignment behaviour by using the “Order by” and “Top” functions and assign the appropriate values to the variable and use it for data processing.

declare @valueAssign nvarchar(50)='', @valueIntAssign int
set @valueAssign='YYYY'
set @valueIntAssign=1111
select @valueAssign as Fullname_DirectAssign
select @valueIntAssign as Number_DirectAssign

drop table if exists DataValueAssign

create table DataValueAssign(
id int,
fullname nvarchar(50),
Age int
)
insert into DataValueAssign values (1,'AAAA',19),(2,'CCCC',54),(3,'BBBB',34)
---First Data Value Assigned to the Variable using order by ID--
select @valueAssign=fullname,@valueIntAssign=age from DataValueAssign order by ID desc
select @valueAssign as Fullname_FirstValue_using_Orderby,@valueIntAssign as Age_FirstValue_using_Orderby
---Top 1 Data Value Assigned to the Variable using order by AGE desc--
select top 1 @valueAssign=fullname,@valueIntAssign=age from DataValueAssign order by age desc
select @valueAssign as Fullname_Top1_Orderby_desc, @valueIntAssign as Age_Top1_Orderby_desc
---Top 1 Data Value Assigned to the Variable using order by AGE--
select top 1 @valueAssign=fullname,@valueIntAssign=age from DataValueAssign order by age
select @valueAssign as Fullname_Top1_Orderby_asc, @valueIntAssign as Age_Top1_Orderby_asc

Output:-

Be aware while assigning value to SQL variable and make sure that the expected value was set to variable, enjoy exploring SQL, Happy Learning !!!

Thanks for reading this post, Please share it to your friends if you liked it šŸ™‚

How to identify the list of folders and files which has been modified on given date using SQL Server

In some situations you may face scenarios like “you need to know all the files and folders modified recently or modified after the specific date range to check and remove unnecessary files\folders from the Drive”, for this scenario you can make use of this script.

Pre-requisites:-

  1. PowerShell
  2. Access to “xp_cmdshell”

The below is the SQL stored procedure which uses 2 parameters (“Drive\Folder path” and “Date”), Date filter is an optional parameter, if you don’t supply any values, by default it will consider the files\folders which are modified from Yesterday (please note that you can change the default date filter value based on your requirement)

--EXEC [SP_Get_Directory_FilesInfo]  'D:\temp\scripts\','2020-04-01'
--EXEC [SP_Get_Directory_FilesInfo]  'D:\temp\scripts\'
Create or Alter PROCEDURE [dbo].[SP_Get_Directory_FilesInfo] 
(@path nvarchar(4000),@yyyymmdd date=NULL) AS 
BEGIN

declare @pscript nvarchar(4000)

IF @yyyymmdd IS Null
	SET @yyyymmdd= cast(DATEADD(DD,-1,getdate()) as date)

if right(@path,1)='\'
	set @path = substring(@path,1,len(@path)-1)

--To avoid using of system drive--
IF (Charindex('C:\',@path)>0  or charindex('c$',@path)>0) BEGIN
	Select 'Alert Message!! C-Drive is not supported' Return;
END

--PS Script--
set @pscript ='powershell.exe -ExecutionPolicy Unrestricted -command "Get-ChildItem -Recurse -Path "' + @path +
'" | Where-Object LastWriteTime -ge "' + CAST(@yyyymmdd  AS VARCHAR) +
'" | select-Object FullName, @{Name="''LastWriteTime''"; Expression={$_.LastWriteTime.ToString("''yyyy-MM-dd HH:mm:ss''")}}, Mode | Export-CSV -NoTypeInformation -path "' + @path + '\Outputfile.csv"" '

Exec xp_cmdshell @pscript, NO_OUTPUT;

drop table if exists tblcsv_data
create table tblcsv_data (aa nvarchar(max),bb nvarchar(max), mode nvarchar(100))

declare @c nvarchar(4000), @d nvarchar(4000)
set @c = @path + '\Outputfile.csv'

EXEC('
BULK INSERT tblcsv_data 
FROM ''' + @c +'''
WITH
(
    FIRSTROW = 2,
    FIELDTERMINATOR = ''","'',  --CSV field delimiter
    ROWTERMINATOR = ''\n'',   --Use to shift the control to next row
    TABLOCK
);
')

set @d = 'del '+ @c
Exec xp_cmdshell @d, NO_OUTPUT;

delete from tblcsv_data where aa like '%Outputfile.csv%'

drop table if exists tbl_FilesInfo
create table tbl_FilesInfo (id bigint identity(1,1), 
FileNames nvarchar(max), 
LastModified Datetime,
isDirectory bit null
)

insert into tbl_FilesInfo (FileNames,LastModified,isDirectory)
select replace(aa,'"',''),
replace(bb,'"',''),
case when mode='d-----"' then 1 else 0 end isDirectory
from tblcsv_data

if not exists (select 1 from tbl_FilesInfo) Begin
	Select 'Please Check!! There are no files having Date Range greater than [' + CAST(@yyyymmdd  AS VARCHAR) + ']' as Status
	return;
	END

select  FileNames,
LastModified,
isDirectory
from tbl_FilesInfo
order by isDirectory desc,
LastModified desc

END

Sample Input Folder:-

Execution of SP with 2 Parameters:-

Execution of SP without Date Parameter:-

In addition to the above, here is the some example of PS scripts which you can directly use it in PowerShell and get the same desired results.

#PS Script 1 output will be displayed in powershell window
Get-ChildItem -Recurse -Path "C:\temp\script" | Where-Object LastWriteTime -ge "2021-04-02" | select-Object FullName, @{Name="''LastWriteTime''"; Expression={$_.LastWriteTime.ToString("''yyyy-MM-dd HH:mm:ss''")}}, @{Name = "Isdirectory"; Expression = {$_.Mode -replace "d-----","1" -replace "-a----","0"}}

#PS Script 2 output will be saved in .csv file
#If you want to save results to text file, please change export file name as Outputfile.txt
Get-ChildItem -Recurse -Path "C:\temp\script" | Where-Object LastWriteTime -ge "2021-04-02" | select-Object FullName, @{Name="''LastWriteTime''"; Expression={$_.LastWriteTime.ToString("''yyyy-MM-dd HH:mm:ss''")}}, @{Name = "Isdirectory"; Expression = {$_.Mode -replace "d-----","1" -replace "-a----","0"}} | Export-CSV "C:\temp\script\Outputfile.csv"

If you enjoyed this blog post, please feel free to share it with your friends!

Update() Trigger Function in Table

Update() function returns “True” in any case the Insert/update attempt is successful on the supplied column, the significance of this function is to trigger the action/code only if the supplied column is get updated or inserted successfully.

In the below example, I’ve used the Update() function for identifying any changes in “CodeValue” column of table “Configtbl” and ignored the column changes if its value is same.

“Configtbl_Audit” table will get loaded based on the changes in “CodeValue” column in “Configtbl”

drop table if exists Configtbl
drop table if exists Configtbl_Audit

create table Configtbl(
	 Code nvarchar(10) not null,
	 CodeValue decimal(10,2) not null
)
insert into Configtbl(Code,CodeValue) values ('VA1111',25.99),('VA1118',19.99)

create table Configtbl_Audit(
	 Code nvarchar(10) not null,
	 Old_Value decimal(10,2) not null,
	 New_Value decimal(10,2) not null,
	 DateModified datetime not null
)
 
IF EXISTS (SELECT name FROM sys.objects WHERE name = 'TRG_UPD_NEW_OLD_VALUE' AND type = 'TR')  
   DROP TRIGGER TRG_UPD_NEW_OLD_VALUE 
GO 

CREATE TRIGGER TRG_UPD_NEW_OLD_VALUE  
ON Configtbl
FOR UPDATE   
AS   

declare @oldvalue decimal(10,2),@newvalue decimal(10,2), @Code nvarchar(10)
IF (UPDATE (CodeValue))  
BEGIN
	select @oldvalue = CodeValue , @Code = Code from deleted

	insert into Configtbl_Audit(Code, Old_Value, New_Value, DateModified)
	select @Code, @oldvalue, CodeValue, getdate()
	from inserted 
	where code=@Code
	and @oldvalue <> CodeValue

END;  
GO 

Output:-

View of Data in Configtbl and Configtbl_Audit tables before doing any updates:

After updating “Code” column in Configtbl table:

After updating “CodeValue” column in Configtbl table:

Iā€™d like to grow my readership. If you enjoyed this blog post, please share it with your friends!

Its always important to look at the License type of your SQL Server

Its always important to look at the License type of your SQL Server!!!

Problem Statement:

We recently had an issue with CPU utilization reaching more than 95% always for database server in one of our performance test environment. Load test environments are resource intensive test, hence it is expected to have high CPU utilization. However, we could observe the number of tests processed and number of transactions are very less spiking the CPU utilization to 95%.

Let me explain a bit more on my environment, We have 4 sockets with 10 physical cores and HT enabled in our test environment. As per the configuration we have total 80 logical CPU available. SQL Server version information as below:


Microsoft SQL Server 2014 (SP2) (KB3171021) - 12.0.5000.0 (X64) 
               Jun 17 2016 19:14:09 
               Copyright (c) Microsoft Corporation
               Enterprise Edition ((missing))Core based Licensing>((missing)) (64-bit) on Windows NT 6.3  (Build 9600: )

Here we can observe that the version information is missing “Core based Licensing”, that means, the SQL version is not Core based, but CAL based. Let us look at the excerpt from the MSDN article:

“Enterprise Edition with Server + Client Access License (CAL) based licensing (not available for new agreements) is limited to a maximum of 20 cores per SQL Server instance. There are no limits under the Core-based Server Licensing model”

So, though we have 4 sockets with 10 Cores, ONLY 20 cores are VISIBLE for SQL Server. In our environment, this was 20*2(HT enabled) = 40 Logical CPUs are visible.

Ref: https://technet.microsoft.com/en-us/library/cc645993(v=sql.120).aspx

Lets confirm the above with other parameters DMV -sys.dm_os_schedulers.


Select parent_node_id,Count(cpu_id) Total_Schedulers,
 count(Case when Status = 'VISIBLE ONLINE' Then 1 Else null End) Visible_Count,
 count(Case when Status = 'VISIBLE OFFLINE' Then 1 Else null End) NotVisible_Count
From sys.dm_os_schedulers 
where status in ('VISIBLE ONLINE','VISIBLE OFFLINE') 
and parent_node_id not in (64)--DAC
Group by parent_node_id

The result looks like below:


This is clear that SQL server was not able to utilize more than 40 logical CPU in the above environment.

Once we upgraded the license to Core Based License, we were able to use all available CPUs in our environment and observed the database CPU utilization has come down to 65% resulting more number of tests and transactions.

Let me reiterate, Its always important to look at the License type of your SQL Server !!!