Solved

Lock table overflow error on Production but not Development

Posted on 2007-12-05
7
1,293 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
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

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Format Date fields 11 56
google retention policy 4 121
Call to SQL server times out 5 50
Access 2003, find all instances of database ODBC 3 42
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
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…

739 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