• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1003
  • Last Modified:

DTS Job Failing on Scheduled Task, but is successful when run manually

First, I don't think this a security/permissions problem.  We have a DTS job that calls a stored procedure that was working for several months.  When we turned on replication, the job started intermittently (most of the time) failing with this error msg.:
Executed as user: GLWHOLESALE\Administrator. ...SStep_DTSExecuteSQLTask_1   DTSRun OnError:  DTSStep_DTSExecuteSQLTask_1, Error = -2147217900 (80040E14)      Error string:  Could not complete cursor operation because the table schema changed after the cursor was declared.      Error source:  Microsoft OLE DB Provider for SQL Server      Help file:        Help context:  0      Error Detail Records:      Error:  -2147217900 (80040E14); Provider Error:  16943 (422F)      Error string:  Could not complete cursor operation because the table schema changed after the cursor was declared.      Error source:  Microsoft OLE DB Provider for SQL Server      Help file:        Help context:  0

However, if I click on the scheduled task and click Start, or run the stored procedure manually, it runs successfully.  

The stored procedure is attached.  It should be said that the IV00102 table is being replicated.  However, the structure doesn't change.  

CREATE procedure X_UPDATE_BIN_HISTORY(@SITE char(11)) as
 
/*local vars for fetch*/
declare @ITEMNUM char(31)
declare @BINNUM char(21)
declare @ZONEID	char(11)
 
/*declare cursor*/
declare	 IV00102CURSOR CURSOR FOR
SELECT GLW.dbo.IV00102.ITEMNMBR, GLW.dbo.IV00102.BINNMBR, GLW.dbo.BIM_BINS.ZONEID FROM GLW.dbo.IV00102 (NOLOCK)  
LEFT OUTER JOIN GLW.dbo.BIM_BINS (NOLOCK)  ON GLW.dbo.IV00102.BINNMBR = GLW.dbo.BIM_BINS.BINID
WHERE GLW.dbo.IV00102.LOCNCODE= @SITE
 
open IV00102CURSOR
 
fetch IV00102CURSOR into @ITEMNUM, @BINNUM, @ZONEID
 
/*no records*/
if (@@FETCH_STATUS = -1)
begin
	close IV00102CURSOR
	deallocate IV00102CURSOR
	return
end
 
	
 
/*while cursor result set not empty*/
while(@@FETCH_STATUS = 0)
begin
 
	declare @histbin char(21)
	declare @histzone char(11)
	declare histcursor cursor for
		--exec BINLOOKUP @ITEMNUM, @SITE
		SELECT TOP 1 BINID, ZONEID  FROM GLW.dbo.XIV_BINHISTORY 
		WHERE  ITEMID = @ITEMNUM AND  SITEID =  @SITE
		ORDER BY  BINDATE DESC
	
	open histcursor
	
	/*we only fetch once b/c we only expect at most one record*/
	fetch histcursor into @histbin, @histzone 
	
	/*if no records, go ahead and enter one*/
	if(@@FETCH_STATUS = -1) 
	begin
				
			insert into  GLW.dbo.XIV_BINHISTORY values (@ITEMNUM, getdate(), @SITE, @BINNUM, @ZONEID)
 
	end
	/*otherwise check to make sure records match.  if they don't enter a new one*/
	else
	begin
			if(@histbin <> @BINNUM OR @histzone <> @ZONEID)
				begin
					insert into  GLW.dbo.XIV_BINHISTORY values (@ITEMNUM, getdate(), @SITE, @BINNUM, @ZONEID)
				end
 
			/*else move on to the next record...*/
	end
	close histcursor
	deallocate histcursor
 
	fetch IV00102CURSOR into @ITEMNUM, @BINNUM, @ZONEID
end
close IV00102CURSOR
DEALLOCATE IV00102CURSOR
GO

Open in new window

0
jschnei555
Asked:
jschnei555
  • 3
  • 3
1 Solution
 
JimFiveCommented:
I can't really speak to the replication aspect, but I suspect the cursors are taking too long and interfering with the replication.  Couldn't you replace your cursors with something like:

CREATE procedure X_UPDATE_BIN_HISTORY(@SITE char(11))
AS
INSERT INTO GLW.dbo.XIV_BINHISTORY(ItemNum, HistDate, Site, BinNum, ZoneID)
      Select ItemNum, getdate(), Site, BinID, ZoneID
            FROM GLW.dbo.IV00102 IV (NOLOCK)
                  LEFT OUTER JOIN GLW.dbo.BIM_BINS BIM (NOLOCK)
                        ON GLW.dbo.IV00102.BINNMBR = GLW.dbo.BIM_BINS.BINID
      WHERE GLW.dbo.IV00102.LOCNCODE = @SITE
      AND NOT EXISTS (SELECT * from GLW.dbo.XIV_BINHISTORY BH
                  WHERE BH.BinID = IV.BinID
                  AND BH.ZoneID = BIM.ZoneID)
0
 
JimFiveCommented:
I think that last should be:
 NOT EXISTS (Select * from GLW.dbo.XIV_BINHISTORY BH
                WHERE BH.BINID = IV.BINID
                AND BH.ZoneID = BIM.ZoneID
                AND BH.ITEMID = IV.ITEMNMBR
                AND BH.SITEID = IV.LOCNCODE

0
 
jschnei555Author Commented:
I would believe that it was taking too long and interfering with the replication if I couldn't run the job manually or execute the stored procedure, successfully.  The point of this query is to log changes to the fields only if they've changed, so I don't see how I can restructure the query as suggested.  The "NOT EXISTS" portion of code would nearly always evaluate to false.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
JimFiveCommented:
The not exists portion (revised) will evaluate to false when the fields listed in that where clause have not changed.

As best I can tell, my revised query should evaluate exactly the same as your nested cursors.

How oftern do you have your job scheduled to run?  If you have autoshrink on your database this can cause this error if it kicks in while the job is running.  Also, any maintenance plan or job that does reindexing can cause this.

You really should redesign this not to use cursors.
--
JimFive
0
 
jschnei555Author Commented:
I want to thank you bunches for your advice.  I will restructure the code to avoid the cursors, based on your recommendation, but there is one issue with your code:  we are trying to log every change since the most current entry.  Using your code, if an item changes from bin A to B and back to A, the most recent change to A would not get logged.  
0
 
jschnei555Author Commented:
final solution just needed a tweak.  thanks so much!

insert into XIV_BinHistory (ItemID, BinDate, SiteID, BinID, ZoneID)
 
select 
IV.itemnmbr, 
getdate(), 
IV.locncode, 
IV.BINNMBR, 
BIM.ZONEID
 
from IV00102 IV (NOLOCK) left join 
BIM_Bins BIM (NOLOCK) on IV.BINNMBR = BIM.BINID 
WHERE IV.LOCNCODE = 'GLW' and 
NOT EXISTS
(Select * from XIV_BinHistory BH
WHERE 
BH.ItemID = IV.ItemNmbr and
isnull(BH.BINID, 0) = isnull(IV.BinNmbr,0) and
isnull(BH.SiteID, '') = isnull(IV.LocnCode, '') and
isnull(BH.ZoneID,0) = isnull(BIM.ZoneID, 0) and 
BH.bindate = (select top 1 bindate from XIV_BinHistory where itemid = BH.ItemID
and siteid = BH.siteid
order by bindate desc)
)

Open in new window

0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now