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. !!
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 🙂
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:-
PowerShell
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!
In this post we are going to see how we can convert the Row values into comma separated String. Different SQL versions to achieve this (mentioned below), lets discuss one by one.
Using Coalesce and Substring Function
Using Replace Function
Using Stuff Function
Using Coalesce and Substring Function
In this example, we have converted the column values (RowNO, RowValues) from RowsIntoString table into String value with comma delimiter using coalesce and substring SQL functions.
drop table if exists RowsIntoString
create table RowsIntoString (RowNo int, RowValues nvarchar(100))
insert into RowsIntoString (RowNo, RowValues)
values (1,'first'),(2,'second'),(4,'third'),(4,'fourth'),(5,'fifth'),(7,'sixth'),(7,'seventh')
,(7,'eighth'),(9,'nineth'),(10,'tenth'),(11,'eleventh')
select * from RowsIntoString
---using Coalesce---
DECLARE @CSkeycol VARCHAR(MAX), @CSkeycol2 VARCHAR(MAX)
select @CSkeycol=COALESCE(@CSkeycol + ', ', '')+cast(RowNo as nvarchar) from RowsIntoString
select @CSkeycol as RowsIntoStringValue
select @CSkeycol2=COALESCE(@CSkeycol2 + ''', ''', '')+RowValues from RowsIntoString
select ''''+@CSkeycol2+'''' as RowsIntoStringValue
---using Substring---
declare @tmp varchar(MAX)=''
declare @tmp2 varchar(MAX)=''
select @tmp = @tmp + cast(RowNo as nvarchar) + ', ' from RowsIntoString
select SUBSTRING(@tmp, 0, LEN(@tmp)) as RowsIntoStringValue
select @tmp2 = @tmp2 + RowValues + ''', ''' from RowsIntoString
select ''''+SUBSTRING(@tmp2, 0, LEN(@tmp2)-2) as RowsIntoStringValue
Using Replace Function
In this example, we have converted the multi row string values into string value with comma delimiter using Replace SQL function.
declare @stg varchar(MAX)
set @stg='second
third
fourth
fifth
sixth
seventh'
select '''' + replace(@stg, char(10),''', ''') + '''' as RowsIntoStringValue
set @stg='1
2
3
4
5
6
7'
select replace(@stg, char(10),', ') as RowsIntoStringValue
Using Stuff Function
In this example, we have Concatenated the Rowvalues with comma which is having same RowNo (in other words, concatenating the RowValues column if RowNo column has more than one values) using Stuff SQL functions.
select * from RowsIntoString where RowNo in (4,7)
select distinct t1.RowNo,
STUFF(
(SELECT ', ' + RowValues
FROM RowsIntoString t2
where t1.RowNo = t2.RowNo
FOR XML PATH (''))
, 1, 1, '') AS RowValues
from RowsIntoString t1
where RowNo in (4,7)
There is another post written by Latheesh which details another way with string_AGG.
Thanks for reading this blog post,If you enjoyed this, please share it with your friends!
Today, there was a requirement from one of my colleague “How to identify a function references in SQL Server?”.
Infact, He was looking for a script to identify the reference places(objects) where the function is being used in SQL Server. Please find the script below that we used to identify the references.
Script:
DECLARE @ObjectName NVARCHAR(100)
SET @ObjectName = N'split' --Give your function/proc name
--Table variable to hold the results from different databases
Declare @ResultTable Table(SourceSchema sysname, Sourceobject sysname, ReferencedDB sysname, ReferencedSchema sysname, ReferencedObject sysname)
Declare @MyQuery NVARCHAR(MAX) = N'
USE [?]
SELECT DISTINCT
SourceSchema = OBJECT_SCHEMA_NAME(sed.referencing_id)
,SourceObject = OBJECT_NAME(sed.referencing_id)
,ReferencedDB = ISNULL(sre.referenced_database_name, DB_NAME())
,ReferencedSchema = ISNULL(sre.referenced_schema_name,
OBJECT_SCHEMA_NAME(sed.referencing_id))
,ReferencedObject = sre.referenced_entity_name
FROM sys.sql_expression_dependencies sed
CROSS APPLY sys.dm_sql_referenced_entities(OBJECT_SCHEMA_NAME(sed.referencing_id) + ''.'' + OBJECT_NAME(sed.referencing_id), ''OBJECT'') sre
WHERE sed.referenced_entity_name like ''%' + @ObjectName + '%'' AND sre.referenced_entity_name like ''%' + @ObjectName + '%''';
Insert into @ResultTable
EXEC sp_MSforeachdb @MyQuery
Select * From @ResultTable
The above script uses sp_MSforeachdb to execute the query in all databases available in the SQL Server.
Please note that sp_MSforeachdb is an undocumented procedure, Microsoft may change the functionality or definition of this Stored Procedure at any time.
See Also:
You may be interested to know more on sp_MSforeachdb usage, please visit the below links
SQLZealots 