Today, I encountered two simple errors when I was trying to to run a simple sql file on my Sybase database. Though the errors are not difficult ones. But it costed some time to find the causes of errors during my little maintenance window...


The task I wanted to do is to run the a sql file t1.sql  and it just create a table on the database db1...simple enough.

test_host$ cat t1.sql
IF OBJECT_ID('test1') IS NOT NULL
    PRINT '<<< Table test1 already has existed>>>'
ELSE
    PRINT '<<< Creating table test1 >>>'
go
create table test1(
col1 int,
col2 varchar(200))
LOCK DATAROWS
go


I tried run it with below command:
isql -Uuser1 -SSYB_SERVER1 -Ppassword1  -i t1.sql  -o t1.out

test_host$ ls -ltr t*

-rwxr-x---   1 sybase   sybase       214 Apr 14 11:54 t1.sql
-rw-r-----   1 sybase   sybase         0 Apr 14 11:56 t1.out

Here I wonder why the t1.out would be zero bytes. isql didnt tell whether the command is successful, nor failed....
Then I had spent some time to figure out the reasons. And everything comes clear until I run the vi command with the t1.sql file.


test_host$view t1.sql
IF OBJECT_ID('test1') IS NOT NULL^M
    PRINT '<<< Table test1 already has existed>>>'^M
ELSE^M
    PRINT '<<< Creating table test1 >>>'^M
go^M
^M
create table test1(^M
col1 int,^M
col2 varchar(200))^M
LOCK DATAROWS^M
go^M
^M
~

The extra ^M character at the end of line means the t1.sql in dos-style, and isql in unix cannot run with this style.

So, the next thing is simple, convert the file to unix format and run it again.

test_host$ dos2unix t1.sql >t2.sql
could not open /dev/kbd to get keyboard type US keyboard assumed
could not get keyboard type US keyboard assumed
test_host$ ls -ltr t*
total 208
-rwxr-x---   1 sybase   sybase       214 Apr 14 11:54 t1.sql
-rw-r-----   1 sybase   sybase         0 Apr 14 11:56 t1.out
-rw-r-----   1 sybase   sybase       202 Apr 14 11:58 t2.sql


test_host$ isql -Uuser1 -SSYB_SERVER1 -Ppassword1 -i t2.sql  -o t2.out
test_host$ ls -la t*
-rw-r-----   1 sybase   sybase         0 Apr 14 11:56 t.out
-rwxr-x---   1 sybase   sybase       214 Apr 14 11:54 t1.sql
-rw-r-----   1 sybase   sybase        29 Apr 14 12:31 t2.out
-rw-r-----   1 sybase   sybase       202 Apr 14 11:58 t2.sql
test_host$ cat t2.out
<<< Creating table test1 >>>

The t2.sql can be run successfully. Here I have learnt 2 lessons:  1) Don't trust the auto file mode in ftp client tool, it sometimes cannot choose conversion correctly.
2) Some tools, e.g. cat and more, cannot show the special characters in files. To view special characters in a file, user vi or view command instead.