MsSql + Application user role
lazarus / 16-Databases

----------------------------------------------------------------------
rnervi · 2025-10-05 13:43

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:

QuoteGeneric SQL error. Detail: Transaction not set
oh gosh, then I set transaction and error changes to:

QuoteMsSql : 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 · 2025-10-06 12:13

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 · 2025-10-06 15:33

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 · 2025-10-06 19:21

MSSQLConnection1.executedirect
needs transaction
and with transaction

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
MSSQLConnection1 : Error 20018 : General SQL Server error: Check
messages from the SQL ServerThe procedure 'sys.sp_setapprole' cannot
be executed within a transaction. 
 :'(

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

----------------------------------------------------------------------
JollyHong · 2026-07-25 11:14

Have you tried creating a tiny test project that only reproduces the
problem? I’ve found that stripping everything back often makes the
real cause much easier to spot, and it also gives others something
they can compile and test quickly. It can save a lot of back-and-
forth.

----------------------------------------------------------------------
rnervi · 2026-07-25 11:39

not yet sorry busy a lot
for now seems my way is working, will send here steps I used to make
it works and ...