Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2003
    Posts
    144

    Exclamation Unanswered: 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?

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by EMoscosoCam
    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    I vote for bears. they are always on the threatdown causing trouble.

  4. #4
    Join Date
    Mar 2003
    Posts
    144

    Post The Stored Procedure

    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)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •