osql script run as a .bat file

Question

osql script run as a .bat file

am-244616

SSCarpal Tunnel

Points: 4936
More actions
October 19, 2005 at 9:07 am

#67583

Guys,
I have bunch of DML statements like below which I need to put together an osql script as .bat file so that it can be run by clicking the bat file
/*
truncate table employee
update employee
set tempid = 200
where id > 100
insert into employee
select * from tempemployee
*/
do you guys know how to proceed on this in the sense how do I get to osql from cmd prompt and run my tsql script and then eventually make the script as .bat file so that it can be run on click.
Thanks

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

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

Ray M SSC-Insane Points: 21093 More actions · Answer 1

Osql is in the C:\Program Files\Microsoft SQL Server\80\Tools\Binn folder, and this information should be in your "Path" environment variables after install.

Open cmd window and type osql have you tried?

just like any bat, just type in the correct osql command and parameters and it will work.

Save your script to a .sql file then execute the file in osql.

osql /E /S ServerName /d DatabaseName /i "C:\path\filename.sql"

Look up osql in books online.

am-244616 SSCarpal Tunnel Points: 4936 More actions · Answer 2

Ray,

I have tried running the script from osql it worked fine, the thing is I want to run the below cmd as a windows batch file. What it will do is someone click on the .bat file and it should run the below command.

C:\>osql /S dt-sc\demo /d demo /U demo /P demo /i demotemp.sql

The most instriguing part is to when I copy the .bat file on all sytems and click on it to run; it should grab the 'servername\instancename' and put it in the above cmd before running the .bat file.

Thanks

Ray M SSC-Insane Points: 21093 More actions · Answer 3

I do not know how to pull a sql servername/instancename using dos.

You can run the script using variables as such.

Google for some dos commands and you can probably get a little further

SET SERVER_NAME=SErvername

SET USER_NAME=Someuser

SET PASSWORD=SomePassword

SET DATABASE_NAME=DatabaseName

SET SCRIPT_LOCATION='\\SERVERNAME\UNCPATH'

osql/U %USER_NAME% /P %PASSWORD% /S %SERVER_NAME% /d %DATABASE_NAME% /i %SCRIPT_LOCATION%\FILENAME.sql

Pause

johnho008 SSC Enthusiast Points: 176 More actions · Answer 4

Hi,

Why not run your TSQL with OSQL command;

OSQL -E -S{servername} -d{databasename} -q{your TSQL}

Mauve SSChampion Points: 11316 More actions · Answer 5

Since this is SQL Server 2005, you should be using SQLCMD vs. OSQL. OSQL is obsolete.

[font="Arial Narrow"](PHB) I think we should build an SQL database. (Dilbert) What color do you want that database? (PHB) I think mauve has the most RAM.[/font]