Unlock sql 2000 table

[SBTBILL]

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.

 

[ProgrammerSteve]

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.

 

[SBTBILL]

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.

 

[ProgrammerSteve]

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.

 

[SBTBILL]

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.

 

[ProgrammerSteve]

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.

 

[SBTBILL]

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.

 

[SBTBILL]

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.

 

[ProgrammerSteve]

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.

 

[SBTBILL]

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.

 

[ProgrammerSteve]

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.

 

[SBTBILL]

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.

 

[SBTBILL]

Still have the problem.

 

[SBTBILL]

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.

 

[ProgrammerSteve]

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.