September 12, 2008 at 6:25 am
Working on SQL Server 200 SP4.
I have a DTS package that is executed from a stored procedure. The problem occurs when I get an error on the package and apply a change to fix it. The next time the Stored Procedure calls ANY Package I get the following error:
Microsoft Data Transformation Services (DTS) Package - Not enough storage is available to process this command.
The only resolution is to bounce the Server.
September 16, 2008 at 7:02 am
September 17, 2008 at 8:17 am
Hi,
Thanks for replying - thi is the code for executing.
SET @Package = 'SXE Done File' -- + @stream
-- Create a DTS Package object
exec @hr = sp_OACreate 'DTS.Package', @pkg output
if @hr <> 0
begin
exec @hr = [master].[dbo].[sp_OAGetErrorInfo] @pkg, @source out, @description out
if @hr = 0
Print 'sp_OACreate Error : ' + @Package + ' ' + @source + ' - ' + @description
else
Print 'sp_OAGetErrorInfo Error (Create)'
end
set @source = ''
set @description= ''
-- load parms
exec @hr = sp_OAMethod @pkg, 'LoadFromSqlServer', null
,@ServerName = '(local)'
,@Flags= 256 --Integrated security. Use 0 for SQL Server authentication
,@PackageName = @Package
if @hr <> 0
begin
exec @hr = [master].[dbo].[sp_OAGetErrorInfo] @pkg, @source out, @description out
print 'error loading parms'
print @Package + ' ' + @source + ' - ' + @description
end
exec @hr = sp_OASetProperty @Pkg, 'GlobalVariables("Load_No").value', @LoadNo
if @hr <> 0
begin
print 'error laod no Variable'
end
exec @hr = sp_OASetProperty @Pkg, 'GlobalVariables("Table_Name").value', @TableName
if @hr <> 0
print 'error Tablename Variable'
exec @hr = sp_OASetProperty @Pkg, 'GlobalVariables("Table_ID").value', @TableID
if @hr <> 0
print 'error TableID Variable'
exec @hr = sp_OASetProperty @Pkg, 'GlobalVariables("File_Path").value', @ImportFolder
if @hr <> 0
print 'error File_Path Variable'
-- run package
exec @hr = sp_OAMethod @pkg, 'Execute'
if @hr <> 0
print 'error on Execute ' + @Package
February 24, 2009 at 8:24 pm
I am having the exact same problem.
Have you found a solution?
February 25, 2009 at 3:32 am
The only resolution suggestions I've found are here:
http://msdn.microsoft.com/en-us/library/ms837395.aspx
HTH,
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply