DTS: Not enough storage is available to process this command

[Alexander]

Hello guys,

I have a stored procedure (which uses the sp's sp_OACreate,
sp_OAMethod, etc.) that should execute a DTS-package on the server.
This DTS-packages exports data from SQL Server database to Excel files.

When I run the DTS on the server, everything works just fine and my Excels are filled with data from SQLServer. However, when I execute the SP that should execute the DTS from a SQL Query Analyzer I get the following error:

Not enough storage is available to process this command.

Even when I do this in a SQL Query Analyzer on the server.
SQL Server SP 3 is installed on the server, and there's plenty of memory available. Right after I re-start the server, SP may work OK.

Can someone tell what is wrong?

Regards,
Alexander

[rmiao]

Sounds memory leak. How much memory does server have? How much allocated to sql? Which version of sql by the way?

[Alexander]

SQL has 6GB of memory, the DTS is not big.

SQL Server 2000.

If this is a memory leak, then it's SQL Server leak, because SP fails even from Query Analyzer, not only from the application.

The SP used to work for years (at least 5 years) and started failing recently.


Regards,
Alexander

[rmiao]

How much memory allocated to sql2k? You enabled awe? Applied any sp?

[Alexander]

All 6GB are allocated to SQL 2k. Awe is NOT enabled. SP3 was applied.

[rmiao]

Sql can't use more than 3gb memory without awe. By the way, only sql2k enterprise edition supports awe. And latest sp is sp4.

[Alexander]

Thank you RMIAO for the usual desire to help!

The DTS package does not require anywhere close to 3GB. May be 10MB worth of data, may be less.

The catch is, that after I re-start SQL Server the stored procedure works just fine. After a while, it stops.

I reset calls to DTS via dtsrun.exe and now waiting to see if all users can execute it without permission issues.

Still, calling DTS using SP is my strong preference.

Any ideas what could it be? Worked fine for many years and suddenly cracked.

I remember reading something about negative aspects of SP4. Do you think it's safe to upgrade?

Thank you.
Alexander

[rmiao]

Sounds sql doesn't have enough memory to work with. Sql2k sp4 has awe bug but fixed by sp4a. You need check you sql edition and let us know memory setting in sql.