Search code examples
perlsql-server-2005odbcfreetds

SQL statement doesn't work for MS SQL 2005

  • SQL client: Ubuntu 14.04LTS
  • DB Server: MS SQL server 2005, accessing via odbc and Freetds. Works fine for my other MS SQL 2005 server.
  • isql 2.2.14
  • Freetds 0.91
  • Text editor: Notepad++ 6.5.5 for windows 7 is editing my .sql file on Ubuntu via a Samba share.
  • No I can't upgrade anything, I'm stuck with what I have.

This has me stumped. I copied and pasted an SQL statement from i-net Clear Reports, and pasted it into a Windows editor, which is editing an Ubuntu .sql file, which is just a text file. I'm already connecting to another MS SQL 2005 server with no problem via isql and Perl. So I was having problems with a big SQL statement with about 10 joins, and simplified the statement to these multiple lines: 

SELECT PKG.SO_ID
FROM
   PACKAGES AS PKG
WHERE
    PKG.tracking_no = '640038823199'
;

This file consists of multiple lines with unix line endings. My isql command is: cat test1.sql | isql dsnname 'domain\username' password -v -b.

I used -b for batch mode because I'm sending input to isql via a file. The errors start immediately on the first line, but I double checked the syntax and for SQL Server 2005 the 'AS' is optional in 'PACKAGES AS PKG'. 

[37000][unixODBC][FreeTDS][SQL Server]Statement(s) could not be prepared.
[37000][unixODBC][FreeTDS][SQL Server]The multi-part identifier "PKG.SO_ID" could not be bound.
[ISQL]ERROR: Could not SQLExecute
[37000][unixODBC][FreeTDS][SQL Server]Incorrect syntax near the keyword 'FROM'.
[37000][unixODBC][FreeTDS][SQL Server]Statement(s) could not be prepared.
[ISQL]ERROR: Could not SQLExecute
[37000][unixODBC][FreeTDS][SQL Server]Incorrect syntax near the keyword 'AS'.
[37000][unixODBC][FreeTDS][SQL Server]Statement(s) could not be prepared.
[ISQL]ERROR: Could not SQLExecute
[37000][unixODBC][FreeTDS][SQL Server]Incorrect syntax near the keyword 'WHERE'.
[37000][unixODBC][FreeTDS][SQL Server]Statement(s) could not be prepared.
[ISQL]ERROR: Could not SQLExecute
[37000][unixODBC][FreeTDS][SQL Server]Incorrect syntax near '='.
[37000][unixODBC][FreeTDS][SQL Server]Statement(s) could not be prepared.
[ISQL]ERROR: Could not SQLExecute
  • What in the world is going on here?
  • Is it possible isql or MS SQL has a problem with tabs in the test1.sql file? There is one tab before PKG.tracking_no.
  • I'm assuming a .sql file is just a text file with SQL statements on it, spanning one or more lines.

Thank you.

Solution

  • By default isql treats each line as its own query, so you need to merge the lines all onto the single line.

    isql is only a simple test app, not designed for more than proving the connection and a quick simple way of issuing queries.

    the -n option in later builds of isql will go closer to what you want, looking for a line that ends in a semicolon before calling SQLPrepare.