linked server insode triggers problems?

Dear all

I am trying to use linked server objects inside a trigger and have some major problems.

Just to explain what i am trying to achieve:

My server A is SQL 2000. When ever a row is added to a table on server A i would like to send some of the inserted values to server B which is a physically different computer and has SQL 2005.

To do that i created linked server object on the SQL 2000 side.

To test the linked server object i go:

Select * ServerB.Database.dbo.Table

This works perfectly and i get the results. I then test the same inside a stored procedure but i do some parameterised queries.

Select id from ServerB.Database.dbo.Table where id=@.myId

This also works perfectly inside the stored procedure.

BUT NOW IT COMES THE FUN PART.

As soon as i place this inside the trigger it just doesnt work. My trigger has nothing else pretty much except for that. Here is a sample:

BEGIN
SET NOCOUNT ON
SET xact_abort ON

DECLARE @.myValue nvarchar(50)

SET @.myValue = '6357'

SELECT * from ServerB.Database.dbo.Table
Where id = @.myValue

END

It just gives me a timeout error. But what is even worse is that after this the whole database is crashed and i have to restart the database service to make it work.

I checked both servers and they have the service DTS for the distributed transaction on. No proxies, no firewals. Also i checked the servers configuration and they have RPC,RPC OUT and Data Access enabled.

I have tried everything over the last week and nothing has worked for me.

Any advice would be much much apreciated.

Sincerely
Dan

When you use 4-part name, the entire table is pulled from the remote server to the local before any filtering is applied. It's never a good idea to fetch & return results from a trigger.

Because trigger, by default, participates in the current transaction, it's best to keep the transaction as short as possible. In the case, you implicitly promote the transaction to a distributed transaction. That means both parties must play nice and DTC (which has a timeout) definitely is needed.

We need a bit more info as to what the timeout value refers to. Do enable 7300 trace flag and post the error here.

e.g.

dbcc traceon (-1,7300,3604)

go

|||

Dan_Dan wrote:

My server A is SQL 2000. When ever a row is added to a table on server A i would like to send some of the inserted values to server B which is a physically different computer and has SQL 2005.

Trigger is bad idea to do this task, you can utilize the Replication feature here.

See more on, http://msdn2.microsoft.com/en-us/library/ms151198.aspx

|||

You have violated a couple of 'no-nos' with TRIGGERS, and as you have discovered, that violation can have severe consequences.

First, a TRIGGER should NOT return data to the client. Use a Stored Procedure or UDF to return data to the client. TRIGGERs were not designed to be part of the client data interface, but were designed for internal operations only.

Second, a TRIGGER should NOT 'reach' outside the current processing thread. When you access data on a remote server, your client, your TRIGGER, and your local server, are at the mercy of the remote server. Anything disruptive in the connection, and/or the remote server will negatively effect your local operation -as you noticed. (This second factor can be worked around by using some form of 'store and forward' service, such as Service Broker. But again, not to retrieve data from a remote server.)

While you can, and will see these 'no-nos' violated, it just isn't a good idea.|||

This is the line from the sql logs.

I got this from C:\Program Files\Microsoft SQL\MSQL\LOGS

2007-08-09 12:19:40.60 spid8 This SQL Server has been optimized for 8 concurrent queries. This limit has been exceeded by 1 queries and performance may be adversely affected.

Before i enabled the dbcc traceon option as suggested in the post. But it does not give me any usable information to figure out as to why this is happening.

What i cant really understand is as to why the query would run inside a stored procedure or if ran just independently in the query analyser but it not in the trigger.

|||

You're running on MSDE, a free version of sql2k which has workload governor set. This is by design so you must upgrade to a paid version if you want to rid of the error.

http://msdn2.microsoft.com/en-us/library/aa258279(sql.80).aspx

As to the problem with the trigger, it could be authentication or network problem. We would need more info to nail it down. (A full error log would be helpful here).

sql