Professional Web Applications Themes

Cannot start more transactions in this session - Microsoft SQL / MS SQL Server

I think you are trying to insert data into the table on the central server that violates a constraint (Primary Key/Foreign key/Unique/Check). This isn't caught by your error handler because the error originates outside the SQL Server from which this code is run and SQL Server will always terminate the batch/stored procedure that is running (and rollback the open transactions) when it gets an error back from an external source. This may sound like an inconvenience, but it actually ensures that external errors can't bring down your SQL Server. The best thing to find out what is going wrong is ...

  1. #1

    Default Re: Cannot start more transactions in this session

    I think you are trying to insert data into the table on the central server
    that violates a constraint (Primary Key/Foreign key/Unique/Check). This
    isn't caught by your error handler because the error originates outside the
    SQL Server from which this code is run and SQL Server will always terminate
    the batch/stored procedure that is running (and rollback the open
    transactions) when it gets an error back from an external source. This may
    sound like an inconvenience, but it actually ensures that external errors
    can't bring down your SQL Server. The best thing to find out what is going
    wrong is to copy the data from your MSDE machine to a different table on the
    central server and compare them there.

    You can do away with the complete error handling in this stored procedure
    IMO. As I explained earlier you can't handle errors on external connections,
    because the batch will be terminated before you reach the error handler. And
    the only errors that I know of that you can handle that can occur with a
    DELETE FROM <table> are raised by triggers and by non-cascading Foreign Keys
    referencing the table. As these are both part of your database design, you
    should be aware of them when you write your stored procedures.

    --
    Jacco Schalkwijk MCDBA, MCSD, MCSE
    Database Administrator
    Eurostop Ltd.


    "Alexander Risøy" <no> wrote in message
    news:VYRQa.13299$e.nsc.no... 
    (a 


    Jacco Guest

  2. #2

    Default Re: Cannot start more transactions in this session

    Hi,

    Removing the BEGIN/COMMIT/ROLLBACK TRANSACTION statements makes the
    procedure work.

    If I remove this transaction control will I be in danger of loosing any
    data? Say, if the INSERT fails and the DELETE completes. Or do I understand
    you correctly that the whole procedure will fail if the INSERT INTO
    OPENDATASOURCE fails?

    -------------------------------------
    The proper error no and msg
    -------------------------------------
    1> EXEC export_transport
    2> GO
    Msg 7392, Level 16, State 2, Server PC2002-03, Procedure export_transport,
    Line
    16
    Could not start a transaction for OLE DB provider 'SQLOLEDB'.
    [OLE/DB provider returned message: Only one transaction can be active on
    this session.]
    -------------------------------------

    Related MS KB article: KB306649.

    --
    Best regards,

    Alexander Risøy
    IT Team
    Nebb Engineering


    "Jacco Schalkwijk" <co.uk> skrev i melding
    news:phx.gbl... 
    the 
    terminate 
    the 
    connections, 
    And 
    Keys 
    > (a [/ref]
    (will [/ref]
    the [/ref]
    result. [/ref]
    failure. 
    >
    >[/ref]


    Alexander Guest

  3. #3

    Default Re: Cannot start more transactions in this session

    Yep, the whole procedure will be aborted on any error with the linked
    server. A simple example:

    ---
    UPDATE OPENDATASOURCE('SQLOLEDB','Data Source=<your server>;User
    ID=<username>;Password=<password>').northwind.dbo. employees
    SET birthdate = '19481208' WHERE employeeid = 1

    Select 'Correct date'

    UPDATE OPENDATASOURCE('SQLOLEDB','Data Source=<your server>;User
    ID=<username>;Password=<password>').northwind.dbo. employees
    SET birthdate = '20481208' WHERE employeeid = 1

    select 'incorrect date' -- Check constraint (Birthdate < GETDATE())
    ---

    you never get to see the 'incorrect date' message.

    You can use BEGIN DISTRIBUTED TRANSACTION if you want to roll everything in
    one transaction, see for more information Books online.

    --
    Jacco Schalkwijk MCDBA, MCSD, MCSE
    Database Administrator
    Eurostop Ltd.


    "Alexander Risøy" <no> wrote in message
    news:W1TQa.13313$e.nsc.no... 
    understand [/ref]
    server 
    > the 
    > terminate [/ref]
    may [/ref]
    errors [/ref]
    going 
    > the [/ref]
    procedure 
    > connections, 
    > And 
    > Keys [/ref]
    you [/ref][/ref]
    table [/ref]
    > (will [/ref]
    > the [/ref]
    > result. [/ref][/ref]
    this [/ref]
    > failure. 
    > >
    > >[/ref]
    >
    >[/ref]


    Jacco Guest

Similar Threads

  1. Replies: 17
    Last Post: September 12th, 11:42 PM
  2. Two session start tags
    By barbedwire103 in forum Dreamweaver AppDev
    Replies: 1
    Last Post: April 2nd, 07:38 PM
  3. session start
    By in forum PHP Development
    Replies: 2
    Last Post: October 23rd, 05:03 PM
  4. Replies: 9
    Last Post: September 11th, 04:29 AM
  5. note 33912 added to function.session-start
    By asddsa@rack1.php.net in forum PHP Notes
    Replies: 1
    Last Post: July 10th, 07:22 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139