sql server dts: OLE: Stored Procedure Error -- sql server dts

OLE: Stored Procedure Error

seddonm1 NO[at]SPAM anz.com
2/26/2004 3:35:08 PM

sql server dts:

Gday guys,
I am having a problem invoking a DTS packade on my SQL Server. I am
using the stored procedures that use the OLE object to invoke the DTS
package. The package I am using is available
'http://www.databasejournal.com/features/mssql/article.php/10894_1459181_1'
called 'spExecutePKG'.

Basically it invokes the DTS package using the OLE Automation
sp_OAMethod call. i.e. it does the following:

EXEC @hr = sp_OAMethod @oPKG, @Cmd, NULL

IF @hr <> 0
BEGIN
PRINT '*** LoadFromSQLServer failed'
EXEC sp_displayoaerrorinfo @oPKG , @hr
RETURN 1
END

We are invoking this using a Visual Basic application.

What is happening is that this works for several weeks after a server
reboot but then the DTS stops running. I have executed the DTS
package using SQL Server Enterprise Manager and it works.

When I execute the DTS package using the stored procedure I get an OLE
automation error number: 80030070. I have resolved this to:
There is insufficient disk space to complete operation.

What I believe is happening is that over time the SQL server
'reserved' disk space is being used up and when the package is
invoked, there is no room left unallocated although there is physical
space on the disk.

Can you guys suggest a way to debug? maybe some info about how the
OLE Automation executes.

Thanks,

Re: Stored Procedure Error

Jim Young
2/26/2004 6:19:10 PM

The component you are using probably has a memory leak that doesn't show any
symptoms until a certain time period has passed. Since you are already using
T-SQL to invoke your job, why not eliminate the use of the OLE object and
create a job for the DTS package and run it with sp_start_job?

Jim

[quoted text, click to view]