I have an automated, scheduled program that runs a code set, then outputs it into a xls document.
I’m having an issue where occasionally it only exports some of the rows…
if I run the query, it pulls… let’s say 5 rows, but it may only export 3… or none.
but if I run the query manually it gives me 5 rows.
Any idea or suggestion?
Thank you,
That is a weird problem! Check the connection for your automation job and if you use a different connection with in the job step than your manuall connection. You can also run a query step just before your export and put the row count into a variable, then put in a step to log the value in the row count variable to the job log. Then you can make sure you are actually getting all the rows you are trying to export by viewing the job log after it runs. It is possible to use a different connection for an export than what the default connection for the job has (specially if you are using and export module .txp file), so check.
Good luck,
this sounds like a support case i am working on. But i don’t have any files to look at. please provide all automation files, dependent files, and automation logging file so i can look into this.
Could you please clarify what activities do you use in the automation script for exporting data? Is it Select to file activity? Or Export wizard activity? (Just to narrow down the problem)
Can you please check what is the setting of "Start export at: "? (there should be the position to a cell in excel like “A” “1”). Could you please verify that this is correct? Maybe you use some headers in the excel file. Not saying it’s the root cause but i remember once i had problem with this setting.
What is the row count difference approx.? In thousands?
When this case happens Is the row count difference always different (random-like) comparing to previous occurrence?
It’s to put the results of the query into an excel file. It’s one huge file that’s broken down into several xls files. It’s an Export, not a select to file. In the Output file there are several queries that create several xls document; and in the automation file there are several run query commands nested with the output file. So it tells it to run the query, then for client code 1 make this xls and client code 2 make another one. Then it runs another query and puts the data on tab 2 of client file 1, etc. I can’t make it into several automations because depending on how many providers the initial code pulls, determines on how long the query takes to run, and the first query has to completely finish before the next step starts because the second set is dependent on the first step. Also, the first step has to completely run because in some cases the second step drops a table that the first step used and uses the same table name, because I have a limited amount of space in my schema, I reuse the table names.
The result set could be between 1 and 1,000. not a big set at all, that’s how we caught the problem. The manager knew a provider should have been on the report and wasn’t. So I went back and looked at the final table and it was there, it just didn’t export.
There’s no rhyme or reason to what it drops.
In order for me to dig into this I need all the information. Opening a support ticket is best. however, if you want to send me the following items through email I will take a look.
I need the Automation script (*.tas)
I need any dependent file such as a linked sql file.
I need you schedule the script to and get the automation log file from the scheduled execution.
I need you to run the script shortly after step 3 in TDP and generate a log showing normal behavior.
i am trying to see what the log files look like when they run normally and when they have an error. It sounds like the data is changing a lot so we would need to run the same script in TDP close to the time where there is an error.
Here’s the problem. I’ve had to take the automation off of part of it, simply because the managers were getting upset that their reports were garbage.
The other issue is it creates a table that in then compared against next week. So I can’t rerun anything because it would change the table that next week’s data compares against.
I have a support ticket open. It’s been open for weeks! SR Number:3633389
Believe me, we’ve copied everything under the sun, he should have whatever you need.
Thanks
From: Debbie Peabody [mailto:bounce-Debbie_Peabody@toadworld.com]
Sent: Monday, November 07, 2016 8:56 AM
To: toaddatapoint@toadworld.com
Subject: RE: [Toad Data Point - Discussion Forum] I have an automated job, the query pulls for instance 5 rows, but only exports 3 of them. Any idea? I’m using TDP 4.0
Reply by Debbie Peabody
In order for me to dig into this I need all the information. Opening a support ticket is best. however, if you want to send me the following items through email I will take a look.
I need the Automation script (*.tas)
I need any dependent file such as a linked sql file.
I need you schedule the script to and get the automation log file from the scheduled execution.
I need you to run the script shortly after step 3 in TDP and generate a log showing normal behavior.
i am trying to see what the log files look like when they run normally and when they have an error. It sounds like the data is changing a lot so we would need to run the same script in TDP close to the time where there is an error.
To reply, please reply-all to this email.
Stop receiving emails on this subject.
Or Unsubscribe from Toad Data Point Forum notifications altogether.
Toad Data Point - Discussion Forum
Flag this post as spam/abuse.
Here are the files for the automation and the sql code, but I can’t run it, I would have to rewrite the code to create different tables, and I just don’t have time to do that.
Like I said, I haven’t been able to run this since I submitted the original heat ticket.
From: Debbie Peabody [mailto:bounce-Debbie_Peabody@toadworld.com]
Sent: Monday, November 07, 2016 8:56 AM
To: toaddatapoint@toadworld.com
Subject: RE: [Toad Data Point - Discussion Forum] I have an automated job, the query pulls for instance 5 rows, but only exports 3 of them. Any idea? I’m using TDP 4.0
Reply by Debbie Peabody
In order for me to dig into this I need all the information. Opening a support ticket is best. however, if you want to send me the following items through email I will take a look.
I need the Automation script (*.tas)
I need any dependent file such as a linked sql file.
I need you schedule the script to and get the automation log file from the scheduled execution.
I need you to run the script shortly after step 3 in TDP and generate a log showing normal behavior.
i am trying to see what the log files look like when they run normally and when they have an error. It sounds like the data is changing a lot so we would need to run the same script in TDP close to the time where there is an error.
To reply, please reply-all to this email.
Stop receiving emails on this subject.
Or Unsubscribe from Toad Data Point Forum notifications altogether.
Toad Data Point - Discussion Forum
Flag this post as spam/abuse.