This article will introduce the new features and UI enhancements that have been added to Teradata SQL Assistant 14.10, and highlight some existing but lesser known features.

This article assumes that you are already familiar with the features in SQL Assistant 14.01. If not, you may wish to read the following articles first:

Additional information in the Database Explorer Tree

A new option has been added to the DB Tree page of the Options screen:
    Display Triggers, Join Indexes, Materialized Views and UDTs

If you check this option a Triggers node will be added below each table and an Indexes node will be added below each database.
In addition, a User-Defined Types node will be added under the SysUDTLib database.

Note - The Indexes node will be called Materialized Views when connected to an Oracle system, or Materialized Query Tables when connected to DB2.

The Indexes node will display all Join and Hash Indexes in the database when it is expanded. Each item will display its name and the table[s] that it is defined on.

As with the Tables and Views nodes, if there are no objects of that type the Indexes, or Triggers, node will be removed when you attempt to expand it.

Earlier versions of SQL Assistant already provide additional information about some tables.

  • Global Temporary Tables will display a 'grey' icon and may be displayed in a different color [this is a user option]
  • Queue Tables will display a different icon [Only when connected using Teradata.Net]

 

Other enhancements in 14.10

When returning CLOB columns you can now specify the file encoding (UTF8, Unicode, ASCII) on the LOB Information dialog.

When exporting data to a Microsoft Access table you may now specify whether a Row Number column will be included in the output table.

When exporting data to a text file, or returning CLOBs, you may now specify whether a Byte Order Mark (BOM) is written at the start of the file.

If text you copy from Microsoft Word, Outlook, or other applications, contains 'Smart Quotes' these will be automatically converted to regular quotes when you paste the text into SQL Assistant. This will avoid syntax errors when you execute the Query.

We now support systems that have only Microsoft .Net Framework 4.0 or 4.5 installed. You will no longer be required to install Framework 2.0 in these cases.

Using Teradata.Net instead of ODBC

Microsoft's .Net Data Provider for ODBC was intended as a temporary way for applications to connect to a database through ODBC while the vendor had time to write a database specific .Net Data Provider for that database.

As a result of this Microsoft implemented only the bare minimum of ODBC functionality. This means that some data types are not fully supported and some areas of functionality are not supported at all. It strongly recommended that all users use Teradata.Net rather than ODBC when they connect to a Teradata data source.

Using Teradata.Net will provide the following additional or improved features:

  • Data retrieval may be up to 8 times faster
  • Data import may be up to 70 times faster
  • You may directly edit the data in a table using an editable grid
  • The approximate location of syntax errors will be highlighted after a query fails
  • Macros [or parallel query execution] that return no rows from one select but return rows from another select will work correctly
  • Time/Timestamp columns with fractional seconds or Time Zones will be displayed in full
  • Decimal values with more than 28 digits will display correctly
  • Returning Interval columns will not cause conflicts with certain other column types
  • CLOBs may be returned using any character set
  • Queue Tables will be displayed using a different icon in the database tree
  • All User Defined Functions will be displayed in the database tree

Note that the first time a user launches SQL Assistant 13.0 or higher the application will automatically create a DataSources.config file from the Teradata ODBC entries in their registry.

Additionally, a customer DBA or IT staff member may pre-create the Teradata.Net data sources by creating a DataSources.config file and including it as part of the install. This file can also be sent to users after performing a standard install.

This file may be installed either in the user's private branch or under the 'All Users' branch.
On Windows 7 the locations would be:
    \Users\<username>\AppData\Roaming\Teradata\SQL Assistant
    \ProgramData\Teradata\SQL Assistant

Adding support for an 'unsupported' database

SQL Assistant should work with any database that provides an ODBC interface. However the application needs to know a few things about that database in order to work correctly.

That database specific information is stored in a file called Vendors.config which is located in:
    \ProgramData\Teradata\SQL Assistant

In general only a user with administrative rights will be able to change files in this path. Starting with SQL Assistant 14.10.0.4 the application will first look in the following directory:
    \Users\<username>\AppData\Roaming\Teradata\SQL Assistant
If the file is found there then this version will be used. Administrative rights are not required to change files in this directory.

For additional information on adding support for a new vendor database please refer to the Vendors.config topic in Appendix C of the user guide.

Conclusion

Here we have described the main enhancements in the latest release of SQL Assistant and a few features you may not have been aware of. I hope this article has made you eager to try the new version which is now available from the download center on T@YS.

 

Discussion
standalone 5 comments Joined 08/08
13 Aug 2013

download link please.
thanks

