Locked Table 4

[walkerjgt]

When trying to post a credit memo a user is getting the error "Opening procedure failed(OPEN-OUTPUT-OESJNWRK). I searched the customer portal and all I could find is that a table might be locked by another user.

How can I view what users are logged in and/or what tables are locked? I have SQL Enterprise Manager so can view the database directly.

 

[jaustill]

I can not take credit for this, I found it on this forum someplace. BTW if you use PHP on your IntraNet you can create a page to let everyone see who is locking what and not have to handle it yourself.

This is an MSSQL view:

SELECT TOP 100 PERCENT CAST(l.SessionID AS char(5)) AS [Session], CAST(m.loginame AS char(20)) AS login_name, CAST(m.hostname AS char(20))AS computer, CAST(l.DBName AS char(20)) AS db_name, CAST(l.TableName AS char(20)) AS table_name, l.RowID AS record, m.last_batch,m.net_library
FROM msllockdb.dbo.MACLOCKS l LEFT OUTER JOIN master.dbo.sysprocesses m ON l.SessionID = m.spid
ORDER BY m.loginame, l.DBName, l.TableName

 

[dgillz]

Jere,

This is way cool. What database do you run this on? msllockbd? or the data_xx database?

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"What version of URGENT!!! are you using?

 

[crystalreporting]

Don - I believe this query is Macola database independent i.e. it directly references the MSLLockDB and master databases within a SQL Server/instance and would show all locks for all databases within the one Server/instance.

Peter Shirley
Macola Consultant, PA and surrounding states.

http://www.srhconsulting.com

 

[jaustill]

Don,
It is a "view" under DATA on our MSSQL2k server. We only use that server for Macola. Under DATA on the MSSQL server right click View, select "New view", paste the code and save it as something like who_is_locking_what_in_macola

 

[walkerjgt]

Very helpful. Thank you!

 

[JayTheGeek]

Nicely Done!

Yes this is database independent. In my environment (two production databases) it correctly lists record locks from both companies.

Thanks!

 

[NEmacGuy]

Did you find your problem?

My first thought on Opening procedure failed messages is a control file, or setup file is missing or missing data. After that it usually is a locking issue.

 

[walkerjgt]

Someone walked away from their pc while inputing an RMA. Was able to see who it was, call them up, and give them the what for Everything unlocked as soon as she closed the screen.