Solved

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

Posted on 2007-11-15
6
987 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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how the fundamental information of how to create a table.

758 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now