Using Isql for SQL SERVER to get the table rows counts in UNIX shell script to

Shell Programming and Scripting
1

need to create shell script to read the table's name from file and connect SQL SERVER using isql (odbcunix)

i 'm able connect to database with below command line syntex but i could not get working in shell script with SQL and storing the row count in variable.

isql -v DSN USERNAME PASSWD

here is what i am looking for:

VARIABLE_NAME=`ISQL DSN USERNAME PWD SELECT COUNT(1) FROM $SRC_TABLE `
2

Isql commands affect the running of isql itself and do not affect the database or data in any way.

Examples

$ isql WebDB MyID MyPWD -w -b < My.sql

Connects to the WebDB as user MyID with password MyPWD, then
execute the commands in the My.sql file and returns the results
wrapped in HTML table. Each line in My.sql must contain exactly
1 SQL command, except for the last line, which must be
blank (unless the -n option is specified).

-n Use the new line processing. (multiple lines of SQL, terminated with command GO).

3

Also, try the command "set nocount on" to suppress the feedback message from the query... (the pesky line that says something like "<N> rows affected" that shows up after each SQL statement)

$
$  cat -n fetch_count_1.sql
     1	use pubs2
     2	go
     3	select count(*) from sales
     4	go
$
$  VAR1=`isql dsn user password -w -b < fetch_count_1.sql`
$  echo $VAR1
30 (1 row affected)
$
$
$  cat -n fetch_count_2.sql
     1	use pubs2
     2	go
     3	set nocount on
     4	select count(*) from sales
     5	go
$
$  VAR2=`isql dsn user password -w -b < fetch_count_2.sql`
$  echo $VAR2
30
$
$
4

getting error message could not SQLexecute.

5

That doesn't tell me anything.

6

Thank you so much Tyler,

when i run the following command with out -w, it displaying the count with long warning message saying that
ODBC Data direct for SQL SERVER is exclusive to ETL tool. when i try to use it shell script it warning!

VAR2=`isql dsn user password -b < fetch_count_2.sql`
7

Why do you omit the "-w" switch?
What do you see if you do use the "-w" switch?

I don't see it in your post.

I don't see this in your post either.