嵌套CLR触发器超时错误 [英] Nested CLR trigger timeout error
问题描述
嗨
我一整天都在谷歌上搜索这个问题的帮助而无法找到答案。
它涉及到SQL Server 2008 R2中的超时错误一个数据库中的T-SQL触发器在另一个数据库上触发CLR触发器。
这是我的场景：
第一个DB（PORTAL_MENSAGENS）有一个触发器，可以触发AVISO_TIPOS表上的任何更改：
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
第二个DB（USERS）有一个CLR触发器，可以在PORTAL_CACHE表上的任何INSERT上触发：
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
和CLR代码使用Web服务：
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();
}
}
}
（我认为这就是全部！）
测试场景：
1 - 如果我这样做只有CLR触发器才能在USERS数据库和服务上完成。
例如，运行这个手动查询，即使我在第一个DB：
(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 - 如果我更改第二个DB上的触发器使用plain T -SQL查询（例如，在某些表上插入任何内容仅用于调试目的）并使用INSERT查询触发第一个数据库上的触发器，一切顺利：
- 第一次触发器触发;
- 第一个触发器在第二个DB上插入数据;
- 第二个触发器火灾;
- 第二个触发器在其他桌子上插入数据。
3 - 更改第二个触发CLR代码并使用INSERT查询触发第一个DB上的触发器，我收到超时错误。
4 - 如果第一个触发器在第二个DB上的某个其他表上插入数据（没有连接触发器）一切顺利。
简而言之
如果单独使用，T-SQL触发器和CLR触发器都可以完美运行;
嵌套T-SQL触发器运行良好。 />
嵌套T-SQL和CLR触发器......没办法！总是超时错误。
SQL服务器上的允许触发其他人选项设置为True;
远程登录超时设置为20（也尝试60）。
问题：
有没有人知道为什么我''我不能使用嵌套触发器作为其中一个CLR触发器吗？
是否可以让它工作？
感谢您提供的所有帮助。
（更新）
我正试图找出这个东西绊倒的地方。
我测试了第一个触发器并插入了它的第一行，另一个查询只是将一个虚拟值插入一个虚拟表。
该行没有任何反应，所以我开始认为超时来自准备阶段。
我不知道从哪里开始。
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.
推荐答案
我自己解决了。
出于某种原因，我还没有找到，这种情况（一个数据库上的触发器在另一个触发CLR触发器的数据库上的表上插入行）根本不起作用。
转向Service Broker消息传递解决方案，它现在正在完善。
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.
这篇关于嵌套CLR触发器超时错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
