Toad Data Point - Toad World

Toad Data Point v4.0 is now available. 
Learn what's new in this release

What's better than data sharing, SQL sharing and workflow sharing?  Getting it for FREE.
Find out how you could get a FREE perpetual TOAD INTELLIGENCE CENTRAL ... $5000 valueOffer valid only until September 30th.

Toad™ Data Point is a multi-platform database query tool that’s built for anyone who needs to access data from multiple sources, quickly integrate and prepare it all, and produce datasets and reports for analysis. It dramatically simplifies data access, writing SQL queries and data preparation – all from a single tool and without needing to be a SQL expert. With Toad, you can:

  • Access almost any source from a single tool
  • Visually drag and drop to create SQL queries
  • Profile, transform and cleanse data easily
  • Automate frequent reporting tasks

Use these links below to learn more about the most popular Toad Data Point topics!


Getting Started



Mastering
Connectivity

Writing
Queries

Import/Export


Automation

New Approaches to Managing
Your Evolving  Database Environment

We’re seeing many organizations move toward agile methodologies. But with the transition from manual change-and-release management to an automated approach of continuous integration comes new challenges ...

 

How to Easily Manage Multiple
Database Platforms and Unstructured Data

Are you struggling to manage an increasingly complex database environment? If so, you’re not alone. Research shows that data growth is doubling every 18 months. And it’s not...

Community Discussion

Danny Torres
Follow / 14 Sep 2016 at 7:04pm / Toad Data Point

How to get the most out of this forum – Best practices for what information...

Expand content
The better a problem is described, the better the assistance tends to be. In order to better assist with your issue at hand, please include the following information in your initial post: - Product...

The better a problem is described, the better the assistance tends to be.

In order to better assist with your issue at hand, please include the following information in your initial post: 

-          Product, Version, and relevant environment details.

-          Include a screenshot of the problem

-          Describe the problem fully. What result was expected?

-          Can the issue be created on demand or is it intermittent?

-          If the problem can be generated faithfully, what are the exact steps to recreate the problem?

-          Any other pertinent information (see below) 

Create Support Bundles

You can create a support bundle and send it to Dell Support or to the Toad Data Point community Web site (peer-to-peer support). The support bundle provides information (about your application and the database) that is used to help troubleshoot problems. 

Select Help | Support Bundle. It may take time for Toad to generate the support bundle depending on your system configuration.
The file name of the generated support bundle is ToadSupportBundleToad Data Point.zip.

 

Create Support Bundle for a SQL Query Issue

If your issue is a SQL query or cross-connection query issue, it is helpful if additional log files are included in the support bundle. These log files are automatically included if you generate a support bundle immediately after executing the query in either the Editor or Query Builder.

Use the following procedure to ensure that the SQL log files are generated and included in the support bundle. These additional log files (proxy.log located in the DataHubSupportBundle.zip and SQLAnalyze.log) are important to assist Support in troubleshooting the issue.

For issues related to SQL or cross-connection query optimization, use the following procedure as well. These logs are also helpful for optimization issues.

 

To create a support bundle for a SQL query or cross-connection query issue

In the Editor or Query Builder, reproduce the error or issue.

After reproducing the error, immediately select Help | Support Bundle to generate a support bundle.

Toad asks if you want to log the row count for each table used in the query. If you select Yes, Toad determines the size of each table used in the query. This adds additional time to the generation process, but also provides additional information about your issue.

When Toad is finished generating the support bundle, attach it.

Note: For best results, always send the entire support bundle.

 

*Note: Please refrain from including private data in your posts

Be as thorough as you can with the provided info. We will reply to you as quick as possible.

We are glad to assist you.

 Latest Release Information:

 This announcement includes information on our latest releases, links to our downloads and documentation and overview of new features.  This way you can stay as informed as possible:

 -          What’s New Section (why they would want to pay attention to this new version)

-          http://documents.software.dell.com/toad-data-point/4.0/release-notes/release-notes/new-features

 -          Link to Download & Documentation

-          https://support.software.dell.com/toad-data-point/4.0

-          Link to Trial

-          https://software.dell.com/register/54955/

 -          Overview of new feature(s) (with link to the release notes)

http://documents.software.dell.com/toad-data-point/4.0/release-notes/release-notes/new-features

http://documents.software.dell.com/toad-data-point/4.0/release-notes/

 

 Top 5 Knowledge Base Articles:

 On this post, you can find our top trending knowledge articles that other customers are inquiring about and popular solutions.

 This month’s Top Knowledge Base Articles

1. This month’s Top Knowledge Base ArticlesVideo - Making Effective Use of Data with Toad Data Point (195645)

https://support.software.dell.com/toad-data-point/kb/195645

 2. Common Oracle connection problems (7712)

https://support.software.dell.com/toad-data-point/kb/7712

 3. Error, "ORA-12170: TNS:Connect timeout occurred" when connecting to Oracle (105610)

https://support.software.dell.com/toad-data-point/kb/105610

 4. Error connecting to database after installing on Windows 64-Bit OS: "ORA-12154" (12495)

https://support.software.dell.com/toad-data-point/kb/12495

 5. "ERROR [08001] [IBM] SQL30081N A communication error has been detected." when connecting to DB2 (47041)

https://support.software.dell.com/toad-data-point/kb/47041

Feel free to browse them. Remember to sign in to your account to view.

 

146 0 / Create an account to join the discussion.
N.B.
Follow / 28 Sep 2016 at 12:13am / Toad Data Point

[TDP 3.8] [Automation] [Copy File Activity] Variable Behavior Pro...

Expand content
I had a bit of inspiration and so I've been working to try and template our basic automation reports that send files externally so that others can implement the same process and only have to change...

I had a bit of inspiration and so I've been working to try and template our basic automation reports that send files externally so that others can implement the same process and only have to change a few variables.

For the most part that hasn't been a problem and is working well, but where I've encountered an issue is when we get to the final two steps of every automation - posting copies of the files sent to the SharePoint site for the internal customers, and moving the files to the archive on the fileserver.

In previous automations, both the copy file mask and the destination have been 90% hard-coded, with only a few variables - such as month, date, year - in use.  We use UNC paths to access both the SharePoint and the Fileserver, and I think this is where our issue is.

In my new 'template' I've created 7 additional variables in addition to our standard 3 - the new ones are bolded:

  1. #Date# - SQL, formatted at MMDDYY
  2. #Mth# - SQL, formatted as MM MTH (e.g. 09 SEP).
  3. #Yr# - SQL, formatted as YYYY.'
  4. #RG# - String, set to B
  5. #BV# - String, set to D
  6. #Vendor# - String, set to Est
  7. #SP_Path# - String, set to \\\\SP_Serverr\\SP_Region\\SP_Site\\C\\Work\\#Vendor#\\#Yr#\\#Mth#
  8. #SP_Mask# - String, set to *#RG#*#BV#*#Vendor#*#Date#.xlsx
  9. #FS_Path# - String, set to \\\\FS_Server\\FS_Dept\\Reports\\#Yr#\\C\\#Vendor\\#Mth#
  10. #FS_Mask# - String, set to *#RG#*#BV#*#Vendor#*#Date#*

The way this automation is configured, the export and delivery of the file to the vendor happens before posting to the SharePoint and archiving to the Fileshare - none of that has issues and works just fine; it's the copy file activity where I'm running into issues - this is how I have them configured:

Copy File to SharePoint:

 

Move File to Archive:

The Log file indicates that both the paths and the masks are being set correctly at startup:

-- 9/27/2016 5:21:09 PM: B (D) - Daily C Work Files (R) (Est).log: Workflow Compiled without Errors
-- 9/27/2016 5:21:09 PM: B (D) - Daily C Work Files (R) (Est).log: Build completed
-- 9/27/2016 5:21:09 PM: B (D) - Daily C Work Files (R) (Est).log: Create Workflow instance
-- 9/27/2016 5:21:09 PM: B (D) - Daily C Work Files (R) (Est).log: Started workflow instance
-- 9/27/2016 5:21:09 PM: B (D) - Daily C Work Files (R) (Est).log: Begin execution script activities
-- 9/27/2016 5:21:09 PM: B (D) - Daily C Work Files (R) (Est).log: Connecting to SQL Server
-- 9/27/2016 5:21:09 PM: B (D) - Daily C Work Files (R) (Est).log: Variable "Mth" set to '09 SEP'
-- 9/27/2016 5:21:09 PM: B (D) - Daily C Work Files (R) (Est).log: Connecting to SQL Server
-- 9/27/2016 5:21:09 PM: B (D) - Daily C Work Files (R) (Est).log: Variable "Yr" set to 2016
-- 9/27/2016 5:21:09 PM: B (D) - Daily C Work Files (R) (Est).log: Connecting to SQL Server
-- 9/27/2016 5:21:09 PM: B (D) - Daily C Work Files (R) (Est).log: Variable "Date" set to '092716'
-- 9/27/2016 5:21:09 PM: B (D) - Daily C Work Files (R) (Est).log: Variable "D_ZipFile" set to '(TEST) B (D) Daily C Work Files (R) (Est) 092716.zip'
-- 9/27/2016 5:21:09 PM: B (D) - Daily C Work Files (R) (Est).log: Variable "RG" set to 'B'
-- 9/27/2016 5:21:09 PM: B (D) - Daily C Work Files (R) (Est).log: Variable "BV" set to 'D'
-- 9/27/2016 5:21:09 PM: B (D) - Daily C Work Files (R) (Est).log: Variable "Vendor" set to 'Est'
-- 9/27/2016 5:21:09 PM: B (D) - Daily C Work Files (R) (Est).log: Variable "SP_Path" set to '\\SP_Server\SP_Region\SP_Site\C\Work\Est\2016\09 SEP'
-- 9/27/2016 5:21:09 PM: B (D) - Daily C Work Files (R) (Est).log: Variable "FS_Path" set to '\\FS_Server\FS_Dept\Reports\2016\C\Est\09 SEP'
-- 9/27/2016 5:21:09 PM: B (D) - Daily C Work Files (R) (Est).log: Variable "SP_Mask" set to '*B*D*Est*092716*.xlsx'
-- 9/27/2016 5:21:09 PM: B (D) - Daily C Work Files (R) (Est).log: Variable "FS_Mask" set to '*B*D*Est*092716*'

However, when the automation gets to the File Activities, something goes wrong:

-- 9/27/2016 5:21:13 PM: B (D) - Daily C Work Files (R) (Est).log: Export Finished, click (TEST) B (D) Daily C Work Files (R) (Est) 092716.xlsx to view file
-- 9/27/2016 5:21:13 PM: B (D) - Daily C Work Files (R) (Est).log: Row count variable File_1_RCOUNT set to 8886 rows
-- 9/27/2016 5:21:13 PM: B (D) - Daily C Work Files (R) (Est).log: File to archive: C:\temp\(TEST) B (D) Daily C Work Files (R) (Est) 092716.xlsx: Size: 0 Bytes
-- 9/27/2016 5:21:13 PM: B (D) - Daily C Work Files (R) (Est).log: 1 files to archive
-- 9/27/2016 5:21:13 PM: B (D) - Daily C Work Files (R) (Est).log: Start archiving
-- 9/27/2016 5:21:13 PM: B (D) - Daily C Work Files (R) (Est).log: Created archive, click (TEST) B (D) Daily C Work Files (R) (Est) 092716.zip to view file.
-- 9/27/2016 5:21:13 PM: B (D) - Daily C Work Files (R) (Est).log: Variable "D_FTP_Rcode" set to '0'
-- 9/27/2016 5:21:13 PM: B (D) - Daily C Work Files (R) (Est).log: Condition: "#D_FTP_Rcode# = 0" evaluated to True

