Category: SQL

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!

SQL

ANY, ALL & SOME Operators in SQL Server

ANY is a SQL operator that used to compare a scalar value with a set of single column resultset from a subquery. If any one of the value in the resultset is satifying the operator condition to the scalar value, then it return TRUE, else FALSE.

--Table Creation
create Table EmpDetails
(
	EmployeeID int,
	EmployeeName Varchar(200),
	Grade Int
)
CREATE tABLE EmpYears
(
	EmployeeID int,
	YearOfExp Int
)

--Data Population
Insert into EmpDetails Values 
(1,'Employee1',3),(2,'Employee1',4),(3,'Employee1',5),
(4,'Employee1',6),(5,'Employee1',7),(6,'Employee1',8),
(7,'Employee1',9)

Insert into EmpYears Values 
(1,3),(2,5),(3,7),(4,10),
(5,15),(6,20),(7,25)

--Get the employees with experiences more than 10 years
Select * From EmpDetails 
    where EmployeeID = ANY(Select EmployeeID From EmpYears where YearOfExp>10)

Drop Table EmpDetails, EmpYears

SOME operator is also working similar to ANY, the below returns same output as ANY.

--Get the employees with experiences more than 10 years
Select * From EmpDetails 
    where EmployeeID = SOME(Select EmployeeID From EmpYears where YearOfExp>10)

ALL operator in SQL Server returns TRUE if *all* the resultset from subquery is true to the operator condition to the scalar value.

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

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, 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 🙂

Linked Server, SQL

Error Message: Server is not configured for RPC in SQL Server

Recently one of my colleague reached out to me with an error message as : “Server is not configured for RPC”

This a typical issue with Linked Server. When we configure a linked server, we need to set up the right values for RPC & RPC Out . We can see these values in SSMS -> Right click on Linked Server -> Properties -> Server Options as in the below screen shot.

RPC / RPC OutRPC stands for Remote Procedure Call and allows you to run stored procedures on the Linked Server.  RPC enables Remote Procedure Calls from the specified server and RPC OUT enables Remote Procedure Calls to the specified server.

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