MsSql + Application user role

[rnervi]

I'm trying to port from D7 to lazarus an application.
this application is loggin on a server (not editable, as is in production) and as 1st operation it has to fire the "application user role".
In laz with native db libraries MSSQLConn I'm experiencing what follows.

AUR MUST be execute off from a transaction. But in this case the error is:

Generic SQL error. Detail: Transaction not set

oh gosh, then I set transaction and error changes to:

MsSql : Error 20018 : General SQL Server error: Check messages from the SQL Server
The procedure 'sys.sp_setapprole' cannot be executed within a transaction.

how hell I can then fire the AUR without transaction if it NEED transaction BUT does not work if setted ???

note: tried with zeos lib, it allows to fire AUR with no transaction, but it gives headhaces l8er when accessing data....

Sounds like a infinite loop...

any suggestion some1 ?

[Zvoni]

You set/assign a Transaction-Object to your Connection, but you don't activate the transaction, and use ExecuteDirect of the Connection?
I seem to remember having a similar issue with SQlite, that you couldn't fire off Pragmas after the connection was established.
Similar Error-Message as yours

IIRC, i had to write a classhelper for the connection using a direct api-call

[rnervi]

ty for answer.
I guess I had tried with executedirect and same result.
I've then done a small "connect ony test" app and in 2 hours will check it again.
ty

[rnervi]

MSSQLConnection1.executedirect
needs transaction
and with transaction

Code: Pascal  [Select][+][-]

1. MSSQLConnection1 : Error 20018 :
2. General SQL Server error: Check messages from the SQL Server
3. The procedure 'sys.sp_setapprole' cannot be executed within a transaction.
4.  

 :'(

thus need to go back to zeus that allows AUR w/o transaction but - until now - troubles to get data from stored etc.
will continue to investigate