Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL SQL-Server Web CLR Services , +
Hi
I've been Googling all day for a help on this problem and couldn't find any answer.
It concerns a timeout error in SQL Server 2008 R2 when a T-SQL Trigger in one Database fires a CLR Trigger on another Database.
 
This is my scenario:
 
First DB (PORTAL_MENSAGENS) has a trigger that fires on any change on the AVISO_TIPOS table:
USE [PORTAL_MENSAGENS]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[Update_Cache_Tipos_Avisos] 
   ON  [dbo].[AVISOS_TIPOS] 
   AFTER INSERT,DELETE,UPDATE
AS 
BEGIN
     SET NOCOUNT ON;
     INSERT INTO USERS.dbo.PORTAL_CACHE (TIPO) VALUES ('TIPOAVISO')
END
 
Second DB (USERS) has a CLR trigger that fires on any INSERT on the PORTAL_CACHE table:
USE [USERS]
GO
ALTER TRIGGER [dbo].[CLRAtualizaCache] ON [dbo].[PORTAL_CACHE]  AFTER  INSERT AS 
EXTERNAL NAME [CLRUpdateCache].[CLRUpdateCache.CLRUpdateCache].[CLRAtualizaCache]
GO
 
and the CLR code thas uses a Web Service:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.SqlServer.Server;
using System.Data.SqlClient;
using CLRUpdateCache.PortalEquipasService;
using System.ServiceModel;
using CLRUpdateCache.PortalAvisosService;
 
namespace CLRUpdateCache
{
    public class CLRUpdateCache
    {
        public static void CLRAtualizaCache()
        {
            SqlConnection conn = new SqlConnection(@"context connection=true");
            conn.Open();
            SqlCommand sqlComm = new SqlCommand(@"SELECT TIPO from inserted", conn);
            SqlDataReader dr = sqlComm.ExecuteReader();
            while (dr.Read())
            {
                switch ((string)dr[0])
                {
                    case "TIPOAVISO":
                        EndpointAddress endpoint_av = new EndpointAddress(new Uri("http://mywebserver/PortalServices/PortalCache/PortalAvisos.svc"));
                        PortalAvisosClient client_av = new PortalAvisosClient(new BasicHttpBinding(), endpoint_av);
                        client_av.PreencheTiposAvisos();
                        break;
                }
            }
            dr.Close();
            conn.Close();
        }
    }
}
 
(I think that's all!)
 
Testing scenarios:
 
1 - If I make it in such a way that only the CLR Trigger is fired all goes well on the USERS DB and on the service.
For instance, running this manual query, even if I'm in the first DB:
INSERT INTO USERS.dbo.PORTAL_CACHE (TIPO) VALUES ('TIPOAVISO')
 
2 - If I change the trigger on the second DB to use plain T-SQL queries (for instance, insert anything on some table just for debuging purposes) and fire the trigger on the first DB with an INSERT query, all goes well:
- first trigger fires;
- first trigger insert data on second DB;
- second trigger fires;
- second trigger inserts debuging data on some other table.
 
3 - Changing back the second trigger to the CLR code and fire the trigger on the first DB with an INSERT query, I get a timeout error.
 
4 - If the first trigger inserts data on some other table on the second DB (with no trigger attached) all goes well.
 
In short
 
The T-SQL trigger and the CLR trigger both run perfectly if used in separate;
Nesting T-SQL triggers also run well.
Nesting T-SQL and CLR triggers ... no way! Allways the timeout error.
 
The "Allow Triggers to Fire Others" option on the SQL server is set to True;
The "Remote Login Timeout" is set to 20 (also tried with 60).
 
The questions:
Does anyone has a clue why I'm not able to use nested triggers being one of them a CLR Trigger?
 
Is ther a way to get it to work?
 
Thanks for all the help I can get.
 

(Update)
I'm trying to figure out where this thing stumbles.
I tested the first trigguer and inserted, on it's first line, another query that just inserts a dummy value into a dummy table.
Nothing happens with that line so I'm starting to think that the timeout comes from the "preparing" stage.
I have no idea where to go from here.
Posted 26-Nov-12 5:38am
Edited 26-Nov-12 23:04pm
v3
Comments
djj55 at 26-Nov-12 12:11pm
   
Have you looked at TRIGGER_NESTLEVEL()?
Jorge J. Martins at 26-Nov-12 12:56pm
   
I didn't think I needed to.
I heven't setup any limitation to it. Besides, both trigger work when they are plain T-SQL.

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Solved it myself.
 
For some reason, I haven't found, this scenario (Trigger on one DB inserts rows on table on another DB that fires a CLR trigger) doesn't work at all.
 
Turned to a Service Broker messaging solution and it's working in perfection now.
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



Advertise | Privacy | Mobile
Web02 | 2.8.141022.1 | Last Updated 24 Jan 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100