INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Unlock sql 2000 table

Unlock sql 2000 table

(OP)
Is there a command to unlock an mssql table that is locked.  I've got one that keeps getting locked.  Figure its a bug somewhere in about 10,000 lines of code in many programs so need something to unlock it until I can find this needle in a hay stack field.

RE: Unlock sql 2000 table

This is not an MTS question, why did you post in the MTS forum?

Also, locking is handled slightly differently depending on which version of MSSQL you're running, and I don't see anywhere in your post where you have this.

Moreover, unlocking a locked table is not the solution, you need to fix the bug that's causing the lock.
 

RE: Unlock sql 2000 table

(OP)
The version is 2000.  I agree fixing is the right idea.  However, right now I have numerous calls to the table in a lot of webpages, stored procedures, and FoxPro programs and I haven't been able to identify the exact call that is doing the lock.  Some of the code is almost 5 years old others relatively new it all uses the table.  Thus I need a way to unlock the table other then kicking all the users out a few times a day.    

RE: Unlock sql 2000 table

NEVER forcefully or directly unlock a table, you can corrupt the entire database.  It sounds like you really don't understand MSSQL, therefore you shouldn't be tinkering with this.  sounds like you're junior-level, seek help from more senior-level staff, don't try to do this on your own.

RE: Unlock sql 2000 table

(OP)
And people say sql is better then dbf!  In 20 years I've never had these kind of problems with a dbf.  I understand that SQL 2000 is a bit weak/buggy, but the idea that you shoudn't force an Unlock is unbelievable.  I've run a 1000 users against a DBF with a lot less problems.   

RE: Unlock sql 2000 table

Why are you blaming MSSQL, saying it is weak and buggy?  It does what it's told to do (by your code), and is much more robust and stable than any dbf would ever be. Again you are approaching this from the wrong angle, you need to fix the bug in your code, not blame MSSQL.  If you want to post some of your code here I can see if I can help you.
 

RE: Unlock sql 2000 table

(OP)
Have found that Friday after I left work withy the table newly rebuilt.  Program Code changed in the area according to out in house SQL expert for all the areas I think were accessed by accounting the lock was back before I got home.  

I don't have the exact code with me but it is basically this
msg="the program used was printing"
Open.theconnection
Insert into ecommerce.dbo.event_log (etype,edate, prempl, ...) Values (msg,getdate(),'Nancy')  
close.theconnection

As I've said this has worked for years.  I do the same thing with a lot of other tables.  What is starting to worry me is that it will start happening with those too.  Most of them have much more business critical information.  

With this one I can drop and rebuild the table with most of the others I couldn't.  It has been necessary to drop and rebuild a few times.

I intially though I had a typo in some obscure place that was only rarely hit but now it seems to be afecting areas that are hit dozens of times a day and have been hit that way for years.   

RE: Unlock sql 2000 table

(OP)
Here is an example of the actual code.

WAIT CLEAR
STORE SPACE(1) TO lcconnstr
DO bcmlink WITH lcconnstr
oconnection=CREATEOBJECT("ADODB.Connection")
oconnection.connectiontimeout = 0
oconnection.commandtimeout = 0
oconnection.OPEN(lcconnstr)
STORE TIME() TO mtime2
msg="S. Tax RPT DS Started at "+mtime1+" ended at "+mtime2
*WAIT WINDOW (msg) NOWAIT
putit="insert into ecommerce.dbo.event_log "
putit=putit+" (etype, "
putit=putit+" edate, "
putit=putit+" order_ID, "
putit=putit+" prgid, "
putit=putit+" prempl) "
putit=putit+" values "
putit=putit+" ('"+msg+"', "
putit=putit+" getdate(), "
putit=putit+" 0, "
putit=putit+" 'HH.bcsltad', "
putit=putit+" 'HH "+m_p_prempl+"') "
oconnection.execute(putit)
oconnection.CLOSE

BCMLINK contains the connection string.

*
* BCMLINK
*
* STANDARD LINK TO SQLPROD
*
PARAMETERS LCCONNSTR
lcconnstr="DRIVER={SQL SERVER};SERVER=Godzilla;UID=vfp;PWD=QuietTime2003@#$;"  && for Godzilla the production database
RETURN LCCONNSTR

For security reasons I changed the server name, UID and PWD.  I also deleted the commented out path to the test server.

RE: Unlock sql 2000 table

I see several problems with this code, but the obvious error is using zero's for both of your timeout settings, zero means infinity, try using 20 for both.

Concatenating strings is a bad idea and will slow your code down, remember strings are immutable, use StringBuilder instead.

I don't see any block-level error handling here, especially with database connections you really need this so that if an error is raised you can affirmatively close the data connection, since leaving it open will cause unpredictable results.
 

RE: Unlock sql 2000 table

(OP)
Some more details.

Ran the code in Enterprise Manager and Query Analyzer it goes instantly.  Couldn't count to 1 before it's done.  Same in VFP or VB.  That is when it works.  If it is going to give me the lock up message it takes about a minute.  

The message is OLE IDispatch ... Insert Statement conflicted with Table Check constraint CK_event_log.  The conflict occurred in database 'ecommerce' table event_log ...

CK_ is the actual error.  The table name is appended by the error message.

Have checked for constraints.  Know of none.

STructure is

even (id) Int 4
etype varchar 100 allow nulls
edate datetime 8 allow nulls
order_id int 4 allow nulls
prempl varchar 50 allow nulls
prdid varchar 100 allow nulls
userid int 4 allow nulls
description varchar 125 allow nulls

No triggers, no dependancies, only the VFP programs write to it, now.  And no known constraints on anything.

The table is designed to let me know who is using what for the most part that is it runs at the start of a program and at the end.  Once in a while I use it to track who did what when, but that is rare.  In that case it is run in the main code of a program.  Most of the time I mean by main code a project made up of 40 plus reports, screens and prgs.

I close the connection because the code could run first at 9 am and next at 5 pm with only calls to DBF's used in between.  The vast majority of the times the program will be open for an hour or 2 with calls to various other sql tables and dbf going on through out.

The projects are designed to merge SBT Pro 6.0 with an in house ecommerce web system with an SQL back end.  Pro 6.0 use DBF's.  

RE: Unlock sql 2000 table

Sounds exactly like an SQL locking issue, even more of a reason to NOT use zero's for your timeout settings, because if the program is locking the table it might NEVER be released. Again without any block-level error handling, if an error is raised you can affirmatively close the data connection, leaving it open could cause the locking issue you describe. Start with these 2 fixes and come back and post the results.

RE: Unlock sql 2000 table

(OP)
Current status.  As with the last time this happened it went away on it's own.  That it tends to happen more in the first half of the month may be significant.

We've been eliminating the Timeout=0.  We are also using enterprise manager less.  We found a place where a different table was not explicitly closed when the program terminated.  That program was run by a different program.

RE: Unlock sql 2000 table

(OP)
Still have the problem.

RE: Unlock sql 2000 table

(OP)
Today it got a little stranger.  The error message came back as Table Check constraint 'N'OurSQLPassword_event_log'  The conflict occurred in Event_log.  Later we had an error saying the table couldn't be found via Query Analyzer.  We went to Enterprise Manager found and browsed it.

The table exists on a server called SeneGence 6.  There is a directory on this for SBT 6.0 called PRO60.  It contains both a VFP6 and a VFP9 runtime.  My in house programs and SBT 6 are run from it.  SBT 6 is a DBF only program that does not access the Event_log.  Once I got everyone out of SBT the event_log and A VB program I was looking at but not running that resides in a different directory on the same server everything started working again.

RE: Unlock sql 2000 table

Did you implement the 2 fixes I described:

Do NOT use zero's for your timeout settings, because if the program is locking the table it might NEVER be released.

Again without any block-level error handling, if an error is raised you can affirmatively close the data connection, leaving it open could cause the locking issue you describe.
 

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close