Post reply

Problems with partitioned Views : "Cannot start more transactions on this session."

  • December 18, 2008 at 5:21 am

    #132999

    Hi & Help !

    having some problems with partitioned views and I was after some wise words of wisdom etc ..

    Created a dpv on two seperate instances , two linked servers and added the following permission

    GRANT SELECT, INSERT, UPDATE, DELETE, VIEW DEFINITION ON SaleHistory TO reader

    If I run a select statement ,, all is good ... for updates, deletes and inserts I get

    "

    OLE DB provider "SQLNCLI" for linked server "SQL2005DBA\SQL20052ND" returned message "Cannot start more transactions on this session.".

    Msg 7395, Level 16, State 2, Line 1

    Unable to start a nested transaction for OLE DB provider "SQLNCLI" for linked server "SQL2005DBA\SQL20052ND". A nested transaction was required because the XACT_ABORT option was set to OFF.

    "

    any ideas ?

    many thanks

    ~simon

  • December 19, 2008 at 3:30 am

    #915079

    http://www.sqlservercentral.com/Forums/Topic203537-5-1.aspx#bm204211

    http://social.msdn.microsoft.com/forums/en-US/sqldataaccess/thread/535614e6-b553-4342-8980-b1a1d9a8b305/

    Sorry I could not figure out how to post a working hyperlink, may be my IE security.

    I've no direct experience with this, but have you tried setting XACT_ABORT on (on the connection doing the insert/update/delete)? These links don't say all that much but do suggest this might be required.

  • December 19, 2008 at 3:46 am

    #915089

    thank you Mike.. much appreiciated 🙂

    simon

Viewing 3 posts - 1 through 2 (of 2 total)

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