TSQL SCRIPT IN A BAT FILE

Question

Post reply

TSQL SCRIPT IN A BAT FILE

Waheed Ansari

Grasshopper

Points: 13
More actions
March 1, 2005 at 1:13 pm

#109627

i want to know how to run a tsql script in windows bat file. Thanks

Waheed

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Zachary Rogers Grasshopper Points: 14 More actions · Answer 1

Waheed -

This is done using a command line program, 'isql', which is installed with the SQL Tools.

Here's an example -

isql -Usa -P -Q"select cast(name as char(20)) from master..sysusers" -n -owork.out

This will list the user names (or the first 20 characters of each) and send it to a file called 'work.out'. It also uses the 'sa' account with no password.

It is possible to set it to use a trusted Windows account (the -E parameter).

From a command prompt run 'isql /?' to see all the options. Be aware, with the parameters, letter case is important. eg '-q' is different from '-Q'.

Hope this helps.

Zack Rogers

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 2

'isql' is still supported as a 'deprocated feature' (it may go away at any time). It is recommended that you use 'osql' instead (the flags are pretty much the same) for SQL 2000. Now in SQL 2005, the utility is sqlcmd'.

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

Wayne West SSC-Insane Points: 22586 More actions · Answer 3

I used isql extensively for a payroll system that had pretty complicated validation conditions that couldn't easily be enforced through triggers and keys, so I wrote a series of isql commands that would invoke stored procedures and return the record sets of violators. SO, you might want to use the output redirectors (> and >&gt to send the results to a text file.

-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]

Zachary Rogers Grasshopper Points: 14 More actions · Answer 4

... the -o <filename> option will direct the output to whatever file you want.

I also use the the -w <num> option to set the width because the default is not wide enough most of the time.

Another trick I use is to wrap the scripts in a compiled basic program (I use Liberty basic) and call isql from within the program passing the paramters to it.

It is an easy and quick way to distribute simple reports to users without opening SQL up to problems.