Post reply

begin transaction

  • December 18, 2007 at 2:46 am

    #181385

    If I run this script (in QA):

    begin tran

    select *

    from database.dbo.tablename

    select *

    from remoteserverdatabase.dbo.tablename

    rollback tran

    the session hangs and I have to kill the process (using kill nn) - this then leaves a ghost process (as seen using sp_who2) and the only way to clean this up is to recycle the SQL server.

    Is this normal for SS to hang like this?

    The code is only an example - I might update a table on the local (dev) server and then select from the live server to make sure I have got the update right but it then goes pear shaped.

    Jez

  • December 18, 2007 at 7:21 am

    #761009

    Hi Jez,

    Could you provide some details about the OS of both servers and the SQL version of both?

    Cheers,

    Simon

  • December 18, 2007 at 7:31 am

    #761019

    They are both NT 5 and SQL 2000 (SP4).

    Jez

  • December 18, 2007 at 7:43 am

    #761024

    Hello Jez,

    It is very clear that you are opening the transaction on one machine and then passing on the pointer to do some operations on another server where there is no transaction opened but you are rolling back. BOL says

    The local transaction started by the BEGIN TRANSACTION statement is escalated to a distributed transaction if the following actions are performed before the statement is committed or rolled back:

    An INSERT, DELETE, or UPDATE statement that references a remote table on a linked server is executed. The INSERT, UPDATE, or DELETE statement fails if the OLE DB provider used to access the linked server does not support the ITransactionJoin interface.

    A call is made to a remote stored procedure when the REMOTE_PROC_TRANSACTIONS option is set to ON.

    Hope this helps you.


    Lucky

  • December 18, 2007 at 7:51 am

    #761027

    Lucky,

    In my scenario, all I am doing on the remote server is a select - there is no insert or update or delete statement on the remote server so there would be no escalation to a distributed transaction.

    Also, my statement does not fail - it just hangs and I cannot kill it (so that it does not appear in sp_who2) without stopping and starting the SQL server. That sounds different to a statement failing.

    Jez

  • December 18, 2007 at 7:57 am

    #761031

    Is the remote server running Windows Server 2003 or XP SP2?

    (I realise you said that you were running NT5 but I wasn't sure if you might have meant 5.1 or 5.2.)

    Cheers,

    Simon

  • December 18, 2007 at 8:00 am

    #761035

    Simon,

    I think the remote server is running Windows Server 2003 (Windows NT 5.0 (2195)). Both SQL Servers are enterprise edition.

    jez

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply