error 7390 when isolation level is set to serializable...

• srini_kris

  SSCommitted

  Points: 1738

  More actions

  August 5, 2004 at 2:52 pm

  #109368

  I am trying to replicate the execution of a stored procedure and hence am using isolation level of serializable as recommended by BOL for purge operations.

  This following code works fine when executed on the server ( logging in via terminal services) bu gives me an error when executed thru Query Analyzer.

  Really puzzled.

  ________________________________________________________

  SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

  GO

  begin transaction

  exec dbo.Procname @parm1 = 'ABC',

  @parm2 =  '13397'

  commit transaction

  go

  _________________________________________________________

  ***************************************************

  Server: Msg 7390, Level 16, State 1, Line 1

  The requested operation could not be performed because the OLE DB provider 'ADSDSOObject' does not support the required transaction interface.

  OLE DB error trace [OLE/DB Provider 'ADSDSOObject' IUnknown::QueryInterface returned 0x80004002].

  *****************************************************

  Any help will be appreciated

  thanks

  -srini

• Allen Cui-55137

  SSC Guru

  Points: 51650

  More actions

  August 5, 2004 at 8:59 pm

  #517933

  It sounds like you were trying to delete data in linked server. We need more information about your system environment and also post your stored procedure too.

• sscbm21

  Old Hand

  Points: 323

  More actions

  August 8, 2004 at 11:26 pm

  #518124

  It could be because of the Authentication. Try using the same Windows Logon as you were using in TS for the QueryAnalyzer.

  bm

   

  
  bm21

• Alamir

  SSCertifiable

  Points: 6085

  More actions

  August 17, 2004 at 4:22 am

  #519288

  by the way it is not recomended that you use serializable isolation level because it hangs all your other recourses .. because all operation is done in serial (not parallel when needed)

  you may check for with (UPDLOCK) in Locks to tables you update in Begin of your code

  UPDLOCK: prevent updates and locks from other resources but make table avaiable to other readers

  you code use something like this

  declare

  @Code int

  set @Code= (SELECT MAX([Code]) from [Customers] with (UPDLOCK) ) +1

  I hope this help u

  
  Alamir Mohamed
  Alamir_mohamed@yahoo.com