Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

Hello friends, I don’t know what made me to write a blog, either it could be because i sit next to latheesh every day or inspired by reading his blogs daily, Any how, let me join with my Guru in sharing my experience.

we recently got a requirement to maintain the data integrity between two tables on a column which does not have any relationship between them.  Here is the scenario

we have two tables as listed below:



CREATE TABLE dbo.PEOPLE ( PEOPLE_ID INT ,EMPNAME VARCHAR(50), EMP_EMAIL VARCHAR(100))
CREATE TABLE dbo.USERS ( USERID INT ,USERNAME VARCHAR(50), USER_EMAIL VARCHAR(100))

INSERT INTO PEOPLE VALUES (1,'KIRAN','KIRANREDDY@GMAIL.COM')
INSERT INTO USERS VALUES (1,'KIRAN','KIRANREDDY2@GMAIL.COM')

I would like to maintain the same email in both tables for an employee, and email can be updated from either of these two tables.

So i have tried implementing the triggers on both tables when ever there is an update on email, I have to update the same email on both tables.

Here is the code I have implemented:



CREATE TRIGGER UPD_TRG_PEOPLE ON PEOPLE AFTER UPDATE AS
BEGIN
IF UPDATE(EMP_EMAIL)
UPDATE U SET USER_EMAIL=I.EMP_EMAIL FROM USERS U INNER JOIN INSERTED  I ON U.USERID=I.PEOPLE_ID 
END

CREATE TRIGGER UPD_TRG_USERS ON USERS AFTER UPDATE AS
BEGIN
IF UPDATE (USER_EMAIL)
UPDATE P SET P.EMP_EMAIL=I.USER_EMAIL FROM PEOPLE P INNER JOIN INSERTED  I ON P.PEOPLE_ID=I.USERID 
END

When I tried updating the email from people, It has resulted the following error:



Msg 217, Level 16, State 1, Procedure upd_trg_users, Line 3
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

Solution :
Initially I have tried avoiding this error by changing the nested triggers Server Configuration Option


EXEC sp_configure 'show advanced options', 1;  
GO  
RECONFIGURE ;  
GO  
EXEC sp_configure 'nested triggers', 0 ;  
GO  
RECONFIGURE;  
GO

Even though this error has not appeared when I tried changing email, I feel that this is not an Ideal solution . As this is server level configuration which is applicable across  all databases. This may have other detrimental impact.

Hence i have found another solution with simple tweak in the trigger code:



CREATE TRIGGER UPD_TRG_PEOPLE ON PEOPLE AFTER UPDATE AS
BEGIN
IF UPDATE(EMP_EMAIL) AND  EXISTS (SELECT 1 FROM USERS   U INNER JOIN INSERTED I ON U.USERID = I.PEOPLE_ID WHERE I.EMP_EMAIL <> U.USER_EMAIL )
UPDATE U SET USER_EMAIL=I.EMP_EMAIL FROM USERS U INNER JOIN INSERTED  I ON U.USERID=I.PEOPLE_ID 
END

CREATE TRIGGER UPD_TRG_USERS ON USERS AFTER UPDATE AS
BEGIN
IF UPDATE (USER_EMAIL) AND  EXISTS (SELECT 1 FROM PEOPLE   P INNER JOIN INSERTED I ON P.PEOPLE_ID=I.USERID  WHERE P.EMP_EMAIL <> I.USER_EMAIL )
UPDATE P SET P.EMP_EMAIL=I.USER_EMAIL FROM PEOPLE P INNER JOIN INSERTED  I ON P.PEOPLE_ID=I.USERID 
END

Hope you enjoyed this post, please share your thoughts on the same.

Advertisements

The identifier ‘source column name’ cannot be bound. Only target columns and columns in the clause scope are allowed in the ‘WHEN NOT MATCHED BY SOURCE’ clause of a MERGE statement.

Problem Statement:

When you want to have a condition in a MERGE with source and target for WHEN NOT MATCHED clause, you may likely to get an error message as below, if you put the condition directly to the MERGE statement.

