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,

driis · Accepted Answer · 2010-07-15T19:07:38.087

16

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.

edited Jul 15 '10 at 19:07

answered Jul 15 '10 at 19:02

driis

161,458
45
265
341

+1: I was too slow, additional link: http://msdn.microsoft.com/en-us/library/ms165702.aspx – OMG Ponies Jul 15 '10 at 19:04
Good link, added it to the answer. – driis Jul 15 '10 at 19:09
Good day sir, But how is the whole coding on how to create the batch file? – WTFZane Mar 15 '17 at 01:01

score 4 · Answer 2 · answered Jul 15 '10 at 19:03

4

sqlcmd -S 127.0.0.1 /E -i MySqlScript.sql

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

answered Jul 15 '10 at 19:03

DaveShaw

52,123
16
112
141

score 3 · Answer 3 · answered Jan 13 '12 at 00:07

If you want a much better answer, here it is:

@echo off
SETLOCAL ENABLEDELAYEDEXPANSION
:: batch file for sql query
SET FIELDVAL=Empty
SET DBNAME=MYDB
SET SQLSTRING=SELECT column_name(s)^
 FROM table_name1^
 INNER JOIN table_name2^
 ON table_name1.column_name=table_name2.column_name^
 AND table_name2.field=%FIELDVAL%
ECHO !SQLSTRING!

ECHO.
sqlcmd.exe -b -S localhost -E -d !DBNAME! -Q "!SQLSTRING!" -W
ECHO Query is done. Hit any key to close this window....
pause>nul

score 0 · Answer 4 · answered Jul 15 '10 at 19:02

0

Start > Run > Type Cmd.

MyDrive:\Mypath\Mybat.bat

=)

answered Jul 15 '10 at 19:02

ajdams

2,276
14
20

How do I run a script using a BAT file?

4 Answers4

Linked