6

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 ?

Improve this question
1
  • fixed some hyperlink problems :) Commented Oct 27, 2012 at 0:20

3 Answers 3

Reset to default
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
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

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 <<EOS. I am close I can feel it!!
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.
I modified, any ideas ? @Shellter
I don't see any significant difference from before. 1. Do you understand about shell "here documents", do you see the <<EOS at the end of my line that starts with TDSVER=8.0 tsql ... ? That is a key technique. 2. but I'm not sure this will solve your problem, based on answers you included in your other posts about problems with TDS. Something about using a lower version number looked like it might help, but you might need to install the lower version number.
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 </tmp/tempfile it DOESN'T make sense. 4. I'm can try to help you, but I need better quality of information on what you're trying and what are the error messages. Please edit your main question above, and use the {} format tool liberally to make easy to read questions. 5. Good luck! ;-)
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)
Improve this answer

Comments

0

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...

Improve this answer

2 Comments

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...
@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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.