-- 9/27/2016 5:21:13 PM: B (D) - Daily C Work Files (R) (Est).log: Destination file name: \\FS_Server\FS_Dept\Automation\Vendors\Est'\SP_Server\SP_Region\SP_Site\C\Work\Est\2016\09 SEP'\(TEST) B (D) Daily C Work Files (R) (Est) 092716.xlsx
-- 9/27/2016 5:21:13 PM: B (D) - Daily C Work Files (R) (Est).log: Destination folder name: '\\SP_Server\SP_Region\SP_Site\C\Work\Est\2016\09 SEP'
-- 9/27/2016 5:21:13 PM: B (D) - Daily C Work Files (R) (Est).log: File will be copied and not moved

-- 9/27/2016 5:21:13 PM: B (D) - Daily C Work Files (R) (Est).log: Copied file C:\temp\(TEST) B (D) Daily C Work Files (R) (Est) 092716.xlsx to \\FS_Server\FS_Dept\Automation\Vendors\Est'\SP_Server\SP_Region\SP_Site\C\Work\Est\2016\09 SEP'\(TEST) B (D) Daily C Work Files (R) (Est) 092716.xlsx
-- 9/27/2016 5:21:13 PM: B (D) - Daily C Work Files (R) (Est).log: Copy File finished, click (TEST) B (D) Daily C Work Files (R) (Est) 092716.xlsx to view file.
-- 9/27/2016 5:21:13 PM: B (D) - Daily C Work Files (R) (Est).log: We would copy C:\temp\*B*D*Est*092716*.xlsx to \\SP_Server\SP_Region\SP_Site\C\Work\Est\2016\09 SEP.

-- 9/27/2016 5:21:13 PM: B (D) - Daily C Work Files (R) (Est).log: Destination file name: \\FS_Server\FS_Dept\Automation\Vendors\Est'\FS_Server\FS_Dept\Reports\2016\C\Est\09 SEP'\(TEST) B (D) Daily C Work Files (R) (Est) 092716.xlsx
-- 9/27/2016 5:21:13 PM: B (D) - Daily C Work Files (R) (Est).log: Destination folder name: '\\FS_Server\FS_Dept\Reports\2016\C\Est\09 SEP'
-- 9/27/2016 5:21:13 PM: B (D) - Daily C Work Files (R) (Est).log: File will be moved
-- 9/27/2016 5:21:13 PM: B (D) - Daily C Work Files (R) (Est).log: Moved file C:\temp\(TEST) B (D) Daily C Work Files (R) (Est) 092716.xlsx to \\FS_Server\FS_Dept\Automation\Vendors\Est'\FS_Server\FS_Dept\Reports\2016\C\Est\09 SEP'\(TEST) B (D) Daily C Work Files (R) (Est) 092716.xlsx
-- 9/27/2016 5:21:13 PM: B (D) - Daily C Work Files (R) (Est).log: Copy File finished, click (TEST) B (D) Daily C Work Files (R) (Est) 092716.xlsx to view file.

-- 9/27/2016 5:21:14 PM: B (D) - Daily C Work Files (R) (Est).log: Destination file name: \\FS_Server\FS_Dept\Automation\Vendors\Est'\FS_Server\FS_Dept\Reports\2016\C\Est\09 SEP'\(TEST) B (D) Daily C Work Files (R) (Est) 092716.zip
-- 9/27/2016 5:21:14 PM: B (D) - Daily C Work Files (R) (Est).log: Destination folder name: '\\FS_Server\FS_Dept\Reports\2016\C\Est\09 SEP'
-- 9/27/2016 5:21:14 PM: B (D) - Daily C Work Files (R) (Est).log: File will be moved
-- 9/27/2016 5:21:14 PM: B (D) - Daily C Work Files (R) (Est).log: Moved file C:\temp\(TEST) B (D) Daily C Work Files (R) (Est) 092716.zip to \\FS_Server\FS_Dept\Automation\Vendors\Est'\FS_Server\FS_Dept\Reports\2016\C\Est\09 SEP'\(TEST) B (D) Daily C Work Files (R) (Est) 092716.zip
-- 9/27/2016 5:21:14 PM: B (D) - Daily C Work Files (R) (Est).log: Copy File finished, click (TEST) B (D) Daily C Work Files (R) (Est) 092716.zip to view file.
-- 9/27/2016 5:21:14 PM: B (D) - Daily C Work Files (R) (Est).log: We would move C:\temp\*B*D*Est*092716* to \\FS_Server\FS_Dept\Reports\2016\C\Est\09 SEP
-- 9/27/2016 5:21:14 PM: B (D) - Daily C Work Files (R) (Est).log: Done

