Solved

Lock table overflow error on Production but not Development

Posted on 2007-12-05
7
1,265 Views
Last Modified: 2008-02-01
I have several SSIS jobs that import data from a Progress 9.1D database.  The last job I built runs fine with in Visual Studio 2005, where I designed it,
It also runs fine from my Developer version of SQL 2005, and it will run fine if I execute my package directly from my Production Integrated Services server, but I get
a table lock error when I try to run it from my production Job Agent. Here is the Error  
Executed as user: OGI\sqlAdmin. ...rsion 9.00.3042.00 for 32-bit  Copyright (C) Microsoft Corp 1984-2005. All rights reserved.    Started:  1:38:11 PM  Error: 2007-12-05 13:38:36.64     Code: 0xC0047062     Source: Data Flow Task Script Component 1 [1]     Description: System.Data.Odbc.OdbcException: ERROR [HY000] [MERANT][ODBC PROGRESS driver][PROGRESS]Lock table overflow, increase -L on server (915)     at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.HandleUserException(Exception e)     at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.PrimeOutput(Int32 outputs, Int32[] outputIDs, PipelineBuffer[] buffers)     at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostPrimeOutput(IDTSManagedComponentWrapper90 wrapper, Int32 outputs, Int32[] outputIDs, IDTSBuffer90[] buffers, IntPtr ppBufferWirePacket)  End Error  Error: 2007-12-05 13:38:36.64     Code: 0xC0047038     Source: Data Flow Task DTS.Pipeline     Description: SSIS Error Code DTS_E...  The package execution fa...  The step failed.
I think it's a bogus error because if Progress was the problem, it would give me the same error from my development box.  Both ODBC Drivers are set up identically.  The Production box runs in a Cluster environment.
Any help or insight would be greatly appreciated.
0
Comment
Question by:ChadMarsh
  • 4
  • 3
7 Comments
 
LVL 27

Expert Comment

by:ptjcb
ID: 20414262
Is your production box 32-bit or 64-bit?
0
 
LVL 2

Author Comment

by:ChadMarsh
ID: 20414268
32bit
0
 
LVL 27

Expert Comment

by:ptjcb
ID: 20414412
Same number of records in both tables?

It looks to me as if this is a Progress db issue.  [MERANT][ODBC PROGRESS driver][PROGRESS]Lock table overflow

I have never worked with Progress. Does it automatically scale locking? If enough records are being used, would it automatically escalate the lock to a table lock? If not, can you add a locking hint so that the database automatically locks the table during the transaction?

0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 2

Author Comment

by:ChadMarsh
ID: 20414581
Your lucky..Progress is awful.  My job actually brings the whole table over which is about 240,000 records and puts them into a temp table in SQL so I can actually work on them.  Believe it or not, it's faster to bring the whole table over then to try and add a filter.
I know that error looks like its on that side, but the job will run fine from my development machine.  The only way I can get it to run in Production is to log into Integration Services and manually execute the package. It runs fine that way.  
I also have another job that brings about 450,000 records with the same driver and it works also. Of course, this is a different table.
I not totally ruling out Progress just because it's so flakey, but it seems strange that I can run in those other places.
Thanks for the response.
0
 
LVL 27

Accepted Solution

by:
ptjcb earned 500 total points
ID: 20414862
I understand that it works fine on development. I just googled Progress 9.1D lock table overflow and there are several hits on the issue within Progress.

-L
# Lock
Table
Entries
8192
There should be no need to set this value higher than the
default. This is the number of record locks any given
process can aquire. NOTE: If you received a 4GL
STOP ERROR while in the application, review your
\epicor\Mfgsys80\db\Mfgsys.LG file. If you find the
error message Lock Table Overflow  Increase the L
you will be advised to increase this value in multiples of
32. Common value increases are: 128000, 256000,
512000, etc.

http://64.233.169.104/search?q=cache:P7txojhFkMMJ:uplinkinc.com/mvug/docs/V800_Progress_PerfTune_Guide.PDF+Progress+9.1D+lock+table+overflow&hl=en&ct=clnk&cd=2&gl=us&lr=lang_en|lang_fr


I'm also, now, very glad that I do not work in Progress.


0
 
LVL 2

Author Comment

by:ChadMarsh
ID: 20414998
Funny..Epicor is exactly what we're using. I checked the LG file and the error was there.
I've been on google all week and didn't come across that. Tunnel vision I guess...
Thanks for your help.
0
 
LVL 27

Expert Comment

by:ptjcb
ID: 20415025
I'm glad I could help.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

SQL Command Tool comes with APEX under SQL Workshop. It helps us to make changes on the database directly using a graphical user interface. This helps us writing any SQL/ PLSQL queries and execute it on the database and we can create any database ob…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now