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"

6 comments:

  1. how can you get error messages to be in the output file?

    ReplyDelete
    Replies
    1. Hi Dave,

      In sql assistant , Go to
      Tools -> Options -> Export and check last 3 boxes.

      This should work.

      Also make sure to check "continue on error " in sql options

      Br,
      Vinay SHet

      Delete
  2. Hi, how to run a sql without invoking the sqla window(like if I start the batch, I dont want to see the SQLA window and I want the output to be printed in the log file)? Please let me know...
    Thanks,
    Dinesh

    ReplyDelete
    Replies
    1. Hi,

      Please use bteq to do the same.

      put following in Bteq1.sql
      .logon SERVER/uname , pass
      ;
      .logoff


      then call the following from windows -> run CMD -> Bteq

      C:\ Bteq < Bteq1.sql > c:\logfile.txt

      Br
      Vinay SHet

      Delete
    2. please mention your sql statement in Bteq1.sql

      Delete
  3. how to invoke a query in unix(asking about interctive mode)

    ReplyDelete