SQL Server transaction question

[MJFShark]

Hi All!

I'm doing some testing with the 7.3 beta and SqlServer. I'm using the oledb driver as from my research it appears to be the correct one to use for recent SQL Server releases. I haven't been able to get transactions to work properly with it and have had to fall back to always using autocommit. The other database protocols seem to work well with going with the default ReadCommitted isolation level (or changing it for MySQL), but I must be misunderstanding how transactions work in oledb? I'm used to:

Set TransactionIsolationLevel to ReadCommitted
Connect
.. do work ..
Commit/Rollback

I've tried adding StartTransaction when using OLEDB/SQL Server, but it doesn't seem to work (if it's useful I can give the errors and what I'm trying.) The error I see when trying to run a query after connecting after setting ReadCommited is:

Project raised exception class EZSQLException with message 'SQL Error: OLEDB Error
Error message: Cannot create new connection because in manual or distributed transaction mode.
Source: Microsoft OLE DB Driver for SQL Server
Code: -2147467259 SQL: IDBSchemaRowset.GetRowset'.

Any thoughts or suggestions appreciated!

-Mark

[marsupilami]

Hello Mark,

just a short and quick reply. Could you try to see what happens if you call FetchAll after Open?

Best regards,

Jan

[MJFShark]

The error occurs on Query.Open, so it doesn't appear to be affected by using FetchAll instead of looping. Here's a test console app that shows the error (at least for me.) Would love to hear any thoughts, thanks!

Code: Select all

program ZeosSQLServerTrans;

{$APPTYPE CONSOLE}

{$R *.res}

uses
  System.SysUtils,
  ZDbcIntfs, ZConnection, ZDataset;

begin
  var ZConn: TZConnection;
  var ZQuery: TZQuery;
  var RecCount := 0;

  try
    ZConn := TZConnection.Create(nil);
    ZConn.Protocol := 'oledb';
    ZConn.Database := 'Provider=MSOLEDBSQL;Server=localhost\SQLEXPRESS;Database=master;Trusted_Connection=yes;';
    ZConn.AutoCommit := False;
    ZConn.TransactIsolationLevel := tiReadCommitted;
    ZConn.Connect;

    ZQuery := TZQuery.Create(nil);
    ZQuery.Connection := ZConn;
    ZQuery.SQL.Text := 'SELECT * FROM INFORMATION_SCHEMA.TABLES';
    ZQuery.Open;
    // Error occurs in line above.
    while not ZQuery.Eof do
    begin
      inc(RecCount);
      ZQuery.Next;
    end;

    Writeln('Records: ' + RecCount.ToString);
    Writeln('Done');

    ZConn.Disconnect;
    ZQuery.Free;
    ZConn.Free;

  except
    on E: Exception do
      Writeln(E.ClassName, ': ', E.Message);
  end;

  Write('Press Enter to quit...');
  ReadLn;

end.

[EgonHugeist]

Errm, you're dealing with a TabularStreamed-Server. I miss the OldDb:"MarsConn=Yes"/ODBC:"MARS_Connection=yes" parameter in your connection string. Please add the parameter, retest and share your findings.

[MJFShark]

Aha! Michael, thank you for that! Adding "MARS Connection=True" to my database string works perfectly! I've very glad I asked here before making a bug report. I'm no expert on the OLEDB stuff and appreciate the help!

-Mark