Running a stored procedure from a batch file

[btacy]

hi,
i wrote a stored procedure for a process. it needs to be run as part of a series of other processes by a user and so i'm thinking of calling it from a batch file to make it as easy as possible for the user. the problem is i don't know how to go about it. can anyone help please? i'm using a SQL 2000.

 

[ptheriault]

use isql in your bat file to call .sql file that has your sql commmand.

like this.

bat file
isql -Usa -Ppassword -Sserver -i "c:\path_to.sql" -o "c:\path_for_out_put.out"

sql file

exec procedure_name

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[btacy]

thanks for responding Paul.

the SQL server uses windows authentication. do i include the windows user name and password in the file? also there isn't a need for an output file, can that be excluded without any consequences?

 

[mutley1]

Use the -E switch in the isql command line. that implies "trusted connection" (so you don't need "-Usa -Ppassword". That just means you need to have the owner of the scheduled task (or SQL job that calls the batch file) as a domain account with the correct privelidges on the server to run stuff.

Also, depending on the stored proc, you might need a -d switch to specify the database to use as well. Look up isql in dos with "isql ?"

HTH,

M.