locked
SQL Linked Server (Access) vs. BizTalk stored procedure RRS feed

  • Question

  • Hello,
    I want to update data into a Access .mdb instead of currently attached SQL table (BizTalk SQL->SQL).

    I've created an linked server and adjusted the stored procedures for update pointing to the linked server instead of the SQL Tables. When I execute the stored procedure from SQL Server 2005 manually everythink works fine, but when I run my BizTalk application I always get the message:

    The requested operation could not be performed because OLE DB provider "<OLE Provider>" for linked server "<MyLinkedServer>" does not support the required transaction interface.

    OLE Provider tested with:
    Microsoft.Jet.OLEDB.4.0
    Microsoft.ACE.OLEDB.12.0

    I don't understand why this doesn't work, since the stored procedure executes perfectly and returns the "0" for success. Isn't this executing of the sproc independent/outside of BizTalk?

    thanks for your help in advance!
    Mathias
    Friday, March 6, 2009 2:17 PM

Answers

All replies

  •  Hi,
    The Biztalk SQL adapter executes your stored procedure under Serialized transaction level .
    So your stored procediure  wants to update your data under the same transaction , and it's probably  not supported by the provider you're using

    If you want to test under the same transaction as Biztalk you could do the following in the query analyzer
     
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;  
    GO  
    BEGIN TRANSACTION-- or BEGIN DISTRIBUTED TRANSACTION (not sure here)  
    GO  
    @rc = execute ....  
    GO  
    COMMIT TRANSACTION;  
    GO 

    Friday, March 6, 2009 3:53 PM
  • Thanks for your answer,
    with
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE 
    I indeed get an error message from the OLE-DB Provider that neither the isolationlevel nor its strengthening could be supported. (In query analyser when i try to alter my stored procedure.)

    Does that mean there is no way with BizTalk or would it be possible with some adaption (using scope)? Is it possible to force BizTalk SQL adapter not to execute under serialized transaction level?

    besides tips for another possibility for easy change from sql to mdb would be very helpful.

    Thanks a lot!
    Friday, March 6, 2009 4:39 PM
  • You could try to lower the transaction level in the stored procedure, but i'm not sure this is going to help.
    see here : http://msdn.microsoft.com/en-us/library/ms173763(SQL.90).aspx

    Otherwise, you can make this a 2 step process: update the data in SQL server then use an SSIS package to push the changes to your access database
    • Marked as answer by MathiasSch. _ Monday, March 9, 2009 9:23 AM
    Friday, March 6, 2009 5:02 PM
  • Hi Mathias,
    Can you say what solved your problem :
    - Lowering the transaction level
    - SSIS package


    Tanks .
    Monday, March 9, 2009 9:49 AM
  • Hello,
    the SSIS package did it (as principle).

    Well, my first tests with some tables and manually starting the package succeeded. But now with some more tables I get errors in the package execution log where I'm not sure if its a data/table structure problem or something else.
    (OLE-DB error 0x80004005 e.g. errors saying things like "The identified component returned an error from the ProcessInput-Method.")

    I'm still investigating.
    best regards
    Mathias
    Monday, March 9, 2009 10:01 AM