Toad Data Point Forum

Danny Torres
Follow / 14 Sep 2016 at 7:04pm / Toad Data Point
Latest post by on 10 May 2017 at 6:59pm

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.

 

6999 4 / Create an account to join the discussion.
cpshort
Follow / 15 May 2018 at 6:24pm / Toad Data Point

HTML Email Body with Loop Variables

Expand content
I have an automation script that loops through a list of users and sends an email to each one. I'd like to format the email body in HTML so that I can add a hyperlink in the email. The email body must...

I have an automation script that loops through a list of users and sends an email to each one.  I'd like to format the email body in HTML so that I can add a hyperlink in the email.  The email body must also be customized for each user something like: 

Hello #Loop_data_1_SQL.NAME#

Please click this link to see your results

I can get the loop variables to insert without any html formatting, and I can get the email to format in HTML but without any loop variables... Not sure how to get both. Any help appreciated!

Also, if anyone from Toad is reading this... a nice addition to the product would be to allow HTML to be input directly into the email body section.  Seems like this would be much more simple.

45 0 / Create an account to join the discussion.
dcribbs_59021
Follow / 15 May 2018 at 2:45pm / Toad Data Point

Right Click Copy Rows - I changed it to not include headers and clicked...

Expand content
So, I was copying some rows, and mistakenly selected the no headers option and also clicked the quit nagging me check box. Now I wish i had not as i cannot find where to change it back in the settings...

So, I was copying some rows, and mistakenly selected the no headers option and also clicked the quit nagging me check box.  Now I wish i had not as i cannot find where to change it back in the settings.  Does anyone know where that is?

78 0 / Create an account to join the discussion.
wiggot
Follow / 3 May 2018 at 6:38pm / Toad Data Point
Latest post by on 15 May 2018 at 2:04pm

Can I reference one calculated field in another?

Expand content
Example: I am trying to convert from Hyperion to something other than Hyperion. Corporate has decided that TDP is a viable alternative. I have one calculated field "F2_Address" that just...

Example:

I am trying to convert from Hyperion to something other than Hyperion. Corporate has decided that TDP is a viable alternative.

I have one calculated field "F2_Address" that just pulls the first four characters of an overall address field, attached to the "Units" table.

I have a second calculated field "F2_Vertex_Check" that looks at "F2_Address" to see if that value is '8002'. There's also a 3rd calculated field that references the 2nd, and a 4th that references the 3rd.

Other than duplicating the code of "F2_Address" inside "F2_Vertex_Check", is there a way to reference "F2_Address" by its Field Alias?

I've tried F2_Address, Units.F2_Address, Units!F2_Address, Units@F2_Address. I can't think of any other esoteric form to reference it. And, it won't get past the compiler when I try to execute the SQL.

344 6 / Create an account to join the discussion.
tennis_champion
Follow / 8 May 2018 at 6:57pm / Toad Data Point
Latest post by on 14 May 2018 at 10:18pm

Query is processing in Editor but not within Scheduled Job (.tas)

Expand content
I have created a new connection to MS SQL server (Toad 4.1.) due to an upgrade. I can run existing queries within Editor. But when I update connection in the existing .tas the query does not recognize...

I have created a new connection to MS SQL server (Toad 4.1.) due to an upgrade. I can run existing queries within Editor. But when I update connection in the existing .tas the query does not recognize the tables within structure of the db. I have never encountered this issue before. Please assist.

281 4 / Create an account to join the discussion.
AleemCh
Follow / 3 Apr 2018 at 10:30pm / Toad Data Point
Latest post by on 14 May 2018 at 10:12pm

Check if file exists and validate before loading

Expand content
Hello there, I have just started using Toad DataPoint for automation. The process I am trying to automate requires a file to be uploaded to database. - Is there a way to check if file is available...

Hello there,

I have just started using Toad DataPoint for automation. The process I am trying to automate requires a file to be uploaded to database.

- Is there a way to check if file is available?

- Is it possible to do a pre-validation on the file, or something similar?

The last file I processed had comma delimited text in one cell. Although the .csv had it enclosed in quotes, the script failed the row as it considered the data for multiple columns instead of one.

Thanks,

604 1 / Create an account to join the discussion.
ajmoehle
Follow / 10 May 2018 at 9:39pm / Toad Data Point
Latest post by on 14 May 2018 at 6:38pm

Identify & Translate Help: Random Corrupting: Older Converted...

Expand content
We are having random files is various versions of Toad corrupting and we cannot figure out the cause. Furthermore the files are so corrupt we cannot pull any data out of it to recreate the file. We end...

We are having random files is various versions of Toad corrupting and we cannot figure out the cause. Furthermore the files are so corrupt we cannot pull any data out of it to recreate the file. We end up finding saved copies (of the exact file) in other locations to use and then no corruption exists, and the file is run. We are trying to find the cause of the corruption and how to get the info in the file itself back.

The corruption seems to convert the file into XML Schema and references "CriteriaDataSet", www.quest.com/2005/07/05/ToadSqlModeler (various others) and more. Granted the first few characters of the file are: 

U+006D : LATIN SMALL LETTER M
U+006E : LATIN SMALL LETTER N
U+0004 : <control-0004> (END OF TRANSMISSION [EOT])




205 4 / Create an account to join the discussion.
ArjenLissenberg
Follow / 14 May 2018 at 6:28pm / Toad Data Point

2 questions Query Builder: filter on filed in left outer join in diagram...

Expand content
We are moving away from Hyperion Brio to TDP for some reports. The way that Brio worked was that, when putting a limit/filter in a field from a table that had an outer connection to the rest of the statement...

We are moving away from Hyperion Brio to TDP for some reports. The way that Brio worked was that, when putting a limit/filter in a field from a table that had an outer connection to the rest of the statement, the outer join would be taken into account (only apply filter if there is a field to be selected). In the diagram window of the query builder a filet/where clause entered in the column/field bar acts as a filter on the entire results.

I know how to fix this in the SQL statement (adding (+) after the fieldname, of defining a "custom" where clause formula). However,before teaching users that have less knowledge of SQL statements and are used to Brio, I wanted to know whether there is a "visual" non SQl statement writing way to apply "where clauses" only to the returned data in an outer join and not to an entire result-set.

Second question is about the Visualize Query option. This option seems to add a lot of "(" and ")" symbols in the statement, even to such account that the statement does not execute any more. I found this when an "IN (X,Y)"-clause in a Where clause  got turned into "IN ((X,Y))" which blocked the execution of the SQL:

WHERE (CASE WHEN table.field IN (X,Y) THEN table.field2 = 'A' ELSE table.field2 = 'B')

turned into 

WHERE (CASE WHEN table.field IN ((X,Y)) THEN table.field2 = 'A' ELSE table.field2 = 'B')

Any way this can be turned off or edited/curbed?

Unanswered 50 0 / Create an account to join the discussion.
AleemCh
Follow / 2 May 2018 at 6:01pm / Toad Data Point
Latest post by on 3 May 2018 at 7:10pm

TDP Automation scheduling through batch file

Expand content
Hi, We are in the very early stages to automating our tasks. A post I read somewhere suggested to use batch file for scheduling tasks so that when TDP is upgraded, we don't have to change program...

Hi,

We are in the very early stages to automating our tasks. A post I read somewhere suggested to use batch file for scheduling tasks so that when TDP is upgraded, we don't have to change program location for each schedule individually. Which makes sense.

So I tried to run a task through batch file, the task itself runs fine in TDP interface as well as from DOS (calling through .bat file). However when I call the .bat file from task scheduler it does not seem to work.

In task manager I do see a new TDP process starts but takes forever and does not even create a log file of the Automation.

Here are the main components:

.bat file (Launch_TDP.bat) contents (requires one argument -fully qualified .tas filename)

echo Toad Data Point 4.3 launched on %date% at %time% for %1 >> "C:\Support\ToadAutomation\Automaion_Log.log\TDP_Launch.log"

CD "C:\Program Files\Quest Software\Toad Data Point 4.3"
"C:\Program Files\Quest Software\Toad Data Point 4.3\toad.exe" -batch=true %1

echo ... and Ended on %date% at %time% >> "C:\Support\ToadAutomation\Automaion_Log.log\TDP_Launch.log"
echo -------------------------------- >> "C:\Support\ToadAutomation\Automaion_Log.log\TDP_Launch.log"

Windows Task scheduler:

Program/Script: C:\ToadAutomation\Launch_TDP.bat

Arguments: C:\ToadAutomation\EN_KO173.tas

Start in: C:\Program Files\Quest Software\Toad Data Point 4.3

Would much appreciate any tips/suggestions

Thanks,

Aleem

346 2 / Create an account to join the discussion.
ajmoehle
Follow / 1 May 2018 at 7:30pm / Toad Data Point
Latest post by on 3 May 2018 at 6:15pm

Excel Formula Errors must reset Ignored Formula Errors

Expand content
I recently upgraded to 32bit TDP Pro 4.3.0.718. Everytime I export data out of a Toad (Oracle: Banner) to an Excel file I run into an error in formula issues. In order for me to reset the data types correctly...

I recently upgraded to 32bit TDP Pro 4.3.0.718. Everytime I export data out of a Toad (Oracle: Banner) to an Excel file I run into an error in formula issues. In order for me to reset the data types correctly, I have to do the following, so that the data types I transfer into other Excel workbooks are read correctly . Go to the File> Options>Formulas>Section: Error Checking>Click:Reset Ignored Errors. Do I have a setting that I misconfigured? 

380 3 / Create an account to join the discussion.
ian.schroen
Follow / 1 May 2018 at 7:19pm / Toad Data Point
Latest post by on 3 May 2018 at 1:51pm

Recent Items Not Opening on Startup

Expand content
I had been using Toad Data Point 3.4 and 3.6, and in those versions, if you had open SQL editor windows that you saved, and then exited Toad with those editors still open, when you next launched toad those...

I had been using Toad Data Point 3.4 and 3.6, and in those versions, if you had open SQL editor windows that you saved, and then exited Toad with those editors still open, when you next launched toad those editors would re-open automatically (i.e., if you have three editor tabs opened, then exited Toad, when re-opening toad, those same three editor tabs would still be there).

I recently got a new machine and installed Toad Data Point 4.2.1. This feature seems to no longer be enabled (or, and I hope this is not the case, the feature is no longer there). The featured items tab has a little box for recent documents, and likewise the file menu has recent documents, but you would have to reopen each item manually.

Is this the new normal I need to deal with, or is there a way I can configure toad so it remembers which editors I had open last session?

Answered 352 3 / Create an account to join the discussion.
AleemCh
Follow / 1 May 2018 at 6:33pm / Toad Data Point
Latest post by on 2 May 2018 at 3:14pm

"Run Program" activity and return value

Expand content
Hi, I am trying capture return value from an external program (Java). The program returns expected values on command prompt and in TDP, however it ends with a failure. In "Run Program" activity...

Hi,

I am trying capture return value from an external program (Java). The program returns expected values on command prompt and in TDP, however it ends with a failure.

In "Run Program" activity, Return code is to be assigned to "Run_1_Code" variable. Then I have if condition to see what value is returned. But the execution fails at "Run Program" activity.

Any suggestions/ideas.

Thanks,

330 1 / Create an account to join the discussion.
InfoMan
Follow / 19 Apr 2018 at 9:54pm / Toad Data Point
Latest post by on 1 May 2018 at 7:37pm

Calculated Column returns as a string instead of a number value