The identifier ‘source column name’ cannot be bound. Only target columns and columns in the clause scope are allowed in the ‘WHEN NOT MATCHED BY SOURCE’ clause of a MERGE statement.

Lets quickly check an example:


Drop table T1,T2
create Table T1(Col1 int, Col2 int)
Insert into T1 Values(1,1),(1,2),(1,3),(1,4),(3,1)--,(2,1)

create Table T2(Col1 int, Col2 int)
Insert into T2 Values(1,1),(1,2),(1,3),(1,5),(2,1)

Select * From T1

		MERGE T1 TG
			USING T2 SR
			ON TG.Col1= SR.Col1 and TG.Col2 = SR.Col2
			WHEN MATCHED THEN
				UPDATE
				SET TG.Col2 = SR.Col2
			WHEN NOT MATCHED BY TARGET THEN
				INSERT  (Col1, Col2)
				VALUES (Col1, Col2)
			WHEN NOT MATCHED BY SOURCE AND TG.Col1 = SR.COl1 THEN
				DELETE;

Select * From T1

Solution:

Here is a simple way of solving the mentioned issue.


Drop table T1,T2
create Table T1(Col1 int, Col2 int)
Insert into T1 Values(1,1),(1,2),(1,3),(1,4),(3,1)--,(2,1)

create Table T2(Col1 int, Col2 int)
Insert into T2 Values(1,1),(1,2),(1,3),(1,5),(2,1)

Select * From T1

		MERGE T1 TG
			USING T2 SR
			ON TG.Col1= SR.Col1 and TG.Col2 = SR.Col2
			WHEN MATCHED THEN
				UPDATE
				SET TG.Col2 = SR.Col2
			WHEN NOT MATCHED BY TARGET THEN
				INSERT  (Col1, Col2)
				VALUES (Col1, Col2)
			WHEN NOT MATCHED BY SOURCE AND TG.Col1 in (Select Col1 From T2) THEN
				DELETE;

Select * From T1

ALTER DATABASE failed because a lock could not be placed on database ‘DBNAME’. Try again later. in SQL Server

At times, we may need to take a database offline. And there is a chance that we encounter an error message like below.
How do we solve this issue and take the database offline?


Msg 5061, Level 16, State 1, Line 1
ALTER DATABASE failed because a lock could not be placed on database 'DBNAME'. Try again later.

Solution:
If there are any connections open, then there is a lock on the database while getting the database into Single user mode/taking offline.
To resolve the issue, as a first step you need to kill all the sessions on the database followed by the ALTER database statement.


USE master;

DECLARE @killSessions varchar(8000) = '';  
SELECT @killSessions = @killSessions + 'kill ' + CONVERT(varchar(5), spid) + ';'  
FROM master..sysprocesses  
WHERE dbid = db_id('dbname')
EXEC(@killSessions); 

ALTER DATABASE dbname SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
ALTER DATABASE dbname SET OFFLINE WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE dbname  SET MULTI_USER;

The number of row value expressions in the INSERT statement exceeds the maximum allowed number of 1000 row values.

One of my colleague was facing an issue while inserting 2000 values to a table variable as below:


 DECLARE @EMPLOYEEDETAILS TABLE
(OLDEMPLOYEECODE NVARCHAR(30), NEWEMPLOYEECODE NVARCHAR(30))

INSERT INTO @EMPLOYEEDETAILS(OLDEMPLOYEECODE, NEWEMPLOYEECODE)
VALUES
('93466','0000007'),
('93467','0000010'),
...
...(2000 Records)
...
('93467','00002000')

Error Message:
Msg 10738, Level 15, State 1, Line 1006
The number of row value expressions in the INSERT statement exceeds the maximum allowed number of 1000 row values.

This is a self explanatory error message as SQL Server does not support row value expressions for more than 1000 values.
To overcome the issue, the code has been modified as below and the query has been executed successfully.

