March 7, 2006 at 2:11 am
Hi All
I am running a scheduled stored procedure that executes a package, this is run multiple times with different parameters.
This runs for a few days and then I get an error LoadFromSQLServer failed, I get this message every time the stored procedure is run until I stop and restart the SQL Server service. Once the Server has been restarted it again works for a few days.
Can anyone please shed some light on to the cause of this, will I have to keep stopping and starting the service?
Thanks
Alan Browne
March 10, 2006 at 8:00 am
This was removed by the editor as SPAM
March 10, 2006 at 8:34 am
Do the Event, SQL or Job logs point to any reason for the failure to load? What is the error message you are receiving?
-Mike Gercevich
March 27, 2006 at 12:43 am
Hi Mike
Sorry taken so long to get back - had a family berivement.
There is nothing showing in any of the logs.
Someone has restarted server yesterday therefore todays worked. The message I usually get is LoadFromSQLServer Failed. I will post more info when next fails, normally 3 days after restart.
Cheers - Alan Browne
May 23, 2006 at 1:54 am
Hi, Alan!
Have you resolved the problem?
We have the same issue now.
Thanks, Yulia
May 23, 2006 at 10:54 am
Hi, Yulia
No still have the same issue and seem to have to reboot server approx every 3 to 4 days.
I will try and get some more info together next time error occurs.
Alan
May 24, 2006 at 8:10 am
You may want to check if there is any exclusive locks on your MSDB database during this situation. Also, start a trace to understand what command is causing the blocking.
-Mike Gercevich
P.S. Sounds like a Trusted-User account validation problem
May 25, 2006 at 3:05 am
I had the same error again this morning and this time I have traced it to failing on:
sp_OAMethod @oPKG, @Cmd, NULL
The DTS that runs all the commands is triggered at 06:15 and this runs for 2 to 3 days without a hitch, I tried rerunning the DTS and all the commands failed again. I Rebooted the SQL Server and ran again and this time all went ok.
Still don't know why this is happening though.
Alan
May 26, 2006 at 5:06 am
Just a thought... Are you calling the sp_OADestroy method for the object you are creating with all sp_OACreate methods? Also, what are the @oPKG & @Cmd variables set to? I am assuming that the @Cmd is the .Execute() method for the package object...but I thought I would ask to make sure.
-Mike Gercevich
May 26, 2006 at 6:51 am
Hi Mike
The code i am using is:
CREATE PROCEDURE spExecutePKGGlobalVariables(
@Server varchar(255),
@PkgName varchar(255),
@ServerPWD varchar(255) = Null,
@IntSecurity bit = 0,
@PkgPWD varchar(255) = '',
@GlobalV_1 varchar(511) = Null,
@GlobalV_2 varchar(511) = Null,
@GlobalV_3 varchar(511) = Null)
AS
SET NOCOUNT ON
DECLARE @OutString varchar(255)
DECLARE @source varchar(255)
DECLARE @description varchar(255)
DECLARE @hr int, @ret int, @oPKG int, @Cmd varchar(1000)
DECLARE @GVName varchar(255), @GVValue varchar(255)
--Create an instance of a DTS package
EXEC @hr = sp_OACreate 'DTS.Package', @oPKG OUT
If @hr <> 0
BEGIN
EXEC sp_displayoaerrorinfo @oPKG, @hr
EXEC @hr = sp_OAGetErrorInfo @oPKG, @source OUT, @description OUT
IF @hr = 0
BEGIN
SET @OutString = 'echo ' + @GlobalV_2 + ' *** Create Package object failed OLE Automation Error Information Source: ' + @Source + ' Description: ' + @description + ' >> C:\AS400Transfer.Log'
EXEC master..xp_cmdshell @OutString
END
ELSE
BEGIN
SET @OutString = 'echo ' + @GlobalV_2 + ' *** Create Package object failed - sp_OAGetErrorInfo failed. >> C:\AS400Transfer.Log'
EXEC master..xp_cmdshell @OutString
RETURN 2
END
RETURN 2
END
--Evaluate Security and Build LoadFromSQLServer Statement
IF @IntSecurity = 0
SET @Cmd = 'LoadFromSQLServer("' + @server + '", "' + SUSER_SNAME() + '", "' + @ServerPWD + '", 0, "' + @PkgPWD + '", , , "' + @PkgName + '")'
ELSE
SET @Cmd = 'LoadFromSQLServer("' + @server + '", "", "", 256, "' + @PkgPWD + '", , , "' + @PkgName + '")'
EXEC @hr = sp_OAMethod @oPKG , @Cmd, NULL
If @hr <> 0
BEGIN
EXEC sp_displayoaerrorinfo @oPKG, @hr
EXEC @hr = sp_OAGetErrorInfo @oPKG, @source OUT, @description OUT
IF @hr = 0
BEGIN
SET @OutString = 'echo ' + @GlobalV_2 + ' *** LoadFromSQLServer failed OLE Automation Error Information Source: ' + @Source + ' Description: ' + @description + ' >> C:\AS400Transfer.Log'
EXEC master..xp_cmdshell @OutString
END
ELSE
BEGIN
SET @OutString = 'echo ' + @GlobalV_2 + ' *** LoadFromSQLServer failed - sp_OAGetErrorInfo failed. >> C:\AS400Transfer.Log'
EXEC master..xp_cmdshell @OutString
RETURN 3
END
RETURN 3
END
IF @GlobalV_1 IS NOT NULL
BEGIN
SET @GVName = Left(@GlobalV_1, CHARINDEX('=', @GlobalV_1)-1)
SET @GVValue = Right(@GlobalV_1, LEN(@GlobalV_1) - CHARINDEX('=', @GlobalV_1))
SET @Cmd = 'GlobalVariables("' + @GVName + '").Value'
EXEC @hr = sp_OASetProperty @oPKG, @Cmd, @GVValue
IF @hr <> 0
BEGIN
EXEC sp_displayoaerrorinfo @oPKG, @hr
EXEC @hr = sp_OAGetErrorInfo @oPKG, @source OUT, @description OUT
IF @hr = 0
BEGIN
SET @OutString = 'echo ' + @GlobalV_2 + ' *** GlobalVariable 1 Assignment Failed OLE Automation Error Information Source: ' + @Source + ' Description: ' + @description + ' >> C:\AS400Transfer.Log'
EXEC master..xp_cmdshell @OutString
END
ELSE
BEGIN
SET @OutString = 'echo ' + @GlobalV_2 + ' *** GlobalVariable 1 Assignment Failed - sp_OAGetErrorInfo failed. >> C:\AS400Transfer.Log'
EXEC master..xp_cmdshell @OutString
RETURN @OutString --4
END
RETURN 4
END
END
IF @GlobalV_2 IS NOT NULL
BEGIN
SET @GVName = Left(@GlobalV_2, CHARINDEX('=', @GlobalV_2)-1)
SET @GVValue = Right(@GlobalV_2, LEN(@GlobalV_2) - CHARINDEX('=', @GlobalV_2))
SET @Cmd = 'GlobalVariables("' + @GVName + '").Value'
EXEC @hr = sp_OASetProperty @oPKG, @Cmd, @GVValue
IF @hr <> 0
BEGIN
PRINT '*** GlobalVariable 2 Assignment Failed ' + @GlobalV_1 + ' ' + @GlobalV_2 + ' ' + @GlobalV_3
EXEC sp_displayoaerrorinfo @oPKG, @hr
PRINT 'OLE Automation Error Information'
EXEC @hr = sp_OAGetErrorInfo @oPKG, @source OUT, @description OUT
IF @hr = 0
BEGIN
SET @OutString = 'echo ' + @GlobalV_2 + ' *** GlobalVariable 2 Assignment Failed OLE Automation Error Information Source: ' + @Source + ' Description: ' + @description + ' >> C:\AS400Transfer.Log'
EXEC master..xp_cmdshell @OutString
END
ELSE
BEGIN
SET @OutString = 'echo ' + @GlobalV_2 + ' *** GlobalVariable 2 Assignment Failed - sp_OAGetErrorInfo failed. >> C:\AS400Transfer.Log'
EXEC master..xp_cmdshell @OutString
RETURN 4
END
RETURN 4
END
END
IF @GlobalV_3 IS NOT NULL
BEGIN
SET @GVName = Left(@GlobalV_3, CHARINDEX('=', @GlobalV_3)-1)
SET @GVValue = Right(@GlobalV_3, LEN(@GlobalV_3) - CHARINDEX('=', @GlobalV_3))
SET @Cmd = 'GlobalVariables("' + @GVName + '").Value'
EXEC @hr = sp_OASetProperty @oPKG, @Cmd, @GVValue
IF @hr <> 0
BEGIN
PRINT '*** GlobalVariable 3 Assignment Failed ' + @GlobalV_1 + ' ' + @GlobalV_2 + ' ' + @GlobalV_3
EXEC sp_displayoaerrorinfo @oPKG, @hr
PRINT 'OLE Automation Error Information'
EXEC @hr = sp_OAGetErrorInfo @oPKG, @source OUT, @description OUT
IF @hr = 0
BEGIN
SET @OutString = 'echo ' + @GlobalV_2 + ' *** GlobalVariable 3 Assignment Failed OLE Automation Error Information Source: ' + @Source + ' Description: ' + @description + ' >> C:\AS400Transfer.Log'
EXEC master..xp_cmdshell @OutString
END
ELSE
BEGIN
SET @OutString = 'echo ' + @GlobalV_2 + ' *** GlobalVariable 3 Assignment Failed - sp_OAGetErrorInfo failed. >> C:\AS400Transfer.Log'
EXEC master..xp_cmdshell @OutString
RETURN 4
END
RETURN 4
END
END
--Execute Package
EXEC @hr = sp_OAMethod @oPKG, 'Execute'
IF @hr <> 0
BEGIN
EXEC sp_displayoaerrorinfo @oPKG, @hr
EXEC @hr = sp_OAGetErrorInfo @oPKG, @source OUT, @description OUT
IF @hr = 0
BEGIN
SET @OutString = 'echo ' + @GlobalV_2 + ' *** Execute failed OLE Automation Error Information Source: ' + @Source + ' Description: ' + @description + ' >> C:\AS400Transfer.Log'
EXEC master..xp_cmdshell @OutString
END
ELSE
BEGIN
SET @OutString = 'echo ' + @GlobalV_2 + ' *** Execute failed - sp_OAGetErrorInfo failed. >> C:\AS400Transfer.Log'
EXEC master..xp_cmdshell @OutString
RETURN 5
END
RETURN 5
END
--Check Package Errors
EXEC @ret=spDisplayPkgErrors @oPKG
--Unitialize the Package
EXEC @hr = sp_OAMethod @oPKG, 'UnInitialize'
IF @hr <> 0
BEGIN
PRINT '*** UnInitialize failed ' + @GlobalV_1 + ' ' + @GlobalV_2 + ' ' + @GlobalV_3
EXEC sp_displayoaerrorinfo @oPKG, @hr
PRINT 'OLE Automation Error Information'
EXEC @hr = sp_OAGetErrorInfo @oPKG, @source OUT, @description OUT
IF @hr = 0
BEGIN
SET @OutString = 'echo ' + @GlobalV_2 + ' *** UnInitialize failed OLE Automation Error Information Source: ' + @Source + ' Description: ' + @description + ' >> C:\AS400Transfer.Log'
EXEC master..xp_cmdshell @OutString
END
ELSE
BEGIN
SET @OutString = 'echo ' + @GlobalV_2 + ' *** UnInitialize failed - sp_OAGetErrorInfo failed. >> C:\AS400Transfer.Log'
EXEC master..xp_cmdshell @OutString
RETURN 6
END
RETURN 6
END
--Clean Up
EXEC @hr = sp_OADestroy @oPKG
IF @hr <> 0
BEGIN
PRINT '*** Destroy failed'
EXEC sp_displayoaerrorinfo @oPKG, @hr
PRINT 'OLE Automation Error Information'
EXEC @hr = sp_OAGetErrorInfo @oPKG, @source OUT, @description OUT
IF @hr = 0
BEGIN
SET @OutString = 'echo ' + @GlobalV_2 + ' *** Destroy failed OLE Automation Error Information Source: ' + @Source + ' Description: ' + @description + ' >> C:\AS400Transfer.Log'
EXEC master..xp_cmdshell @OutString
END
ELSE
BEGIN
SET @OutString = 'echo ' + @GlobalV_2 + ' *** Destroy failed - sp_OAGetErrorInfo failed. >> C:\AS400Transfer.Log'
EXEC master..xp_cmdshell @OutString
RETURN 7
END
RETURN 7
END
RETURN @ret
GO
Yes sp_OADestroy method is called each time - untill it fails that is.
--Create an instance of a DTS package
EXEC @hr = sp_OACreate 'DTS.Package', @oPKG OUT
SET @Cmd = 'LoadFromSQLServer("' + @server + '", "' + SUSER_SNAME() + '", "' + @ServerPWD + '", 0, "' + @PkgPWD + '", , , "' + @PkgName + '")'
Alan
June 15, 2006 at 5:30 am
Hi everyone
I am still having a problem with this one and no answer in sight, has anybody got any other ideas Please?
Thanks
Alan Browne
June 23, 2006 at 1:44 am
Hi All
I have a log error for this now:
Step 'DTSStep_DTSExecutePackageTask_6' failed
Step Error Source: Microsoft Data Transformation Services (DTS) Package
Step Error Description:Not enough storage is available to process this command.
Not enough storage is available to process this command.
Step Error code: 80070008
Step Error Help File:sqldts80.hlp
Step Error Help Context ID:1100
Step Execution Started: 23/06/2006 07:39:47
Step Execution Completed: 23/06/2006 07:39:47
Total Step Execution Time: 0 seconds
Progress count in Step: 0
But still don't know how to fix.
Any help would be greatly appreciated
Thanks
Alan
June 23, 2006 at 9:44 am
Alan,
Sounds like a memory leak. Does this KB article apply?: http://support.microsoft.com/kb/816937/ It says that the problem was fixed in sp4 and there's a hotfix for instances running sp3.
Greg
Greg
June 26, 2006 at 3:55 am
Thanks Greg
I was thinking that myself but our server is at service pack 4 and we still have the problem.
Alan
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply