Recursive Triggers on SQL Server and User Profile Service Problem

25/07/2011 Leave a comment

Recently Microsoft has published a “FAST PUBLISH” article about User Profile Application service starting problem with FIM Syncronization service.In KB defined problem is caused by The ‘Recursive Triggers Enabled’ property for the Model database is set to ‘True’ in the SQL instance. And the error is in your application log

The server encountered an unexpected error and stopped.
“ERR: MMS(6016): sql.cpp(5580): Query (update [mms_run_history] set [is_run_complete] = 1,[run_result] = N’stopped-server’,[end_date] = <Date & Time> where ([is_run_complete] = 0)) performed with error
ERR: MMS(6016): sql.cpp(5633): Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).
….

What is a Recursive Triggers ?
A requirsive trigger is a trigger that fired by the other triggers or intreacting object that fire the trigger when executed recursively.

SQL Server also allows for recursive invocation of triggers when the RECURSIVE_TRIGGERS setting is enabled using ALTER DATABASE.

Recursive triggers enable the following types of recursion to occur:

  • Indirect recursion
    With indirect recursion, an application updates table T1. This fires trigger TR1, updating table T2. In this scenario, trigger T2 then fires and updates table T1.
  • Direct recursion
    With direct recursion, the application updates table T1. This fires trigger TR1, updating table T1. Because table T1 was updated, trigger TR1 fires again, and so on.

The following example uses both indirect and direct trigger recursion Assume that two update triggers, TR1 and TR2, are defined on table T1. Trigger TR1 updates table T1 recursively. An UPDATE statement executes each TR1 and TR2 one time. Additionally, the execution of TR1 triggers the execution of TR1 (recursively) and TR2. The inserted and deleted tables for a specific trigger contain rows that correspond only to the UPDATE statement that invoked the trigger.

Note:The previous behavior occurs only if the RECURSIVE_TRIGGERS setting is enabled by using ALTER DATABASE. There is no defined order in which multiple triggers defined for a specific event are executed. Each trigger should be self-contained.

Disabling the RECURSIVE_TRIGGERS setting only prevents direct recursions. To disable indirect recursion also, set the nested triggers server option to 0 by using sp_configure.

If any one of the triggers performs a ROLLBACK TRANSACTION, regardless of the nesting level, no more triggers are executed.

How to change “Recursive Triggers Enabled” property to false ?

From the SQL Server Management Studio, expand ‘System Databases’ > Right-click ‘Model’ | Properties | Options | under Miscellaneous section, set ‘Recursive Triggers Enabled’ property to ‘False’.

or.

The recursive trigger setting works on a database level . For checking the status of the recursive setting, use this command:

 EXEC sp_dboption '<name of db>', 'recursive triggers' -

for enabling Recusive Triggers:

 EXEC sp_dboption '<name of db>', 'recursive triggers', 'true'

for disabling Recursive Triggers:

 EXEC sp_dboption '<name of db>', 'recursive triggers', 'false'

For our senario use like this:

EXEC sp_dboption 'Model', 'recursive triggers', 'false'

Resources:
http://msdn.microsoft.com/en-us/library/ms189799.aspx
http://support.microsoft.com/kb/2579951

 

 

Advertisement

Filed under Sharepoint 2010, T-SQL Tagged with , , , , , ,

T-SQL RANK() , DENSE_RANK() , NTILE(), ROW_NUMBER()

26/01/2010 Leave a comment

Rank():
Returns the rank of each row within the partition of a result set. The rank of a row is one plus the number of ranks that come before the row in question.
Usage: RANK ( )    OVER ( [ < partition_by_clause > ] < order_by_clause > )  

Dense_Rank() :
Returns the rank of rows within the partition of a result set, without any gaps in the ranking. The rank of a row is one plus the number of distinct ranks that come before the row in question.
Usage: DENSE_RANK ( )    OVER ( [ < partition_by_clause > ] < order_by_clause > )  

Ntile():
Distributes the rows in an ordered partition into a specified number of groups. The groups are numbered, starting at one. For each row, NTILE returns the number of the group to which the row belongs.
Usage: NTILE (integer_expression)    OVER ( [ <partition_by_clause> ] < order_by_clause > )  

Row_Number():
Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.
ROW_NUMBER ( )     OVER ( [ <partition_by_clause> ] <order_by_clause> )  

Example  

All Recordset :   

select * from finals 
RecordID Name Surname Course Point
1 Bugra Postaci Mathematics 89
2 Bugra Postaci Lecture 90
3 Dany Lowe Mathematics 75
4 Dany Lowe Lecture 85
5 Alice Marcel Mathematics 77
6 Alice Marcel Lecture 100
7 Simon Duru Mathematics 45
8 Simon Duru Lecture 58

 

select row_number() over(order by RecordID desc) as RowNumber ,* from finals 

RowNumber RecordID Name Surname Course Point
1 8 Simon Duru Lecture 58
2 7 Simon Duru Mathematics 45
3 6 Alice Marcel Lecture 100
4 5 Alice Marcel Mathematics 77
5 4 Dany Lowe Lecture 85
6 3 Dany Lowe Mathematics 75
7 2 Bugra Postaci Lecture 90
8 1 Bugra Postaci Mathematics 89

 

select ntile(2) over(order by RecordID desc) as [Ntile] ,* from finals 

