Nested CLR trigger timeout error

See more:

SQL

SQL-Server

service

web

CLR

, +

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.