Despite the log file indicating that the Destination Folder Name is correct (-- 9/27/2016 5:21:13 PM: B (D) - Daily C Work Files (R) (Est).log: Destination folder name: '\\SP_Server\SP_Region\SP_Site\C\Work\Est\2016\09 SEP') the file path that Toad comes up with is completely wrong (\\FS_Server\FS_Dept\Automation\Vendors\Est'\SP_Server\SP_Region\SP_Site\C\Work\Est\2016\09 SEP'\(TEST) B (D) Daily C Work Files (R) (Est) 092716.xlsx ) - it appears as though Toad is appending the source directory of the automation script ( \\FS_Server\FS_Dept\Automation\Vendors\Est ) to the destination file name, which makes no sense - no other Automation has this kind of issue, only this one where I've attempted to store the Destination Paths and the File Copy/Move mask in a Variable - and as you can see, Toad correctly sets those variables, and even correctly applies the mask and those paths - but it also appends it's own directory for some reason.

Has anyone else encountered this?  If so, did you find a work around?

31 0 / Create an account to join the discussion.
zzz
Follow / 27 Sep 2016 at 5:25pm / Toad Data Point
Latest post by on 27 Sep 2016 at 11:04pm

Task Scheduler: Export not running Macro(s) on Excel 2016

Expand content
Toad Data Point 3.8 Windows Server 2008 R2 Standard Excel 2016 Hello, I have seen similar postings of this issue but can't seem to resolve it. With the recent MS Office upgrade, data in all XLSM...

Toad Data Point 3.8
Windows Server 2008 R2 Standard
Excel 2016

Hello,

I have seen similar postings of this issue but can't seem to resolve it. With the recent MS Office upgrade, data in all XLSM files are no longer updated since the macro does not execute.

While observing the process, I noticed the file exported was a different a size, then it quickly "rolls-back" to the size of "pre-macro".

I've also interrupted the process; as soon as the file is exported (before it was finalized), I intentionally opened the file and the data is accurate (the macro had run). I then received an error notification from Toad (as intended) since the process could not be completed.

The Export seems execute the macro, but then disables it before finalizing? It works fine running the job directly from Toad, but not from the Task Scheduler.

Toad Data Point 3.8 -- .TAS

1. ODBC Driver: Oracle in OraClient11g_home1 (64 bit)

2. Export Category: Query;   Output Format: Excel;   Excel file: .xlsm;   Worksheet name: (Clear worksheet data before exporting);   Existing File: Add Date/Time Suffix to filename

3. Execute macro: After export (I've tried different combinations)

4. Export objects list: Export query to ExcelFile

Excel 2016

1. Macro Settings:

a) Enable all macros

b) Trust access to the VBA project object model

Task Scheduler

1. Configure for: Windows Server 2008 R2 Standard (I've selected "Windows Server 2008" - no difference)

2. Run with highest privileges

In addition (this occurs sporadically), when I open an existing TAS file, a new Database Activities (i.e. Database Connection), or File Activities (i.e. Delete File), or System Activities (i.e. Pause) is added mysteriously towards the bottom. Nothing in the Error Log to explain the reason for this...

Any suggestions? I greatly appreciate the help!

Thank you Smile

Unanswered 45 1 / Create an account to join the discussion.