OLE Automation Error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

OLE Automation Error

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-01-18 : 08:32:24

Alan writes "SQL Server 2000 sp4
Can anybody explain why this works for awhile and then errors:

[SP1: This code returns automation error - shown at end]

-- TEST CODE
DECLARE @SelectedDate DATETIME
SET @SelectedDate = CONVERT(DATETIME, '2006-01-06 00:00:00', 102)
-- END TEST CODE

DECLARE @SelectedOffice varchar(2)
DECLARE @Result varchar
DECLARE salesoffice_cursor CURSOR FOR
SELECT DISTINCT GinstOfficeRouteDay.HandheldBranchNo
FROM GinstOfficeRouteDay

OPEN salesoffice_cursor

FETCH FROM salesoffice_cursor into @SelectedOffice
WHILE @@fetch_status = 0
BEGIN
EXEC @Result = spGinsters_AS400Transfers_DateOffice @SelectedDate, @SelectedOffice
FETCH NEXT FROM salesoffice_cursor INTO @SelectedOffice
END
CLOSE salesoffice_cursor
DEALLOCATE salesoffice_cursor
GO

[SP1 executes this code SP2 (15) times:]

CREATE PROC spGinsters_As400Transfers_DateOffice
@SelectedDate DATETIME, @SelectedOffice varchar(2)
AS

-- TEST CODE
--DECLARE @SelectedDate DATETIME
--SET @SelectedDate = CONVERT(DATETIME, '2006-01-06 00:00:00', 102)
--DECLARE @SelectedOffice varchar(2)
--SET @SelectedOffice = 'WM'
-- END TEST CODE

DECLARE @RouteNo varchar(3)
DECLARE route_cursor CURSOR FOR
SELECT DISTINCT GinstOfficeRouteDay.RouteNo
FROM GinstOfficeRouteDay
WHERE (GinstOfficeRouteDay.Date = @SelectedDate) AND (GinstOfficeRouteDay.HandheldBranchNo = @SelectedOffice)
ORDER BY GinstOfficeRouteDay.RouteNo
OPEN route_cursor
FETCH FROM route_cursor into @RouteNo
WHILE @@fetch_status = 0
BEGIN
EXEC spGinsters_AS400Transfers_DateRoute @SelectedDate, @RouteNo
FETCH NEXT FROM route_cursor INTO @RouteNo
END
CLOSE route_cursor
DEALLOCATE route_cursor
GO

[SP2 executes this code SP3 (approx 15 times for each execution):]

CREATE PROC spGinsters_As400Transfers_DateRoute
@SelectedDate DATETIME, @RouteNo varchar(3)
AS
-- TEST CODE
--DECLARE @SelectedDate DATETIME
--DECLARE @RouteNo varchar(3)
--SET @SelectedDate = CONVERT(DATETIME, '2006-01-06 00:00:00', 102)
--SET @RouteNo = '602'
-- END TEST CODE

DECLARE @DateString varchar(10)
SET @DateString = (Right('00' + CAST(DatePart(d,@SelectedDate) as varchar),2)) + '/' + (Right('00' + CAST(DatePart(m,@SelectedDate) as varchar),2)) + '/' + (Right('0000' + Cast(DatePart(yyyy,@SelectedDate) as varchar),4))

DECLARE @Server varchar(255)
DECLARE @PkgName varchar(255)
DECLARE @ServerPWD varchar(255)
DECLARE @IntSecurity bit
DECLARE @PkgPWD varchar(255)
DECLARE @GlobalV_1 varchar(511)
DECLARE @GlobalV_2 varchar(511)

SET @Server = 'RATest'
SET @PkgName = 'NSITransfer'
SET @ServerPWD = 'sa'
SET @IntSecurity = 0
SET @PkgPWD = ''
SET @GlobalV_1 = 'SelectedDate=' + @DateString
SET @GlobalV_2 = 'RouteNo=' + @RouteNo
EXEC master.dbo.spExecutePKGGlobalVariables @Server, @PkgName, @ServerPWD, @IntSecurity, @PkgPWD, @GlobalV_1, @GlobalV_2

SET @PkgName = 'InvoiceTransfer'
SET @GlobalV_1 = 'SelectedDate=' + @DateString
SET @GlobalV_2 = 'RouteNo=' + @RouteNo
EXEC master.dbo.spExecutePKGGlobalVariables @Server, @PkgName, @ServerPWD, @IntSecurity, @PkgPWD, @GlobalV_1, @GlobalV_2
GO

[This seems to work for awhile then errors as shown in message below]

Packge Succeeded
Step DTSStep_DTSDataPumpTask_3 (Transform Data Task: undefined) Succeeded
Step DTSStep_DTSDataPumpTask_1 (Transform Data Task: undefined) Succeeded

etc, etc 85 times in total then

Packge Succeeded
*** Unable to get steps
OLE Automation Error Information
HRESULT: 0x0000275d
sp_OAGetErrorInfo failed.
*** UnInitialize failed SelectedDate=06/01/2006 RouteNo=708
OLE Automation Error Information
HRESULT: 0x80040005
sp_OAGetErrorInfo failed.
*** Create Package object failed SelectedDate=06/01/2006 RouteNo=709
OLE Automation

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources