database entry for what user has a sales order open?

Question

Is there any record made to the database when a user has a sales order open? IE, Sage 100c SQL knows when a user has an order open, since another user cannot also open the same order... but is that information written to the DB or is it only a flag within the application server? Is it accessible?

wishingforsql · Answer

For SQL, the data resides here: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/153a4761-f9aa-4e81-bcde-bf7c5086ff20/how-to-know-which-object-the-key-lock-is-put-on?forum=sqldatabaseengine 
 Sample query to find out if a sales order is locked. I'll be rolling this in to an sp once I have some other pieces built, but this will definitely serve my purposes:

SELECT L . request_session_id AS SPID, 
 DB_NAME ( L . resource_database_id ) AS DatabaseName, 
 O . Name AS LockedObjectName, 
 P . object_id AS LockedObjectId, 
 L . resource_type AS LockedResource, 
 L . request_mode AS LockType, 
 ST . text AS SqlStatementText, 
 ES . login_name AS LoginName, 
 ES . host_name AS HostName, 
 TST . is_user_transaction as IsUserTransaction, 
 AT . name as TransactionName, 
 CN . auth_scheme as AuthenticationMethod 
 FROM sys . dm_tran_locks L 
 JOIN sys . partitions P ON P . hobt_id = L . resource_associated_entity_id 
 JOIN sys . objects O ON O . object_id = P . object_id 
 JOIN sys . dm_exec_sessions ES ON ES . session_id = L . request_session_id 
 JOIN sys . dm_tran_session_transactions TST ON ES . session_id = TST . session_id 
 JOIN sys . dm_tran_active_transactions AT ON TST . transaction_id = AT .transaction_id 
 JOIN sys . dm_exec_connections CN ON CN . session_id = ES . session_id 
 CROSS APPLY sys . dm_exec_sql_text ( CN . most_recent_sql_handle ) AS ST 
 
 WHERE resource_database_id = db_id () 
 AND ST . text LIKE '% your order here %' 
 AND O . Name = 'SO_SalesOrderHeader' 
 
 ORDER BY L . request_session_id

Sage 100

database entry for what user has a sales order open?

Top Replies

COMMUNITY

COMPANY

PARTNERS

SUPPORT & TRAINING