March 17, 2010 at 4:47 am
my stored procedure was work then suddenly the following error appeared
*** LoadFromSQLServer failed
OLE Automation Error Information
HRESULT: 0x80040e4d
sp_OAGetErrorInfo failed.
the code I use is
create proc sp_displayoaerrorinfo
@object as int
as
Declare @hr int
DECLARE @output varchar(255)
DECLARE @source varchar(255)
DECLARE @description varchar(255)
PRINT 'OLE Automation Error Information'
EXEC @hr = sp_OAGetErrorInfo @object, @source OUT, @description OUT
IF @hr = 0
BEGIN
SELECT @output = ' Source: ' + @source
PRINT @output
SELECT @output = ' Description: ' + @description
PRINT @output
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
RETURN
END
GO
create proc sp_displaypkgerrors
@pkg as int
as
declare @numsteps int
declare @steps int
declare @step int
declare @stepresult int
declare @pkgresult int
declare @hr int
select @pkgresult = 0
EXEC @hr = sp_OAGetProperty @pkg, 'Steps', @steps OUTPUT
IF @hr <> 0
BEGIN
print 'Unable to get steps'
EXEC sp_displayoaerrorinfo @pkg --, @hr
RETURN
END
EXEC @hr = sp_OAGetProperty @steps, 'Count', @numsteps OUTPUT
IF @hr <> 0
BEGIN
print 'Unable to get number of steps'
EXEC sp_displayoaerrorinfo @steps --, @hr
RETURN
END
while @numsteps > 0
Begin
EXEC @hr = sp_OAGetProperty @steps, 'Item', @step OUTPUT, @numsteps
IF @hr <> 0
BEGIN
print 'Unable to get step'
EXEC sp_displayoaerrorinfo @steps --, @hr
RETURN
END
EXEC @hr = sp_OAGetProperty @step, 'ExecutionResult', @stepresult OUTPUT
IF @hr <> 0
BEGIN
print 'Unable to get ExecutionResult'
EXEC sp_displayoaerrorinfo @step --, @hr
RETURN
END
select @numsteps = @numsteps - 1
select @pkgresult = @pkgresult + @stepresult
end
if @pkgresult > 0
print 'Package had ' + cast(@pkgresult as varchar) + ' failed step(s)'
else
print 'Packge Succeeded'
GO
create proc sp_executepackage
@packagename varchar(255), --package name, gets most recent version
@userpwd varchar(255) = Null, --login pwd
@intsecurity bit = 0, --use non-zero to indicate integrated security
@pkgPwd varchar(255) = '' --package password
as
declare @hr int
declare @object int
--create a package object
EXEC @hr = sp_OACreate 'DTS.Package', @object OUTPUT
if @hr <> 0
Begin
EXEC sp_displayoaerrorinfo @object --, @hr
RETURN
end
--load the package (ADD integrated security support)
declare @svr varchar(15)
declare @login varchar(15)
select @login = SUSER_NAME()
select @svr = HOST_NAME()
declare @flag int
select @flag = 0
if @intsecurity = 0
if @userpwd = Null
EXEC @hr = sp_OAMethod @object, 'LoadFromSqlServer',NULL, @ServerName=@svr, @ServerUserName=@login, @PackageName=@packagename, @Flags=@flag, @PackagePassword = @pkgPwd
-- ServerName As String, [ServerUserName As String], [ServerPassword As String], [Flags As DTSSQLServerStorageFlags = DTSSQLStgFlag_Default], [PackagePassword As String], [PackageGuid As String], [PackageVersionGuid As String], [PackageName As String], [pVarPersistStgOfHost])
else
EXEC @hr = sp_OAMethod @object, 'LoadFromSqlServer',NULL, @ServerName=@svr, @ServerUserName=@login, @PackageName=@packagename, @Flags=@flag, @PackagePassword = @pkgPwd, @ServerPassword = @userpwd
else
begin
select @flag = 256
EXEC @hr = sp_OAMethod @object, 'LoadFromSqlServer',NULL, @ServerName=@svr, @PackageName=@packagename, @Flags=@flag, @PackagePassword = @pkgPwd
end
IF @hr <> 0
BEGIN
print 'LoadFromSQLServer failed'
EXEC sp_displayoaerrorinfo @object --, @hr
RETURN
END
--execute it
EXEC @hr = sp_OAMethod @object, 'Execute'
IF @hr <> 0
BEGIN
print 'Execute failed'
EXEC sp_displayoaerrorinfo @object --, @hr
RETURN
END
--return the step errors as a recordset
exec sp_displaypkgerrors @object
-- unitialize the package
EXEC @hr = sp_OAMethod @object, 'UnInitialize'
IF @hr <> 0
BEGIN
print 'UnInitialize failed'
EXEC sp_displayoaerrorinfo @object --, @hr
RETURN
END
-- release the package object
EXEC @hr = sp_OADestroy @object
if @hr <> 0
Begin
EXEC sp_displayoaerrorinfo @object --, @hr
RETURN
end
GO
any suggestio please
March 17, 2010 at 12:21 pm
I believe that's a login failed error code (do a Bing search for 0x80040e4d and you'll see other instances, not just related to SQL Server). Has the service account for SQL Server had a recent password change? Is the account expired or locked out?
K. Brian Kelley
@kbriankelley
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply