Another notification problem

[LHSoft]

Hello,

in this never-ending-story I do have a new problem with notifications I do not understand.
On form1 I use notifications with some Tables, on Form2 I do not.
Recently I have added a small table on Form1 to use notifications.
This table can be modified on Form2, changes should be notified to all users with opened form1.
This works unless I do not insert or delete a row from the table.

So here is the log,
the table being in notification on form1 is TbKonLP,
TbKonGR2 is not in notification but in master-detail to TbKonLP

• 17:16:23 624: Flag: tfQExecute, Object: MSAccess/TMSTable: TMSTable
  Name: TBStmLie
  OWNER: Form2
  SELECT * FROM MoStmLie
  -----------------------------------------------------------------------------------------------------------
  17:16:23 659: Flag: tfQExecute, Object: MSAccess/TMSTable: TMSTable
  Name: TbKonLP
  OWNER: Form2
  SELECT * FROM MoKonLP
  WHERE Lieferantennummer = :Lieferantennummer
  :Lieferantennummer(Integer,IN)=103
  -----------------------------------------------------------------------------------------------------------
  17:16:26 085: Flag: tfQExecute, Object: MSAccess/TMSTable: TMSTable
  Name: TbKonLP
  OWNER: Form2
  DELETE FROM MoKonLP
  WHERE
  Lieferantennummer = ? AND Raster = ?
  
  :Old_Lieferantennummer(Integer,IN)=103
  :Old_Raster(WideString[5],IN)='1: 44'
  -----------------------------------------------------------------------------------------------------------
  17:16:26 087: Flag: tfQExecute, Object: MSAccess/TMSTable: TMSTable
  Name: TbKonGR2
  OWNER: Form2
  SELECT * FROM MoKonGR
  WHERE Bezeichnung = :Raster
  :Raster(WideString[0],IN)=<NULL>
  -----------------------------------------------------------------------------------------------------------
  17:16:26 088: Flag: tfQExecute, Object: MSAccess/TMSQuery: TMSQuery
  Name:
  WAITFOR (RECEIVE message_body, status, queuing_order, conversation_group_id, conversation_handle, message_sequence_number, service_contract_name, service_contract_id, message_type_name, message_type_id, validation FROM SDAC_NS_90_QUEUE), TIMEOUT 2147483647
  -----------------------------------------------------------------------------------------------------------
  17:16:26 177: Flag: tfQExecute, Object: MSAccess/TMSTable: TMSTable
  Name: TbKonLP
  OWNER: Form1
  select Lieferantennummer,Raster,Gr01,Gr02,Gr03,Gr04,Gr05,Gr06,Gr07,Gr08,Gr09,Gr10,Gr11,Gr12,Gr13,Gr14,Gr15,Gr16 from dbo.MoKonLP
  -----------------------------------------------------------------------------------------------------------
  17:16:26: Exception:
  Statement does not support notifications
  
  EDatabaseError
  EDatabaseError
  -----------------------------------------------------------------------------------------------------------
  17:16:27 691: Flag: tfQExecute, Object: MSAccess/TMSQuery: TMSQuery
  Name:
  WAITFOR (RECEIVE message_body, status, queuing_order, conversation_group_id, conversation_handle, message_sequence_number, service_contract_name, service_contract_id, message_type_name, message_type_id, validation FROM SDAC_NS_90_QUEUE), TIMEOUT 2147483647
  -----------------------------------------------------------------------------------------------------------
  

Again my question:
do you see anything problematic?

best regards
Hans

[LHSoft]

Hello,

I have found the problem: the table in notification was set to read only.
Can you tell me if this behavior is correct?
It looks suspicious cause this problem only occurs if in an table- or datasource event any other table is opened, even if this table is not in any notification.
So this works (FormLaqgBes/TbKonGR is in notification, others are not):

• 22:50:52 352: Flag: tfQExecute, Object: MSAccess/TMSTable: TMSTable
  Name: TbKonGR
  OWNER: FormKoDat
  UPDATE MoKonGR
  SET
  Gr11 = ?
  WHERE
  Bezeichnung = ?
  
  :Gr11(WideString[1],IN)='w'
  :Old_Bezeichnung(WideString[7],IN)='10: 0-5'
  -----------------------------------------------------------------------------------------------------------
  22:50:52 355: Flag: tfQExecute, Object: MSAccess/TMSQuery: TMSQuery
  Name:
  WAITFOR (RECEIVE message_body, status, queuing_order, conversation_group_id, conversation_handle, message_sequence_number, service_contract_name, service_contract_id, message_type_name, message_type_id, validation FROM SDAC_NS_51_QUEUE), TIMEOUT 2147483647
  -----------------------------------------------------------------------------------------------------------
  22:50:52 427: Flag: tfQExecute, Object: MSAccess/TMSTable: TMSTable
  Name: TbKonGR
  OWNER: FormLagBes <<-- notification
  select Bezeichnung,Gr01,Gr02,Gr03,Gr04,Gr05,Gr06,Gr07,Gr08,Gr09,Gr10,Gr11,Gr12,Gr13,Gr14,Gr15,Gr16 from dbo.MoKonGR
  -----------------------------------------------------------------------------------------------------------

