Query never ends execution

Hello

I have a .Net application that calls an stored procedure. When it does, the execution goes and never ends (I have to kill the windows process). When I call the sp from within the Management Studio, it also never ends executing and I have to cancel the query. But, when I call it immediately after, it takes 45 seconds to complete.

Now, the sp has several parts and I have made that it prints a message at the end of each part so that I can read where it stops. Strange enough, it completes all parts except the last one, which has the form INSERT INTO myLocalTable SELECT * FROM MyRemoteTable. But if I execute the Select independetly, I discover that it brings no rows! Now, many of the @.@.rowcount printed after the execution of the other parts shows zero rows involved or just a few. I am not using cursors, each part is an UPDATE statement or an INSERT.

TestMachine1 runs SQL2005 SP2 and has as linked server myRemoteServer (SQL2000) server. The stored procedure in TestMachine1 inserts rows to a table in myRemoteServer and brings back some rows.

What could be wrong?What could be wrong?
I'll guess it is one of these issues:
A) Gerbils nibbling on your network cable.
B) Global warming affecting your server environment.
C) Bears. Big nasty ones.
D) Some problem with the code you did not bother posting.|||I vote for bears. they are always on the threatdown causing trouble.|||Here is the general structure of the problematic stored procedure (Consider that there are 2 remote tables and 2 local tables instead of just one, and that the operations are made for both of them in a similar fashion):

declare @.LastUpdate datetime
declare @.Workstation varchar(250)

set @.Workstation=host_name()

SET NOCOUNT ON

execute spGetLastUpdate @.LastUpdate output

insert into Synonym_MyRemoteTable1Temp
select
@.Workstation,
ID,
Value1,
Value2,
Value3
from myLocalTable1
where UpdateTimeStamp>@.LastUpdate

execute Synonym_spMyRemoteProcedure @.Workstation -- Explained below
/*
This remote procedure makes an update and an insert as follows:

update MyRemoteTable1
set
Value1=MyRemoteTable1Temp.Value1,
Value2=MyRemoteTable1Temp.Value2,
Value3=MyRemoteTable1Temp.Value3
from MyRemoteTable1
inner join MyRemoteTable1Temp on MyRemoteTable1Temp.ID=MyRemoteTable1.ID and WorkStation=@.WorkStation

insert into MyRemoteTable1
(
ID,
Value1,
Value2,
Value3,
UpdateTimeStamp
)
select
ID,
Value1,
Value2,
Value3,
GetDate()
from MyRemoteTable1Temp
where
WorkStation=@.WorkStation and ID not in (select ID from MyRemoteTable1)
*/

update MyLocalTable1
set
Value1=T.Value1,
Value2=T.Value2,
Value3=T.Value3,
UpdateTimeStamp=GetDate()
from MyLocalTable1
inner join
(
select
ID,
Value1,
Value2,
Value3
from Synonym_MyRemoteTable1
where UpdateTimeStamp>@.LastUpdate and
WorkStation<>@.Workstation

) as T on T.ID=MyLocalTable1.ID

insert into MyLocalTable1
(
ID,
Value1,
Value2,
Value3,
UpdateTimeStamp
)
select
ID,
Value1,
Value2,
Value3,
GetDate()
from Synonym_MyRemoteTable1
where
UpdateTimeStamp>@.LastUpdate and
Workstation<>@.WorkStation and
ID not in (select ID from MyLocalTable1)