Dear All:

First, sorry to be wordy, just trying to give what we have found so far so
that you might be able to see something we missed or didn't check.

We have 6 DTS Packages that we use to update our Web database from our
internal database. We have a job setup to run all 6 DTS packages in
succession. We created the job on 4/23 and here is our job history:

4/23 -- Success
4/24 -- Success
4/25 -- Failure (Security issue that was resolved)
Tested 4/25 after security fix and worked.
4/26 -- Success
4/27 -- Success
4/28 -- Failure (Error: Not enough storage is available to complete this
operation)
Tested task 4 times. 3 failures and one success.
4/29 -- Failure (Error: Not enough storage is available to complete this
operation)
Ran job without modification in morning, ran successfully. Ran repeatedly
and got: Fail, Fail, Success, Fail, Success
4/30 -- Failure (Error: Not enough storage is available to complete this
operation)
5/1 -- Failure (Error: Not enough storage is available to complete this
operation)
5/2 -- Success

The error we keep getting in the package logs is:
Step Error Source: Microsoft Data Transformation Services (DTS) Package
Step Error Description:Not enough storage is available to complete this
operation.
(Microsoft Data Transformation Services (DTS) Package (8007000e): Not
enough storage is available to complete this operation.
) (Microsoft Data Transformation Services (DTS) Data Pump (8007000e): Not
enough storage is available to complete this operation.
)
Step Error code: 8007000E
Step Error Help File:sqldts80.hlp
Step Error Help Context ID:1100

This occurs on DTSStep_DTSDataPumpTask_1 in the second DTS package to run.
It imports rows from one SQL database to another SQL database on the same
server. The Pump has two transforms, a column copy that copies all but one
column from source to destination. The other is a ActiveX script that uses
a lookup that takes 3 values in and outputs one integer value to the one
remaining destination column. Right now the transform copies 150K rows.

We can run the task repeatedly and it will fail most of the time, but will
occationally succeed. E.G. Over the weekend, it failed Saturday and Sunday
and then succeeded Monday. No user intervention.

We have PLENTY of HD space and PLENTY of RAM available. Security ACLs have
been verified according to Microsoft for SQL Server and SQL Server Agent.
All other tasks and imports and exprots that run around this job succeed.

Anyone know what the issue is here and why this is occuring? What is it
checking to get a "not enough storage" error when everything is showing
plenty of space?

Thanks,
Scott

Scott,

Since it does work sometimes, there is definitely no major issue.

Try doing this.

In the data pump, instead of having two transformation, assign source
columns to the destination column for all the columns in the activeX
script. You may have to delete the first transformation.

Thanks,
Yunus.

Hi Scott

"Wm. Scott Miller" <Scott.Miller (AT) spamkillerwvinsurance (DOT) gov> schrieb im
Newsbeitrag news:%2361SU2yTFHA.3636 (AT) TK2MSFTNGP14 (DOT) phx.gbl...
do you use lookups in the transform? Then this would be a known bug in SQL
Server.
If so, you can order a hotfix to solve the problem.
http://support.microsoft.com/default...b;en-us;889170
I noticed this error only on W2k3 Servers with SP1 installed, so far.
Which system do you use?

Helge

Helge:

Thanks for the reply. We are running under Win2k3 with SP1 installed and
then uninstalled (created a unforseen problem on the server). Job ran fine
for about a week and then started giving errors. Yes, this one uses a
lookup, which I'd like to follow Microsoft's advice for a workaround to "not
use lookup queries in DTS packages" but I'd like to see them do what I am
without the use of one. We have got it working again by rebooting the
server. The reason we started this job was because a previous one began
failing right at the end with Microsoft .NET crashing on cleanup.
Everything ran fine, but it kept reporting a failure because of cleanup
problems. Rebooting the server fixed that one so we have moved back to
using it instead of this one. And, yes, it is using a lookup without
problems. Difference between the two jobs is a follows:

Job in question is one Job with 6 steps. Each step executes a separate DTS
package.
Job now running is one Job executing one DTS Package that executes 6 DTS
packages.

Sounds like Microsoft doesn't even understand what fixes the issue. Lookups
work fine as long as not executed directly from a job. Must call the lookup
package from another package for it to work. We would never have gotten
this error has .NET not started messing up.

Scott

"Helge C. Rutz" <news050424 (AT) nurfuerspam (DOT) de> wrote

BTW, think that SQL 2000 SP4 includes that hotfix?

Scott

"Helge C. Rutz" <news050424 (AT) nurfuerspam (DOT) de> wrote

Well, I spoke too soon. The job has been running fine for about a week and
just last night began failing again with the same exact error. This time
with the "fixed" routine. Obviously, nothing has changed over the week (you
know, don't fix it if it ain't broke :-) So it appears to be some type of
memory leak brought on by use of SQL Server 2k on Win2k3SP1 (even if SP1 is
uninstalled). Hopefully this is fixed in SQL2kSP4--at least it is listed as
being included. We shall see........

Scott

"Wm. Scott Miller" <Scott.Miller (AT) spamkillerwvinsurance (DOT) gov> wrote