Category: SQL Server

Errors and Exceptions, SQL, SQL Server

Exception: The module being executed is not trusted. Either the owner of the database of the module needs to be granted authenticate permission, or the module needs to be digitally signed.

When we were trying to create a procedure and trying to execute the procedure, it was throwing an error as

The module being executed is not trusted. Either the owner of the database of the module needs to be granted authenticate permission, or the module needs to be digitally signed.

create or Alter procedure GetAGNodes
With encryption, EXECUTE AS owner

Though it was granted with execute permission with the user, we need to give the database trustworthy to be enabled. The below script needs to be executed to resolve the issue.

ALTER DATABASE DBNAME SET TRUSTWORTHY ON

Hope this helps, if there are any other related issues and resolutions, please feel free to share as a comment!

Errors and Exceptions, SQL, SQL Server

Exception: “Invalid parameter 1 specified for datediff.” in SQL Server

Problem Statement:

One of the view creation scripts in our deployment failed continuously with the below exception:

Invalid parameter 1 specified for datediff.
Incorrect syntax near the keyword ‘AS’.

The root cause of this error is as below.

FLOOR((DATEDIFF("W",ml.column1,ML.column2)+1)/7) AS aliasname

Solution:

FLOOR((DATEDIFF(W,ml.column1,ML.column2)+1)/7) AS aliasname

The change is that the datepart has been modified to remove the double quote.

SQL Server, T-SQL

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, SQL Server, T-SQL

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 🙂

Cmd Shell, Powershell Scripts, SQL, SQL Server, T-SQL

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!