how do i run a script using a BAT file

Question

i would like to have a BAT file open a sql server script

currently i have this code in the sql file:

declare @path varchar(255), @mydb varchar(50)
SELECT @mydb = 'timeclockplus'
select @path = 'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Backup\' + @mydb + '-' + convert(varchar(8),getdate(),112) + '.bak'
BACKUP DATABASE @mydb TO DISK = @path

how do i open this SQL file from a BAT file>??

i am currently trying to run it like this:

C:\Program Files\Microsoft SQL Server\80\Tools\Binn\osql -E -S Sql server-hl7\timeclockplus timeclockplus.sql -oresults.txt

but OSQL does not exist in the BINN directory,

Answer 1

You should invoke the sqlcmd command-line tool from your batch file. Assuming your sql file is "backup.sql", the command line would be something like:

sqlcmd -E -S yoursqlinstance -i backup.sql

-E uses trusted connection, replace with -U and -P if you need to specify a SQL username and password. See also this article with examples.

Answer 2

Start > Run > Type Cmd.

MyDrive:\Mypath\Mybat.bat

=)

Answer 3

sqlcmd -S 127.0.0.1 /E -i MySqlScript.sql

Replace /E with /U and /P if you don't have trusted connection