Using with (updlock) with DW retrieve and in line SQL statements work different (SQL Server)

I am trying to achieve a lock method to edit records (rows) and notify users which user has the record locked, using SQL Server. For that it would requiere to:

Use WITH (UPDLOCK) in the select of the datawindow
Have the id of the row with the non documented feature

select %%lockres%% ,* from <table>

Use a select like this:

select l.*
from sys.dm_tran_locks as l
where l.resource_type in ('KEY', 'RID')

I would need also the name of the user in the Host or other field en in dbtrans, thats no problem

Also the dbid and objectid from SQL, thats no problem either.

A time out to ROLLBACK an release in case of huge delay from the user.

But I was trying to do it without changing or copying a lot of datawindows so I am dynamically modifying the SQL select in my ancestor dw object, with dw.SetSQLselect, but seems not to work for a DW.

I have tried:

check the correct change in retrievestart and retrieveend events.
checking the dbtrace.

It seems that the build it method dw.retrieve ignores the WITH clause. As it does not executres the select with lock, as per the next trace results.

(09821818): SELECT co_asientos.IdAsiento , co_asientos.compania , co_asientos.IdTipoDocumento , co_asientos.IdOficina , co_asientos.Documento , left(ge_tipodocumento.nombre,5)+' '+co_asientos.Documento as display FROM co_asientos , ge_tipodocumento WHERE ( co_asientos.IdTipoDocumento = ge_tipodocumento.IdTipoDocumento ) and ( ( co_asientos.IdAsiento IS NULL ) ) (DBI_PREPARE) (0.292 MS / 592.450 MS)

But it do works with in line sql statements like, using the same transaction object:+

Trace when stoped at event retrieve start:

(08C00C10): PREPARE:
(08C00C10): SELECT co_asientos.IdAsiento , co_asientos.compania , co_asientos.IdTipoDocumento , co_asientos.Documento , co_asientos.fecha ,

.....

co_asientos.idModulo , co_asientos.IdOficina , space(100) as ColChange FROM CO_ASIENTOS WITH (UPDLOCK) WHERE ((co_asientos.IdAsiento = 72208)) (DBI_PREPARE) (0.081 MS / 213.591 MS)

BUT trace at retrieve end:

(08C00C10): PREPARE:
(08C00C10): SELECT co_asientos.IdAsiento , co_asientos.compania , co_asientos.IdTipoDocumento , co_asientos.Documento , co_asientos.fecha ,

.........

FROM co_asientos WHERE ((co_asientos.IdAsiento = 72208)) (DBI_PREPARE) (0.437 MS / 214.638 MS) (THE UPDLOCK DISAPEARS)
(08C00C10): DESCRIBE:(DBI_DESCRIBE) (0.040 MS / 214.678 MS)

THEN THE RECORD IS NOT LOCKED

select idasiento INTO :IDASIENTO from co_asientos with (UPdLOCK) where idasiento=72208;

IN the rade:

(03C12420): select idasiento from co_asientos with ( UPdLOCK ) where idasiento =72208 (DBI_PREPARE) (0.275 MS / 233.661 MS)
(03C12420): GET AFFECTED ROWS:(DBI_GET_AFFECTED_ROWS) (0.000 MS / 233.661 MS)
(03C12420): ^ 1 Rows Affected

THE RECORD NOW IS LOCKED

By this result, I would guess is not the driver, I am using PB126 with SNC 11.

Am I missing something? Is this a known issue?

Best Regards

Alfredo

Resolved Using with (updlock) with DW retrieve and in line SQL statements work different (SQL Server)