I figure this is a long shot, but I would try anyway.
has anyone seen this error before?
<TEXT FOR THOSE WHO CANT SEE THE IMAGE: [“com.microsoft.sqlserver.jdbc.SQLServerException: The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.”,“com.microsoft.sqlserver.jdbc.SQLServerException: The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.”]>
It comes up whenever I submit an Access Request for a user.
The Access Request is for several Entitlements on a JDBC Source.
The JDBC Source has a -Create Rule- that calls several SPROCS when it is triggered.
My database admin tells me the SPROCS are fine, but I’m having her double check if she missed something. Meanwhile, I’m troubleshooting on my side to see if my rule is incorrect. It seems to add the user to the database, but some of the entitlements aren’t added.
To determine if this is a code issue or stored proc issue, you can either execute the stored proc directly in the SQL server by hardcoding the user data as the arguments of the stored proc or you can execute the stored proc using a java JDBC program.
If the above suggestion works for you then there might be some issue with your JDBC rule. Could you please confirm if all the underlined columns are your entitlement attributes? Also, it will be helpful if you can share the schema defined for entitlements and code snippet for create account operation in your JDBC provisioning rule.
Thanks everyone for the replies! @ipobeidi we unfortunately aren’t using XACT_STATE() in our SQL queries. That would have come in handy for tracking transactions, but the way this was all written at my company LONG LONG AGO, they didn’t include that particular function AND we obviously can’t make any changes in flight without crashing the plane…
BUT!
Because our main SPROC was calling SOOOO many other SPROCS, my database admin rewrote her SPROC so that INSTEAD of calling the other SPROCS to build and add and all that, the main SPROC would do it all in it’s queries. No additional SPROC calls. No transactions to deal with.
This actually worked for us. We were able to get users built in our database through the JDBC connector. I’ll probably end up writing an entire JDBC Connector Blog Post series with how much I’ve learned about building in such a complex SQL environment like ours, but for now, I’ll give the solution to @Shanmukh , since testing all hardcoded variables in SQL proved to be unsuccessful as well and eventually led us to discovering the transaction errors when setting a column in a database that is dependent on another column.
