Openquery QUESTION

• sun_yt

  SSC Enthusiast

  Points: 124

  More actions

  November 14, 2007 at 6:34 pm

  #179341

  I executed OPENQUERY method in SQLSERVER2005 to change isolation level of mysql database.

  SELECT * FROM OPENQUERY(YOURDBLINK,

  'SET Transaction Isolation Level READ UNCOMMITTED')

  prompt:

  Msg 7357, Level 16, State 2, Line 2

  Cannot process the object "SET Transaction Isolation Level READ UNCOMMITTED". The OLE DB provider "MSDASQL" for linked server "YOURDBLINK" indicates that either the object has no columns or the current user does not have permissions on that object.

  And executed

  SELECT * FROM OPENQUERY(YOURDBLINK,

  'SELECT @@tx_isolation;')

  result:

  @@tx_isolation

  ----------------

  REPEATABLE-READ

  As far as permissions were concerned,I loged ined MySql with the same username/password

  and executed 'SET Transaction Isolation Level READ UNCOMMITTED' directly,it worked.

  BTW, regardless SSIS.

  Thanks!

• sun_yt

  SSC Enthusiast

  Points: 124

  More actions

  November 14, 2007 at 10:54 pm

  #751379

  In brief, I need to change database isolation level of mysql by using SQLSERVER2005 stored procedure.