Can't Post InventoryTrx

[NEmacGuy]

I receive a message stating Quantities are being reconciled.
I have run the reconcile utility in inventory and this did not fix it.
Any Ideas ?
Thank you for your help!

 

[NEmacGuy]

I have cleared the issue but would love to know if there is a process in the system to clear it, and how it got created.
I ran a SQL Trace, then tried to post. SY00800 and Activity had records in the Dynamics DB. I cleared all except sa (I log in as sa), and this cleared the issue.

Note : this is a non production database as of the moment.

 

[jazgeek]

user probably got stranded in system whilst posting or entering an inventory transaction.

sometimes you actually get them stuck with a dexlock in the tempdb database. Then you have to boot everyone out, clear the table and then do a reboot of the sql server. Quite annoying.



-----------
and they wonder why they call it Great Pains!

jaz

 

[BigBurcie]

I clear locked records all the time and you don't have to clear all users out of the system (we have up to 85 users between 4am and 7pm local time). I just run the following query in SQL Query Analyzer to find locked records.

select A.session_id, A.row_id, table_path_name, B.sqlsvr_spid, C.USERID,*
from tempdb..dex_lock A
left join tempdb..dex_session B
on A.session_id=B.Session_id
left join dynamics..activity C
on C.sqlsesid=b.session_id
order by table_path_name, B.session_id

If a record has an abandoned lock, the USERID field will be null. I then delete the lock from the dex_session and dex_lock tables and we're back up and running. I can also confirm that I am clearing the correct lock because it lists the company, table, and Row_ID being locked as well. This makes it a 2 minute job to free users, without the MS method of asking everyone nicely to log out while I clear sessions.

I just thought I should add this as it happens far more than I would like and I'm pretty sure I'm not the only one that finds this.

 

[jazgeek]

bug still exists on purchasing transactions that force you to delete out of SY00800. start a purchasing transaction, go to smarlist and search for an open PO and double click it to open the PO. It kicks you out of your previously started PO and depending on your level of completion it may be a corrupt but certainly is locked.



-----------
and they wonder why they call it Great Pains!

jaz

 

[CurbE]

I too clear locked records all the time having all users exit out of the system (with 50+ concurrent users). Here's what I use:
delete from tempdb..DEX_SESSION where session_id not in (select SQLSESSID from DYNAMICS..ACTIVITY)
GO
delete from tempdb..DEX_LOCK where session_id not in (select SQLSESID from DYNAMICS..ACTIVITY)