linked server inside triggers big problem

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

linked server inside triggers big problem

Author

Topic

DanDan
Starting Member

7 Posts

Posted - 2007-08-08 : 02:33:04

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

nr
SQLTeam MVY

12543 Posts

Posted - 2007-08-08 : 05:41:09

You aren't sending anything to ServerB - you are trying to extract data from it.
Could be transfering the whole table to your server to fulfill the query - might not be failing just taking a long time.
While this is happening it will hold any locks involved in the transaction which I suspect is the cause of your failure.
If the firing transaction has any involvement with ServerB then it's likely you have a deadly embrace.

This is a really bad thing to attempt and likely to cause you all sorts of problems (you are lucky that it's failing now).
Find a different solution e.g. putting an entry in a another table from the trigger and using a job to do whatever on the other server outside the transaction.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

DanDan
Starting Member

7 Posts

Posted - 2007-08-08 : 19:01:43

hi nr

i am not sending anything to server B bacause i havent got to that part succesfully yet. I need that piece of information so i can dynamicly build the insert query for a table in server B. So pretty much that will be the next step.

It cant be possibly sending the whole table because i am doing a selection based on a particular ID which is unique. So at most i will get 1 record back from the table in server B. I even tested that same query in the query analyser and it takes 0.32 seconds to run and it completes succesfully with a result of 1 record.

When i put it inside the trigger it just does not wont to run.

It seems to be having problems with the trigger only. The same thing inside a stored procedure works with no problems.

Any ideas?

Cheers
Dan

DanDan
Starting Member

7 Posts

Posted - 2007-08-08 : 21:57:41

What i have also tried is to not do the select but only try an insert.

I created a holding table on server B.

Then i go:

INSERT INTO [SERVERB].[TheDATABASE].[dbo].[THETable]
([ErrDesc])
VALUES
('value')

Now again when i run this outside the trigger in the query analyser it works with no problems. Finishes in 1 second.

Now when i try and do the same query inside the trigger it just wont run. I get the time out error again and my database i completely stuffed.

I read online few other posts and other people are having success with this. I dont know what i could be doing wrong. It is just when i run this inside the trigger that it gives me the problems. And makes it really hard to debug.

Hmmmmmmmmm

Any ideas

pootle_flump

1064 Posts

Posted - 2007-08-09 : 05:49:10

DanDan - nr's point is valid. You really should be keeping scope of a trigger within the database (let alone the server). Remember that a trigger is a synchronous process - the insert cannot complate until the trigger completes. Even if you get this working some network blip could bring your application down. Typically for this you would do as Nigel says - write enough information to a local table so that a job can be run every x seconds\ minutes\ hours and complete the processes.

nr
SQLTeam MVY

12543 Posts

Posted - 2007-08-09 : 05:54:28

>> It cant be possibly sending the whole table because i am doing a selection based on a particular ID which is unique.

Why not?
It's got to do the compare on one server so it's a choice between wending the ID to the remote server and doing the compare remotely or copying the table to the local server and doing the conmpare locally.
If the server thinks these may produce different results it would do it locally.
You can look at teh query lpan to see what it's doing or look at what happens on the remote server or see if anythnig is blocking on either server.

Anyway it's a bad idea to attempt this.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

DanDan
Starting Member

7 Posts

Posted - 2007-08-09 : 07:47:07

so guys u think that this would be better done through replication?

probably do a push transactional replication.

or maybe even use the sql agent that runs on an interval. like every minute

thanx for your input guys

but i still would like to find out what the problem is with the trigger. i cant explain it and i dont like leaving unexplained things:)

once i figure out what is happening i wont go nowhere near it. i already started preparing the replication on the table.

cheers

nr
SQLTeam MVY

12543 Posts

Posted - 2007-08-09 : 08:06:29

Use the profiler to see what's happening on both servers.
Check the query plan to see how it's actioning your query.
Use sp_who2 to see if anything's blocking.
Use this on both servers to see what's being executed and check the diskio to see if it's doing a lot
http://www.nigelrivett.net/SQLAdmin/sp_nrInfo.html

(Thought I posted all this before but maybe it was on another thread).

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

pootle_flump

1064 Posts

Posted - 2007-08-09 : 08:21:22

quote:
Originally posted by DanDan

so guys u think that this would be better done through replication?

probably do a push transactional replication.

or maybe even use the sql agent that runs on an interval. like every minute

If it is one table I wouldn't bother with the effort of getting your head round replication & setting it up (unless you are already comfy with it). I would just use sql agent. If you plan to do this for many tables then replication is a good option.

DanDan
Starting Member

7 Posts

Posted - 2007-08-09 : 19:02:03

im pretty comfy with the replication

have dealth with it a lot especially with replication to and from mobile devices

i didnt wanna use it here cos i think it is a bit of an overkill for the purpose.

the trigger seemed like a cool idea at the time:) not anymore

thank you for your help.

i am still trying to figure out why the trigger does not wanna work. not gona leave it until i found out what is happening:)

cheers

pootle_flump

1064 Posts

Posted - 2007-08-10 : 06:25:05

quote:
Originally posted by DanDan

i didnt wanna use it here cos i think it is a bit of an overkill for the purpose.

I agree.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources