SQL table keeps locking

[SBTBILL]

I have and mssql table that keeps locking. i access it with ADO. For years this worked fine. Now suddenly it is locking worst and worst every day. Anybody ran into this before? This isn't a particularly important table but I'm worried it will migrate to other tables.

 

[Mike Lewis]

This question doesn't seem to be related to VFP. Have you tried posting it in a forum that deals with the specific database or back end that you are using?

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[Olaf Doschke]

Without any code you provide we need to guess what you mean by locks, here's a general article on SQL Server Locks, maybe that helps:

http://www.sql-server-performance.com/tips/reducing_locks_p1.aspx

If you rather have Transactions taking longer than this may help you. If you rather have "real" errors, eg Tableupdate() returning .F. because of update conflicts, then that may not have to do with SQL table locks failing.

Are you using manual transactions? When do you start them, when do you commit or rollback? You may cause more locks than needed in the first place.

Bye, Olaf.

 

[JRB-Bldr]

In addition to what the others have said above, when you say...
"For years this worked fine. Now suddenly it is locking worst and worst every day."

What changed?

If you haven't changed your VFP application, then what else changed?

Good Luck,
JRB-Bldr

 

[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.

 

[Olaf Doschke]

Opening and closing a connection for every single insert sql isn't very performant. This has few to nothing to do with sql server table, page or row locks.

How large is the database, what are resources on the server, how is the database configured in regard to expansion? Default rather is a growth of 10%, if that is configured to 1 or a few MB only each time, this could slow down database growth.

Last not least connection pooling would help keeping that way of connecting/disconnecting fast, if that is turned off recently, the times to connect will rise and logging is slowed down.

Bye, Olaf.

 

[JRB-Bldr]

You still have not told us what has changed to make it transition from working in the past and now locking up.

Regardless, have you executed the same code directly in the SQL Server and evaluated its stand-alone execution time?

If it should now be taking a good while to execute directly in the SQL Server and/or it should lock up, then there is something going on there and not in your VFP code.

Also, while we are grasping for straws and while I would not expect to see any noticable difference, have you tried executing the code using a SQL String Connection and SQL Pass Through -- not involving ADO?

putit = "INSERT INTO ecommerce.dbo.event_log ";
   + " (etype, ";
   + " edate, ";
   + " order_ID, ";
   + " prgid, ";
   + " prempl) ";
   + " values ";
   + " ('"+msg+"', ";
   + " getdate(), ";
   + " 0, ";
   + " 'HH.bcsltad', ";
   + " 'HH "+m_p_prempl+"') "

nHandle = SQLStringConnect("Driver={SQL Server};Server=MySQLSrvr;Database=MySQLDB;Uid=sa;Pwd=secret;") 
nRet = SQLEXEC(nHandle,putit)
IF nRet = 1
   < successful - do whatever >
ENDIF
=SQLDisconnect(nHandle)

Good Luck,
JRB-Bldr

 

[SBTBILL]

No on using SQL pass through commands used ADO first and now I move things back and forth with VB. Some the early problems showed up in stored procedures called by VB.

If I knew what had changed that is where I would look first. I have new code but these areas are mostly old.

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.

 

[JRB-Bldr]

I just did a Google search for "SQL Server" "Table Check constraint"

It returned a number of 'hits' which might be of value to you in resolving your issues. From the 'hits' it appears as though your issues might be in the SQL Server and not in how VFP 'talks' to it.

Good Luck,
JRB-Bldr

 

[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.

 

[Olaf Doschke]

Is this a multi cpu server? Even if, another process aside of SQL Server might just have hogged the cpu. VFP programs tend to do so in lenghty SQLs or other tight loops with no DOEVENTS once in a while.

An idea would be monitoring resources and see if peak performance coincides with these errors you get.

Even though timeouts would rather give timeout errors than reporting nonexistance of an existing table, you may get more hints what is failing.

Bye, Olaf.

 

[dbMark]

If you need to monitor CPU usage and other low level processes and activities, try free Process Explorer from SysInternals, now a part of Microsoft's web site. Hopefully the next time you have a slowdown you can use it to see what is consuming the most CPU resources, etc. Here the site of a tutorial:

http://www.msteched.com/2010/NorthAmerica/WCL314