• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1320
  • Last Modified:

Lock table overflow error on Production but not Development

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
ChadMarsh
Asked:
ChadMarsh
  • 4
  • 3
1 Solution
 
ptjcbCommented:
Is your production box 32-bit or 64-bit?
0
 
ChadMarshAuthor Commented:
32bit
0
 
ptjcbCommented:
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
ChadMarshAuthor Commented:
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
 
ptjcbCommented:
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
 
ChadMarshAuthor Commented:
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
 
ptjcbCommented:
I'm glad I could help.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now