Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Scheduled DTS vs. Manual Execution of DTS

Posted on 2003-12-05
12
Medium Priority
?
695 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
[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
  • 7
  • 5
12 Comments
 
LVL 15

Expert Comment

by:Ralf Klatt
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:Ralf Klatt
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 15

Expert Comment

by:Ralf Klatt
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:Ralf Klatt
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
 

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:Ralf Klatt
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:
Ralf Klatt earned 250 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:Ralf Klatt
ID: 9960203
Hi,

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


Best regards, Raisor
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
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…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

670 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