Solution:


DECLARE @EMPLOYEEDETAILS TABLE
(OLDEMPLOYEECODE NVARCHAR(30), NEWEMPLOYEECODE NVARCHAR(30))

INSERT INTO @EMPLOYEEDETAILS(OLDEMPLOYEECODE, NEWEMPLOYEECODE)
SELECT * FROM (VALUES
('93466','0000007'),
('93467','0000010'),
...
...(2000 Records)
...
('93467','00002000')
) A(Col1, Col2)

You may share any other alternatives/thoughts….

Could not load assembly ‘Microsoft.SqlServer.Connectioninfo, Version=10.0.0.0, Culture=netural, PublicKeyToken=89845dcd8080cc91’ or one Of its dependencies. The system cannot find the file specified.

Problem Statement:

When trying to migrate SQL Server 2008 R2 to SQL Server 2016, we identified an issue with the following error:

Could not load assembly ‘Microsoft.SqlServer.Connectioninfo, Version=10.0.0.0, Culture=netural, PublicKeyToken=89845dcd8080cc91’ or one
Of its dependencies. The system cannot find the file specified.

As a knee jerk reaction, we initially thought it was an issue with SQL Server 2016 installation and need to install the Feature Pack for SQL Server 2016. Once applied the feature Pack, we again tested and found the same issue unfortunately(but really fortunate one!!!).

Analyzing further(I mean, reading the error message clearly, It is found the version 10.0.0 that represents Microsoft® SQL Server® 2008.
As we do not have SQL Server 2008 installed in the environment and the code(.NET application) has some reference to the version mentioned, this was not able to find the dlls in the specified path(C:\windows\assemblies\).

To proceed with our testing, we installed SQL Server 2008 R2 Feature pack and resumed our testing. Finally, it worked like a treat!!!

The learning is we always need to go to the facts(error message) than blindly presume things!!!

please find the below information for various version feature pack references:

Microsoft® SQL Server® 2016 Feature Pack (Version: 13.0.1601.0)
https://www.microsoft.com/en-us/download/details.aspx?id=52676
Microsoft® SQL Server® 2014 SP2 Feature Pack (Version: 12.0.5000.0)
https://www.microsoft.com/en-us/download/details.aspx?id=53164
Microsoft® SQL Server® 2014 Feature Pack (Version: 12.0.0.0)
http://www.microsoft.com/en-us/download/details.aspx?id=42295
Microsoft® SQL Server® 2012 SP1 Feature Pack (Version: 11.0.0.0)
http://www.microsoft.com/en-us/download/details.aspx?id=35580
Microsoft® SQL Server® 2012 Feature Pack (Version: 11.0.2100.60)
https://www.microsoft.com/en-us/download/details.aspx?id=29065
Microsoft® SQL Server® 2008 R2 SP2 Feature Pack (Version: 10.50.4000.0)
https://www.microsoft.com/en-us/download/details.aspx?id=30440
Microsoft® SQL Server® 2008 R2 SP1 Feature Pack (Version: 10.50.2500.0)
http://www.microsoft.com/en-us/download/details.aspx?id=26728
Microsoft® SQL Server® 2008 R2 Feature Pack (Version: 10.50.1600.1)
https://www.microsoft.com/en-us/download/details.aspx?id=16978
Microsoft SQL Server 2008 Service Pack 4 Feature Pack (Version: 10.0.6000.29)
https://www.microsoft.com/en-in/download/details.aspx?id=44277
Microsoft SQL Server 2008 Service Pack 3 Feature Pack (Version: 10.00.5500.00)
https://www.microsoft.com/en-us/download/details.aspx?id=27596
Microsoft SQL Server 2008 Service Pack 2 Feature Pack (Version: 10.00.4000.00)
https://www.microsoft.com/en-us/download/details.aspx?id=6375
SQL Server 2008 Service Pack 1 (Version: 10.00.2531.00)
https://www.microsoft.com/en-in/download/details.aspx?id=20302