Expand content
I thought that his was fixed in an earlier version of TDP, but this gremlin has struck me again. I have created a calculated column that is a very simple formula for Gross Margin Dollars ( table1.Revenue...

I thought that his was fixed in an earlier version of TDP, but this gremlin has struck me again.  I have created a calculated column that is a very simple formula for Gross Margin Dollars ( table1.Revenue - table1.COGS ).  I ran this query several times throughout the day, and now it has decided that the result will be string value instead of a numerical value.  This means that when I send the results to a Pivot Grid, the column will NOT behave as expected with an aggregation...no summing, averaging, etc....just a zero.  How can this be fixed?

Here is a screen shot showing how the values are left aligned (as one would expect for text/strings), instead of right-aligned.

Toad Data Point 4.3.0.718 (32 bit)

Unanswered 770 4 / Create an account to join the discussion.
AleemCh
Follow / 23 Apr 2018 at 9:30pm / Toad Data Point
Latest post by on 1 May 2018 at 7:25pm

Check file presence

Expand content
Hi, Sorry for a small question, but I could find a task that I could use to see if a file (.csv) exists. Also if the file is present, I want to check if the file is for the current date which is first...

Hi,

Sorry for a small question, but I could find a task that I could use to see if a file (.csv) exists.

Also if the file is present, I want to check if the file is for the current date which is first value in the first row.

Thanks,

Aleem

679 6 / Create an account to join the discussion.
InfoMan
Follow / 26 Apr 2018 at 3:55pm / Toad Data Point
Latest post by on 1 May 2018 at 3:53pm

Exporting 8.1MM row Dataset

Expand content
I'm having trouble exporting an 8.1 million row dataset. Tried going to Excel, it says that it is successful, but the resulting file is 4 KB...so that's no good. Tried going to tab delimited, but...

I'm having trouble exporting an 8.1 million row dataset.  Tried going to Excel, it says that it is successful, but the resulting file is 4 KB...so that's no good.  Tried going to tab delimited, but it errors right-away and stops.

Toad Data Point 4.3.0.718 (32 bit)

Log from tab delimited attempt...

Saved the query with results, closed Toad and restarted...same result.

Now trying sending to Local Storage and exporting from there...maybe that will work.

Answered 583 4 / Create an account to join the discussion.
rmdat2003
Follow / 30 Apr 2018 at 10:40pm / Toad Data Point

Object reference not set to instance of object

Expand content
I see this error using command line automation, but have been able to reproduce in the GUI also. The scenario is that under command line automation, I get an exit code 999, when a comparison between two...

I see this error using command line automation, but have been able to reproduce in the GUI also. The scenario is that under command line automation, I get an exit code 999, when a comparison between two csv file is done. A dcp file is used to compare the files. Now, in the GUI version I am able to get the same ,by opening the dcp file, closing, opening repeatedly and intermittently I get an error "Object reference not set to instance of object". It seems that something is caused the files to not properly load when the dcp file loads, but this happens somewhat randomly.

252 0 / Create an account to join the discussion.
marcia.m
Follow / 18 Apr 2018 at 1:25am / Toad Data Point
Latest post by on 23 Apr 2018 at 11:39pm

Full Profiling on Hive generic ODBC result set not working since migration...

Expand content
I recently migrated from TDP Pro 3.8 to TDP Pro 4.2 64-bit. I find that when I have query results from a Hive query, and I click the Profiling tab, I see the summary data, but when I click the "Full...

I recently migrated from TDP Pro 3.8 to   TDP Pro 4.2 64-bit.

I find that when I have query results from a Hive query, and I click the Profiling tab, I see the summary data, but when I click the "Full Profiling" link on top-right, nothing happens.

Full profiling works OK in 4.2 when I had results from an Oracle query, however. 

This worked perfectly fine in 3.8, for Hive queries (32-bit driver), I did it a lot.  But now 4.2, Hive result sets are not sending to full profiling.    ??  any advice?

Best I can determine, I get this exception, when I click the link:

New exception: 4/17/2018 7:17:42 PM

Quest.Toad.Exceptions.IgnoredException: Could not resolve connection for TRL: odbc://1A+Hive+IT+KERB+BW3/

Stack trace:

at Quest.Toad.Trl.TrlScheme.ResolveConnection(String trl, String xml, Boolean allowOpen, Boolean visible)
at Quest.Toad.Profiling.ToadProfileSource.GetConnection()
at Quest.Toad.Profiling.FullProfileOptionForm.set_ProfileSource(ToadProfileSource value)
at Quest.Toad.Profiling.FullProfileOptionForm.ProfileData(FastTable table, ToadDataAdapter adapter)
at Quest.Toad.Profiling.ProfileOverviewControl.fullProfileItem_ItemClick(Object sender, ItemClickEventArgs e)
at DevExpress.XtraBars.BarItem.OnClick(BarItemLink link)
at DevExpress.XtraBars.BarButtonItem.OnClick(BarItemLink link)
at DevExpress.XtraBars.BarItemLink.OnLinkClick()
at DevExpress.XtraBars.BarButtonItemLink.OnLinkAction(BarLinkAction action, Object actionArgs)
at DevExpress.XtraBars.ViewInfo.BarSelectionInfo.UnPressLink(BarItemLink link)
at DevExpress.XtraBars.Controls.CustomLinksControl.OnMouseUp(MouseEventArgs e)
at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
at System.Windows.Forms.Control.WndProc(Message& m)
at DevExpress.XtraBars.Controls.DockedBarControl.WndProc(Message& msg)
at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)

Thanks

866 7 / Create an account to join the discussion.
rmdat2003
Follow / 16 Apr 2018 at 9:17pm / Toad Data Point
Latest post by on 20 Apr 2018 at 12:13pm

Automate dcp file does not remember connection

Expand content
I create a dcp file for automation, which has connections to two Excel spreadsheets that has different values. Toad properly shows the differences, but when I save the dcp file, and reload, the differences...

I create a dcp file for automation, which has connections to two Excel spreadsheets that has different values. Toad properly shows the differences, but when I save the dcp file, and reload, the differences do not get shown - I have to recreate the connection and mapping again. I have tried the auto connect on and off, but the only way this works is to recreate the entire mapping, which is of course no use for automation scripts. What is the solution please ?

836 3 / Create an account to join the discussion.
MR BARKER
Follow / 17 Apr 2018 at 10:59pm / Toad Data Point
Latest post by on 19 Apr 2018 at 6:24pm

How to export SQL script to Qlik Sense QVD

Expand content
I've written my SQL query and want to export it to a QVD file so that I can load it directly into Qlik Sense, is this possible? I only see options to export to Excel or csv. Thanks

I've written my SQL query and want to export it to a QVD file so that I can load it directly into Qlik Sense, is this possible? I only see options to export to Excel or csv.

Thanks

Unanswered 799 3 / Create an account to join the discussion.
tracy.bakowski
Follow / 18 Apr 2018 at 4:08pm / Toad Data Point
Latest post by on 18 Apr 2018 at 8:43pm

Cross Connection Queries

Expand content
Hello, I am trying to build a simple query connecting a SQL server table and an Oracle table. I've found many items in the help and videos saying this can be done. However, I am unable to find the...

Hello, I am trying to build a simple query connecting a SQL server table and an Oracle table. I've found many items in the help and videos saying this can be done.  However, I am unable to find the option to build a cross connection query.  We are using TOAD Data point version 4.1.0.226.  Any guidance is greatly appreciated. Thank you!

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