Solved

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

Posted on 2007-11-15
6
990 Views
Last Modified: 2013-11-30
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
Comment
Question by:jschnei555
  • 3
  • 3
6 Comments
 
LVL 15

Expert Comment

by:JimFive
ID: 20290071
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
 
LVL 15

Accepted Solution

by:
JimFive earned 500 total points
ID: 20290101
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
 

Author Comment

by:jschnei555
ID: 20298145
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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 15

Expert Comment

by:JimFive
ID: 20298579
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
 

Author Comment

by:jschnei555
ID: 20299228
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
 

Author Comment

by:jschnei555
ID: 20299289
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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question