Thursday, June 7, 2012

How to start SQL assistant using Batch Mode or using Command Prompt?

How to start SQL assistant using Batch Mode or using Command Prompt.

This is indeed interesting as there is not much documentation on how you can use SQL assistant via Batch mode or using Command prompt in Windows.

For most of batch mode / Command prompt (interactive mode) we would use Bteq due to its flexibity and ease of use in either Windows or UNIX systems.

There are some cases where you would like to use SQL assistant to run the set of queries over a period of time or for repetitive tasks. In such cases we could use following commands.

Go to Windows start --> Run -->
Enter following command

Sqla -c Test1 -f "c:\my queries\emplst.sql” -e "c:\my queries\emplst_output.log"

Explanation
Sqla
It is used to invoke sql assistant from its home directory.

-c Test1
-c is used to establish connection to Test1 server. 
Please make sure to setup ODBC connection string using ODBC administrator & enter IP address, Username & password.

-f "c:\my queries\emplst.sql" 
The parameter –f is used to open a file in the given path directly on to Sql assistant.
In above scenario, we are trying to open emplst.sql which is present in c:\my queries

As a general rule I would enclose filenames in double quotes just in case they contain spaces or special characters.

-e "c:\my queries\emplst_output.log"
The parameter –e is used to export the results/ output into the given file.

Sometimes we need to use –p ODBC or –p TD depending on version of TD sql assistant.
I would suggest to use –p parameter whenever running the command as to identify the Data source as either ODBC or Teradata.net


One could create a reusable batch file in windows and run this command with our sql file and record output in log file for reporting or whatever you want to do!!!
Steps:
1.      Create file opensqla.bat
2.      Enter following command
Start Sqla -c Test1 -f "c:\my queries\emplst.sql” -e "c:\my queries\emplst_output.log"