MikeDempsey 94 comments Joined 10/06
13 Aug 2013

You can download it from the T@YS download site. In TTU 14.10 you can no longer download individual packages. You have to download the complete 'Tools' package.
These packages are now released monthly.

karl_hughes 2 comments Joined 03/13
25 Sep 2013

In Ver 14.10 I can no longer highlight and execute SQL statements that are in commented-out areas, i.e. preceeded by '--' or between '/*' and '*/'.  I have placed SQL statements in Production SQL to enable quick debugging that now must be copied and executed separately.
Why was this change made? Can it optionally be changed back by DBAs?
 
 

Karl Hughes

MikeDempsey 94 comments Joined 10/06
02 Oct 2013

You must have just upgraded from 12.0.
13.x and 14.x parse the query "in place" so any SQL within a comment is seen as a comment ... and therefore not a valid piece of SQL.
In 15.0 we will switch to a new editor (see the editor in the new Visual Studio extension) and have rewritten the parsing so that this (and other) problems no longer occur.
Until 15.0 you will need to copy the part of the procedure you wish to execute into a new tab before executing it.
 

MartinJEd22 1 comment Joined 05/13
22 Oct 2013

Hi Mike,
 
There's one difference between TD SQL Assistant 13 and 14 I don't understand.
When I export (File-Export) as column of a table with a unicode varchar field as ANSI file, the character "£" gets changed into "?" in the export file in Version 13. In version 14 it stays "£". The funny thing is that in both versions the answerset has got the "£" in it.
I assume there's no check-box in the options or similar to control it?
Thanks for any help.

MikeDempsey 94 comments Joined 10/06
22 Oct 2013

I corrected the encoding used to create the Export file.
For the ANSI case I had previously not specified an encoding since I wanted Encoding.Default to be used.  
However Microsoft in their great wisdom do not use Encoding.Default when you omit the encoding parameter. I can't remember what they do use but that is why it was not working in 13.0.  (Apparently the 'default' is not 'Default'!)

tomkent 3 comments Joined 10/11
15 Nov 2013

Will or when will "With" (Summary) work with Teradata .Net?

MikeDempsey 94 comments Joined 10/06
15 Nov 2013

There are no current plans to implement support for the WITH summary clause in the .Net Data Provider.
It is felt that simply ignoring the returned summary rows (as ODBC does) is a kind of data corruption. Therefore, if support is added it would need to be added in such a way as to return the summary data along with the detail rows. 

Ekladios 8 comments Joined 02/12
22 Jan 2014

Hi,
I need to display a message when any user starts up SQLA. Is it possible to direct me on where is the file that can be edited to display the start-up messageges?
Thanks,
Adel

MikeDempsey 94 comments Joined 10/06
22 Jan 2014

If you mean you want to change the startup message in the splash screen the only way to do that would be to provide a resource dll in which you had changed my original startup message.
I think that would only work if you are running on a system that is not set to US English locale. (The US English resource strings are embedded in the app itself.)

Ekladios 8 comments Joined 02/12
22 Jan 2014

Mike,
This will run in the UK, secure site. All what I need is to display a message
"This is a secure site, any unauthorised usage will be reported"
This message to be displayed if any user invokes/starts SQLA application. 
I would appreciate your help on this.
Thanks,
Adel

MikeDempsey 94 comments Joined 10/06
22 Jan 2014

Since you are not in the US you could do it but it will require a bit of work.
You would create a resource file for the en-GB locale and place it in a \en-GB sub-directory of the execution directory. (This assumes you can modify the application install process, or add an additional step after the install completes)
Email me directly at mike.dempsey@teradata.com for more details.

18 Jul 2014

In SQLA 14.10 is there a configuration option a user can use to "SET TIME ZONE" instead of having to do this for every session?

MikeDempsey 94 comments Joined 10/06
21 Jul 2014

SQLA doesn't have an option to do that directly.
A user can be defined to have a Startup string which is executed whenever you connect a session, but unfortunately it only gets triggered when you connect via BTEQ, or with special settings with CLI or JDBC. [Never with ODBC or .Net] 
If you only connect to one data source you could change the shortcut you use to launch SQLA to automatically connect and execute the SET command. That would only work for the first session you connect of course, but the command would be something like:
Startup -c "dsn\user\pwd" -s "SET TIME ZONE Interval '05:00' Hour To Minute"
You should also include -p Td or -p Odbc to tell it whether to connect using Teradata.net or ODBC.
 

22 Jul 2014

Thanks! Mike.

You must sign in to leave a comment.