?
Solved

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

Posted on 2007-11-15
6
Medium Priority
?
998 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 2000 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
Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

 
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

Get MongoDB database support online, now!

At Percona’s web store you can order your MongoDB database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card. Handle your MongoDB database support now!

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
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…
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.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Suggested Courses

771 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