freeTDS bash: Executing sql queries in Microsoft SQL server

Question

I am able to connect to a Microsoft SQL Server 2008 instance via a Mint Linux VM using freeTSD and command line to execute sql statements on it. Now I want automate this in a bash script. I am able to successfully login in my bash script:

TDSVER=8.0 tsql -H servername -p 1433 -D dbadmin -U domain\\Administrator -P password

I then have my SQL query:

USE dbname GO delete from schema.tableA where ID > 5 GO delete from schema.tableB where ID > 5 GO delete from schema.tableC where ID > 5 GO exit

This works when doing manually via freeTSD command line, but not when I put in bash file. I followed this post: freeTSD & bash.

Here is my bash script sample:

echo "USE dbname GO delete from schema.tableA where userid > 5 go delete from schema.tableB where userid > 5 go delete from schema.tableC where ID > 5 GO exit" > tempfile | TDSVER=8.0 tsql -H servername -p 1433 -D dbname -U domain\\Administrator -P password < tempfile

the output of the bash script is:

locale is "en_US.UTF-8"
locale charset is "UTF-8"
Default database being set to sbdb
1> 2> 3> 4> 5> 6> 7> 8>

and then the rest of my script is executed.

Can someone give me a step by step answer to my problem ?

score 6 · Accepted Answer · edited Nov 14 '12 at 22:41

6

I'm not sure how your sample can work at all.

Here is my bash script sample:

echo "USE dbname .... exit" > tempfile | TDSVER=8.0 tsql -H servername -p 1433 -D dbname -U domain\\Administrator -P password < tempfile
# ------------------------------------^^^^ ---- pipe char?

Try using a ';' char.

echo "USE dbname .... exit" > tempfile ; TDSVER=8.0 tsql -H servername -p 1433 -D dbname -U domain\\Administrator -P password < tempfile
# ------------------------------------^^^^ ---- semi-colon

Better yet, use shell's "here documents".

TDSVER=8.0 tsql -H servername -p 1433 -D dbname -U domain\\Administrator -P password <<EOS
     USE dbname 
     GO 
     delete from schema.tableA where userid > 5 
     go 
     delete from schema.tableB where userid > 5 
     go 
     delete from schema.tableC where ID > 5 
     GO 
     exit
  EOS

IHTH.

Current command line input:

echo "delete from table where userid > 5
go
delete from table where userid > 5
go
delete from table where ID > 5
GO
exit" < /tmp/tempfile; TDSDUMP=/tmp/freetds.log TDSVER=8.0 tsql -H servername -p 1433 -D dbname -U Administrator -P password <<EOS

edited Nov 14 '12 at 22:41

BryanH

5,826
3
34
47

answered Nov 14 '12 at 21:45

shellter

36,525
7
83
90

you get that "USE dbname ... as an output" ? I modified as you said including '\r\n' as I am sshing using cygwin. I get a result of: '>' which looks like some type of prompt, its not the freeTDS as it usually has a '#' appended and accepts sql commands... echo "delete from table where userid > 5\r\n go\r\n delete from table where userid > 5\r\n go\r\n delete from table where ID > 5\r\n GO\r\n exit\r\n" < /tmp/tempfile ; TDSDUMP=/tmp/freetds.log TDSVER=8.0 tsql -H servername -p 1433 -D dbname -U Administrator -P password < – Nov 14 '12 at 22:24
See edits, sorry. But No, not output, it is input to tsql via shell here documents. Sorry, but mpossible to read the rest of your comment, can you update your question instead? (Leaving for the day, may be able to look at your response from home, but more likely not until tomorrow.) GOod luck. – shellter Nov 14 '12 at 22:32
I modified, any ideas ? @Shellter – Nov 16 '12 at 22:00
I don't see any significant difference from before. 1. Do you understand about shell "here documents", do you see the `< – shellter Nov 16 '12 at 22:51
3. the bottom block in my answer was added/edited into my answer by BryanH. I don't think that is the right way to try to solve your problem, and with an `echo ....` reading IN from ` – shellter Nov 16 '12 at 22:53

score 3 · Answer 2 · edited Dec 14 '15 at 21:46

3

Old thread but this seemed to work..

printf  "use mydbname\ngo\nselect * from mytable\ngo\nexit\n"|tsql -I freetds.conf -S profileName -U user -P 'password'

1> 2> 1> 2> ID  stringtest  integertest
1   test    50
2   teststring2 60
3   test3   70
(3 rows affected)

edited Dec 14 '15 at 21:46

Anders Abel

67,989
17
150
217

answered Dec 14 '15 at 21:22

rob

31
2

score 0 · Answer 3 · answered Oct 29 '12 at 15:50

try

 echo "USE dbname\n GO\n delete from schema.tableA where ID > 5\n GO\n delete from schema.tableB userid > 5\n go\n delete from schema.tableC where ID > 5\n GO\n exit\n"

the rest of this string is stuff that maybe works

and try

 echo "USE dbname;\n delete from schema.tableA where ID > 5;\n delete from schema.tableB userid > 5;\n delete from schema.tableC where ID > 5;\n exit\n"

and try

 echo "USE dbname; delete from schema.tableA where ID > 5; delete from schema.tableB userid > 5; delete from schema.tableC where ID > 5; exit"

if you are using odbc, i recommend the second trial. if you are sending commands to sql with a "go" word as sql sentences separator, maybe the first one is better. maybe the third one... who knows... only trial and error can tell...

none of these worked, I think the problem is that I need mimic the 'enter' key in freeTDS so that the command will exececute, or the correct delimiter to goto next line... — , Nov 14 '12 at 17:49
@ebel : "mimic enter key", you say you're using bash, any chance you're using bash in a Windows environment (maybe even Cygwin?). Try changing "\n"s above to "\r\n". Good luck. — shellter, Nov 14 '12 at 21:47

freeTDS bash: Executing sql queries in Microsoft SQL server

3 Answers3

Linked