Solved

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

Posted on 2007-11-15
6
991 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
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.

 
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

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

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

Suggested Solutions

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
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.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

732 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