but this does not work:

• 22:47:40 479: Flag: tfQExecute, Object: MSAccess/TMSTable: TMSTable
  Name: TbKonGR
  OWNER: FormKoDat
  UPDATE MoKonGR
  SET
  Gr11 = ?
  WHERE
  Bezeichnung = ?
  
  :Gr11(WideString[1],IN)='w'
  :Old_Bezeichnung(WideString[7],IN)='10: 0-5'
  -----------------------------------------------------------------------------------------------------------
  22:47:40 482: Flag: tfQExecute, Object: MSAccess/TMSQuery: TMSQuery
  Name:
  WAITFOR (RECEIVE message_body, status, queuing_order, conversation_group_id, conversation_handle, message_sequence_number, service_contract_name, service_contract_id, message_type_name, message_type_id, validation FROM SDAC_NS_51_QUEUE), TIMEOUT 2147483647
  -----------------------------------------------------------------------------------------------------------
  22:47:40 508: Flag: tfQExecute, Object: MSAccess/TMSQuery: TMSQuery
  Name:
  select SUM(Menge01), SUM(Menge02), SUM(Menge03), SUM(Menge04), SUM(Menge05), SUM(Menge06), SUM(Menge07), SUM(Menge08), SUM(Menge09), SUM(Menge10), SUM(Menge11), SUM(Menge12), SUM(Menge13), SUM(Menge14), SUM(Menge15), SUM(Menge16), SUM(SummeMenge) from MoLagMgB WHERE Raster='10: 0-5'
  -----------------------------------------------------------------------------------------------------------
  22:47:40 517: Flag: tfObjDestroy, Object: MSAccess/TMSQuery: TMSQuery
  Name:
  Object destroyed: TMSQuery ($041380F0)
  -----------------------------------------------------------------------------------------------------------
  22:47:40 568: Flag: tfQExecute, Object: MSAccess/TMSTable: TMSTable
  Name: TbKonGR
  OWNER: FormLagBes <<-- notification
  select Bezeichnung,Gr01,Gr02,Gr03,Gr04,Gr05,Gr06,Gr07,Gr08,Gr09,Gr10,Gr11,Gr12,Gr13,Gr14,Gr15,Gr16 from dbo.MoKonGR
  -----------------------------------------------------------------------------------------------------------
  22:47:40: Exception:
  Statement does not support notifications
  
  EDatabaseError
  EDatabaseError
  -----------------------------------------------------------------------------------------------------------
  22:47:42 942: Flag: tfQExecute, Object: MSAccess/TMSQuery: TMSQuery
  Name:
  WAITFOR (RECEIVE message_body, status, queuing_order, conversation_group_id, conversation_handle, message_sequence_number, service_contract_name, service_contract_id, message_type_name, message_type_id, validation FROM SDAC_NS_51_QUEUE), TIMEOUT 2147483647
  -----------------------------------------------------------------------------------------------------------

This listings are the same beside one query to another table which is not involved in notifications.


I have just run into another problem:
If setting a table to FetchAll=False and this table is not in notification and have this table open, all notifications on other forms will fail:

• 23:19:06 405: Flag: tfTransact, Object: MSAccess/TCustomMSTransaction: TCustomMSTransaction
  Name:
  OWNER: SQLMain <<-- SQLMain: TMSConnection
  Start:
  -----------------------------------------------------------------------------------------------------------
  23:19:06 406: Flag: tfError, Object: OLEDBAccess/EMSError: Eine neue Transaktion ist unzulässig, weil in dieser Sitzung andere Threads ausgeführt werden.
  Error: Eine neue Transaktion ist unzulässig, weil in dieser Sitzung andere Threads ausgeführt werden.
  -----------------------------------------------------------------------------------------------------------
  23:19:06: Exception:
  Eine neue Transaktion ist unzulässig, weil in dieser Sitzung andere Threads ausgeführt werden.
  
  EMSError
  TDBAdvGlowButton
  -----------------------------------------------------------------------------------------------------------

Error translation: a new transaction is not allowed cause there are other threads in this session.
SQLMain is the connection to the Database

Can you tell me something about this second error?
It's very surprising for me.

best regards
Hans

[azyk]

In case of using Pessimistic Mode, on calling the TMSQuery.Edit method SDAC itself opens a transaction. If you open another dataset for notification after that not having closed the transaction, SQL Server will generate the error message you have specified. This is the behavior of SQL Server, which we cannot affect.