In Automation, Export Wizard Doesn't Pass DateTime Correctly, Throws "not a valid month" error

Toad Data Point & Toad Intelligence Central
1

Using the Set Variable activity to set two date time fields, then using the Export Wizard activity to call a SQL script that uses those datetime parameters as input throws the following error message.

Timestamp Message
8/26/2014 3:44:34 PM Variable “COUNTY” set to ‘ARCHULETA’
8/26/2014 3:44:34 PM Variable “TSTART_DATE” set to 8/1/2013 12:00:00 AM
8/26/2014 3:44:34 PM Variable “TEND_DATE” set to 7/31/2014 12:00:00 AM
8/26/2014 3:44:34 PM CYFF - ORA-01843: not a valid month

8/26/2014 3:44:34 PM Failed

It works just fine using the Select to File activity instead of Export Wizard, but Export Wizard has the ability to append rows in Excel, while Select to File does not.

This is Toad Data Point 3.5

2

What database platform are you using (Oracle, SQL Server, Sybase, etc.)? You might have better luck passing the variable as text and using a To_Date function (Oracle) or Convert function (SQL Server/Sybase) as a work around.

3

Oracle. I could do that, but I’d have to change a lot of scripts. Just hoping bugs like this can be fixed for the next release.

4

Please check that your variable has type DateTime and set correctly like on the following screen shot

3324.Capture.png

After that you can use the variable in SELECT like

SELECT ORDER_ID, ORDER_DATE, SHIPPING_ADDRESS_ID, BILLING_ADDRESS_ID, CONTACT_ID, ESTIMATED_SHIP_DATE, ACTUAL_SHIP_DATE, AMOUNT_BILLED

FROM QUEST_DEV.ORDERS

WHERE ESTIMATED_SHIP_DATE < :VarDateTime

Regards

Aleksey

5

Thanks Aleksey,

It doesn't work in the Export Wizard activity.

On Wed, Aug 27, 2014 at 11:16 AM, Aleksey Bazhenov bounce-AlekseyBazhenov@toadworld.com wrote:

RE: In Automation, Export Wizard Doesn't Pass DateTime Correctly, Throws "not a valid month" error

Reply by Aleksey Bazhenov
Please check that your variable has type DateTime and set correctly like on the following screen shot

After that you can use the variable in SELECT like

SELECT ORDER_ID, ORDER_DATE, SHIPPING_ADDRESS_ID, BILLING_ADDRESS_ID, CONTACT_ID, ESTIMATED_SHIP_DATE, ACTUAL_SHIP_DATE, AMOUNT_BILLED

FROM QUEST_DEV.ORDERS

WHERE ESTIMATED_SHIP_DATE < :VarDateTime

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or Unsubscribe from Toad Data Point - General notifications altogether.

Toad Data Point - Discussion Forum

Flag this post as spam/abuse.

--
Eric Chilenskas

Performance Data Specialist

Eric.Chilenskas@state.co.us

6

TDP 3.5 Select to File activity now has the same set of properties when exporting to xls/xlsx as Export Wizard. Please check Advanced Properties button.

7

Select to File doesn’t have the ‘append rows’ option. It overwrites data saved to the same sheet. I’m taking data from multiple sources and putting them into the same sheet, which is why I’m using the Export activity.

I’m working around it by putting data on separate tabs, then writing a script without a datetime variable to select * into one tab via the Export activity.

8

You can export to a csv file and then call a batch job to append. If your batch job (call it AddFiles.bat) contains Type path%1.csv>>path\TotalFile.csv and you pass the new file name as the first parameter each time, at the end you will have all your data in TotalFile.csv. In Automation you use a Run Program step to call AddFiles.bat (include the path) and on the arguments line you pass the [tag:FileName]# variable or literal text of the file name (use double quotes around it if there are spaces in your path or file name). You can put this inside of a loop function to use the variable file name.

9

We’ve discussed it from my companies’ end as well and this is something I believe they have addressed in the upcoming 3.6 version. I’ve seen the option and there will be an append to the same worksheet option on the export wizard in that version. You should try the Beta 3.6 and see if that works for you and accomplishes what you need. It did for us.

Chad

10

Toad Data Point 3.6 still doesn’t pass datetime without an error under the Export activity.

I see that Select to File has the ‘Append’ option, but now I’m getting ‘character set mismatch’ errors on scripts that run perfectly fine in TDP 3.5 in Oracle.

11

I have no problem to use datetime variable in select to file activity with append. Can you send your tas file to yake.li@quest.com to investigate. Thanks!

12

Not in Select to File, but in the Export Wizard activity. I need Export Wizard because it has append, S to File does not in 3.5. I was told it does in 3.6 but other bugs crop up in 3.6 that prevent me from using it.

In 3.6 Beta passing dates to export wizard doesn't work either.

I use TDP 3,5 and Oracle

Sent from my iPhone

On Sep 4, 2014, at 4:10 PM, Kiki bounce-Kiki@toadworld.com wrote:

RE: In Automation, Export Wizard Doesn't Pass DateTime Correctly, Throws "not a valid month" error

Reply by Kiki
I have no problem to use datetime variable in select to file activity with append. Can you send your tas file to investigate.

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or Unsubscribe from Toad Data Point - General notifications altogether.

Toad Data Point - Discussion Forum

Flag this post as spam/abuse.

13

Eric, can you send export wizard template and details on what is occurring to me in private email and we will investigate. debbie.peabody@quest.com Include connection type, version and screenshots of results along with the export template.

14

Hi, Debbie,

Thanks for responding. I've written up a document with the two biggest TDP 3.5 bugs that affect my work, and have attached supporting files.

The version is Toad Data Point 3.5.0.2926. The connection type is to Oracle 11g using SID.

The Word document has explanations & screenshots. The Test.txp is the export file for the second error mentioned in the document.

I would be grateful if both of these issues were resolved in the coming release.

The bugs are both in Automation, Toad not passing list parameters correctly, which forces me to hard code lists, and is a lot of extra work, and secondly in Automation not passing datetime parameters properly in the Export Wizard activity. Both explained in the .doc.

If you have questions or need more information, please let me know. I'm eager to get these resolved.

Eric

On Thu, Sep 4, 2014 at 5:20 PM, Debbie Peabody bounce-Debbie_Peabody@toadworld.com wrote:

RE: In Automation, Export Wizard Doesn't Pass DateTime Correctly, Throws "not a valid month" error

Reply by Debbie Peabody
Eric, can you send export wizard template and details on what is occurring to me in private email and we will investigate. debbie.peabody@quest.com Include connection type, version and screenshots of results along with the export template.

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or Unsubscribe from Toad Data Point - General notifications altogether.

Toad Data Point - Discussion Forum

Flag this post as spam/abuse.

--
Eric Chilenskas

Performance Data Specialist

Eric.Chilenskas@state.co.us

15

Try using MM instead of mm in the to_Date function for the month.

From: ericchil [mailto:bounce-ericchilenskas@toadworld.com]

Sent: Thursday, September 11, 2014 2:32 PM

To: toaddatapoint@toadworld.com

Subject: Re: [Toad Data Point - Discussion Forum] In Automation, Export Wizard Doesn’t Pass DateTime Correctly, Throws “not a valid month” error

Re: In Automation, Export Wizard Doesn’t Pass DateTime Correctly, Throws “not a valid month” error

Reply by ericchil

Hi, Debbie,

Thanks for responding. I’ve written up a document with the two biggest TDP 3.5 bugs that affect my work, and have attached supporting files.

The version is Toad Data Point 3.5.0.2926. The connection type is to Oracle 11g using SID.

The Word document has explanations & screenshots. The Test.txp is the export file for the second error mentioned in the document.

I would be grateful if both of these issues were resolved in the coming release.

The bugs are both in Automation, Toad not passing list parameters correctly, which forces me to hard code lists, and is a lot of extra work, and secondly in Automation not
passing datetime parameters properly in the Export Wizard activity. Both explained in the .doc.

If you have questions or need more information, please let me know. I’m eager to get these resolved.

Eric

On Thu, Sep 4, 2014 at 5:20 PM, Debbie Peabody bounce-Debbie_Peabody@toadworld.com wrote:

RE: In Automation, Export Wizard Doesn’t Pass DateTime Correctly, Throws “not a
valid month” error

Reply by Debbie Peabody

Eric, can you send export wizard template and details on what is occurring to me in private email and we will investigate.
debbie.peabody@quest.com Include connection type, version and screenshots of results along with the export template.

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad Data Point - General notifications altogether.

Toad Data Point - Discussion Forum

Flag
this post as spam/abuse.

–

Eric Chilenskas

Performance Data Specialist

Eric.Chilenskas@state.co.us

Attachments:

Toad Data Point Bugs to Developers.docx

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad Data Point - General notifications altogether.

Toad Data Point - Discussion Forum

Flag
this post as spam/abuse.

16

Issue #1: Change your variable type to SQL and execute this against Oracle:

select '''ALAMOSA'', ''BACA'', ''BENT''' country from dual;

This will "trick" the automation into behaving. I have entered QAT-3922 to fix in the future but it will not make the TDP 3.6 release.

Workaround.png
Workaround.png658×651 20.6 KB

Issue #2:

Also use this same solution for binding the dates.

select to_date('04/13/2013', 'MM/DD/YYYY')
from dual

var6.png
var6.png667×687 22.4 KB

Issue #3:

Union all with two of same binds. In this case you trying to get around the issue in item one but you can't use the same variable and have different values. Use the work around given. I did enter QAT-3923 as we should not totally abbend in this case:)