Ntile RecordID Name Surname Course Point
1 8 Simon Duru Lecture 58
1 7 Simon Duru Mathematics 45
1 6 Alice Marcel Lecture 100
1 5 Alice Marcel Mathematics 77
2 4 Dany Lowe Lecture 85
2 3 Dany Lowe Mathematics 75
2 2 Bugra Postaci Lecture 90
2 1 Bugra Postaci Mathematics 89

 

as you see ntile function just divide the scope with given number 

select rank() over (order by Surname) as [Rank], * from finals 

Rank RecordID Name Surname Course Point
1 7 Simon Duru Mathematics 45
1 8 Simon Duru Lecture 58
3 3 Dany Lowe Mathematics 75
3 4 Dany Lowe Lecture 85
5 5 Alice Marcel Mathematics 77
5 6 Alice Marcel Lecture 100
7 1 Bugra Postaci Mathematics 89
7 2 Bugra Postaci Lecture 90

 
rank function is working like  match one “Duru” as 1 match another “Duru” as 1 but total count as 2 , next surname matches “Lowe” as count +1 as 3 , another “Lowe” as 3  ; now total count is 4 matching next surname as “Marcel” is count + 1 as 5 and goes on …. 

select dense_rank() over (order by Surname) as [DenseRank], * from finals 

DenseRank RecordID Name Surname Course Point
1 7 Simon Duru Mathematics 45
1 8 Simon Duru Lecture 58
2 3 Dany Lowe Mathematics 75
2 4 Dany Lowe Lecture 85
3 5 Alice Marcel Mathematics 77
3 6 Alice Marcel Lecture 100
4 1 Bugra Postaci Mathematics 89
4 2 Bugra Postaci Lecture 90

 Dense rank its obvious like shown. 

 

if you want to make your denserank number reseting by groups there is a way use PARTITION BY
below example reseting ranks by using “partition by” by course column
select dense_rank() over (Partition by course order by Surname) as [DenseRank], * from finals  

DenseRank RecordID Name Surname Course Point
1 8 Simon Duru Lecture 58
2 4 Dany Lowe Lecture 85
3 6 Alice Marcel Lecture 100
4 2 Bugra Postaci Lecture 90
1 7 Simon Duru Mathematics 45
2 3 Dany Lowe Mathematics 75
3 5 Alice Marcel Mathematics 77
4 1 Bugra Postaci Mathematics 89

Thats all folks… 

Filed under T-SQL Tagged with , , , ,

T-SQL Row_Number() and Paging

19/01/2010 Leave a comment

In MS SQL 2005 you can use Row_Number() function for paging:

DECLARE @PageNumber AS INT;
DECLARE @PageSize AS INT;
SET @PageNumber = 2;
SET @PageSize = 10;

WITH CustomerCTE AS
(
    SELECT ROW_NUMBER() OVER(ORDER BY LoginDate, CustomerID) AS RowNumber
          ,CustomerID
          ,LoginDate
          ,CustomerID
          ,EmployeeID
      FROM dbo.Customers
)

SELECT *
  FROM CustomerCTE
 WHERE RowNumber BETWEEN (@PageNumber – 1) * @PageSize + 1 AND @PageNumber * @PageSize
 ORDER BY LoginDate , CustomerID;

Filed under T-SQL Tagged with , , ,

Rebuild all indexes in your SQL Server by db names

13/01/2010 Leave a comment

DECLARE @Database VARCHAR(255)
DECLARE @Table VARCHAR(255)
DECLARE @cmd NVARCHAR(500)
DECLARE @fillfactor INT

SET @fillfactor = 90

DECLARE DatabaseCursor CURSOR FOR
SELECT name FROM master.dbo.sysdatabases
WHERE name IN (‘   ‘)  — [ WRITE HERE YOUR DB NAMES,… ]
ORDER BY 1

OPEN DatabaseCursor

FETCH NEXT FROM DatabaseCursor INTO @Database
WHILE @@FETCH_STATUS = 0
BEGIN

SET @cmd = ‘DECLARE TableCursor CURSOR FOR SELECT table_catalog + ”.” + table_schema + ”.” + table_name as tableName
FROM ‘ + @Database + ‘.INFORMATION_SCHEMA.TABLES WHERE table_type = ”BASE TABLE”’

— create table cursor
EXEC (@cmd)
OPEN TableCursor

FETCH NEXT FROM TableCursor INTO @Table
WHILE @@FETCH_STATUS = 0
BEGIN

SET @cmd = ‘ALTER INDEX ALL ON ‘ + @Table + ‘ REBUILD WITH (FILLFACTOR = ‘ + CONVERT(VARCHAR(3),@fillfactor) + ‘)’
EXEC (@cmd)
FETCH NEXT FROM TableCursor INTO @Table
END

CLOSE TableCursor
DEALLOCATE TableCursor

FETCH NEXT FROM DatabaseCursor INTO @Database
END
CLOSE DatabaseCursor
DEALLOCATE DatabaseCursor

Filed under T-SQL Tagged with , , ,

Together one column’s data in one string

11/01/2010 Leave a comment

There is a useful expression in T-SQL  “COALESCE()” we can use it for combining a column data into a string easyly

For example

Declare @sumtextnvarchar(100)
select top 5 @sumtext = Coalesce(@sumtext + ‘;’ ,”) + Cast(Name as nvarchar)
from Country
print @sumtext

Output:

Afrika;Australi en Nieuw-Zeeland;Belgi;Benelux;Bulgarije;Canada;China;Duitsland;EU-landen;Frankrij

May the code be with you !

Filed under T-SQL Tagged with ,