Scheduled DTS vs. Manual Execution of DTS

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.
Parzival2003Asked:
Who is Participating?
 
Ralf KlattConnect With a Mentor Concerned World CitizenCommented:
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
 
Ralf KlattConcerned World CitizenCommented:
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
 
Ralf KlattConcerned World CitizenCommented:
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
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
Parzival2003Author Commented:
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
 
Ralf KlattConcerned World CitizenCommented:
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
 
Parzival2003Author Commented:
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
 
Ralf KlattConcerned World CitizenCommented:
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
 
Parzival2003Author Commented:
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
 
Ralf KlattConcerned World CitizenCommented:
Hi,

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


Best regards, Raisor
0
 
Parzival2003Author Commented:
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
 
Parzival2003Author Commented:
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
 
Ralf KlattConcerned World CitizenCommented:
Hi,

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


Best regards, Raisor
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.