Solved

Scheduled DTS vs. Manual Execution of DTS

Posted on 2003-12-05
12
629 Views
Last Modified: 2013-11-30
OK -- I've googled this for days now, and I still can't find the answer.

We have a DTS package which does several steps, in one of which it exports data from a view to a text file using BCP. The syntax is:

EXEC master..xp_cmdshell 'bcp SunshineMLS..vwList out \\web\d$\webserv\inclist.asp /c -t"\t" -S SMLSDB'

When I run the job manually, the view exports, just for example purposes, 100 rows of data. I've scheduled the package by right-clicking on the DTS package and selecting "Schedule Package..." The scheduled job works fine every night, and as far as I can tell the other 4 tasks in the DTS Package work fine -- but this BCP export when scheduled only exports 94 rows (again, just for example).

Why would the scheulded version of the DTS export a different # of rows from the View compared to the manually-executed DTS package? Did the act of scheduling a package "compile" a version of the View or its execution path so that if I changed the View a week ago or so, the scheduled DTS is still using the "old" version of the View?

I can't figure it out.

Any suggestions would be appreciated. I've tried re-saving the DTS package and re-scheduling it anew, but the same results.
0
Comment
Question by:Parzival2003
  • 7
  • 5
12 Comments
 
LVL 15

Expert Comment

by:Raisor
ID: 9883281
Hi,

Do you have any machine for testing purposes? -> You could export your datasource to that machine and rebuild the DTS package on that machine (manually) -> to see if the result is the same ...

Or have you done so already? ;-))


Best regards, Raisor
0
 
LVL 15

Expert Comment

by:Raisor
ID: 9883440
Hi,

As I don't want to repeat every single article from MS KB -> here the appropriate link: http://www.kbalertz.com/sresults.aspx?sw=Scheduled+DTS&st=1&stec=1 -> which surely helps solving your problem!


Best regards
0
 

Author Comment

by:Parzival2003
ID: 9883981
Thanks, but I don't think any of these links apply, as they all reference failure of the DTS package or security context of the scheduled job.

The scheduled job functions just fine, so the security context is OK. The problem is that when the scheduled job runs, 94 rows are exported. When run manually, the DTS package BCPs (if I may create a verb) the view and exports 100 rows. If I used "SELECT * FROM vwList" in Query Analyzer, again I get 100 rows.
0
 
LVL 15

Expert Comment

by:Raisor
ID: 9884511
Hi,

Sorry for the inconvenience! -> In one article was described that it happens that your scheduled job could run until a certain point -> runs into an error but does not stop with an exception -> would mean in your case: "comes up to record 94 -> produces an error which is not reported -> looks at no further records -> and ... well, that's 94 of 100 then!

Is it possible for you to publish the code you're running at Query Manager and the one you're running in the scheduled DTS job?

Just to compare "manually" -> or reproduce in my test environment!


Best regards, Raisor
0
 

Author Comment

by:Parzival2003
ID: 9885130
Thank you, Raisor, for your help.

The package does 5 tasks, each being a "Execute SQL Task". All appear to function properly and none partially complete or hang. The view is used dynamically create an .ASP include file that looks something like this:

<option value='A1'>A1
<option value='A2'>A2
<option value='A3'>A3
<option value='A4'>A4
<option value='A5'>A5
<option value='A6'>A6

However, when it's scheduled (once a night), it comes out something like this:

<option value='A1'>A1
<option value='A3'>A3
<option value='A4'>A4
<option value='A6'>A6

But as noted above, when executing the DTS manually, or just this step by itself, or "SELECT * FROM viewname" in Query Analyzer, the full results appear. The job doesn't abruptly stop, it just doesn't include rows somewhere in the middle of the resultset. I have noted that specific rows are missing -- For example, now every night when the job executes "<option value='R5'>R5" (for example) is missing from the include file, but when running the DTS manually, "R5" is exported as expected.

And BTW, SQL Server 2000, all the latest service packs, etc.
0
 
LVL 15

Expert Comment

by:Raisor
ID: 9885499
Hi,

About what formats of data are we talking? -> all just stings? maybe some integers or date values?

The data you've shown is not really interesting for to get a solution!

The queries are important! The one you run in Query Manager (which is ok by result if understood well) and the query thrown by SQL Agent at a certain date at a certain time ...

... or are we talking about one single SP that succeeds giving you results manually while automated not?


Best regards, Raisor

0
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 

Author Comment

by:Parzival2003
ID: 9920282
I'm sorry, but I just don't understand...

I have a view that says "CREATE VIEW vwList AS SELECT TOP 100 PERCENT featval FROM tblfeat WHERE featval IS NOT NULL ORDER BY featval"

1. If I run "SELECT * FROM vwList" in Query Analyzer, I get all results.
2. I have the BCP statement above in a DTS package. If I manually run the DTS package I get all results.
3. I schedule the DTS package and it runs on a nightly basis -- and I DO NOT get all results. For some reason, several rows of data are not exported.

Thanks again for your help.
0
 
LVL 15

Expert Comment

by:Raisor
ID: 9920627
Hi,

Please, can you post the error message you're receiving -> ... job history? ... event log?


Best regards, Raisor
0
 

Author Comment

by:Parzival2003
ID: 9921317
There is no error message! That's the point. The job runs fine, without error -- however, when scheduled, the output file has (just for example) 96 rows of data instead of all 100.



0
 
LVL 15

Accepted Solution

by:
Raisor earned 125 total points
ID: 9922724
Hi,

I'm really sorry to say that but I'm stuck at this point -> what I need is to have a look at it!

Is there any way to post or mail data to me that helps me rebuilding your conditions?


Best regards, Raisor
0
 

Author Comment

by:Parzival2003
ID: 9949970
Thanks for your time anyway. I guess this is a problem I'll have to live with. I don't know the proper etiquette, so please accept all the points.
0
 
LVL 15

Expert Comment

by:Raisor
ID: 9960203
Hi,

Thank you! -> If you have any more information then please let me know!


Best regards, Raisor
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

705 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

20 Experts available now in Live!

Get 1:1 Help Now