osql script run as a .bat file

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

osql script run as a .bat file

Author

Topic

scelamko
Constraint Violating Yak Guru

309 Posts

Posted - 2005-10-19 : 10:48:20

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

karuna
Aged Yak Warrior

582 Posts

Posted - 2005-10-19 : 11:16:28

Try your batch file with something like this

osql /S ServerName /U UserName /P Password

use /i Filename if you have the query in a file

Check with BOL for more details.

Hope it helps

Karunakaran

scelamko
Constraint Violating Yak Guru

309 Posts

Posted - 2005-10-19 : 15:32:40

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

Kristen
Test

22859 Posts

Posted - 2005-10-19 : 15:44:10

For servername you can probably use "local" or "localhost" or maybe "127.0.0.1"

But I don't know how its going to guess which of the multiple instancename on the machine it should use!

Kristen

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2005-10-19 : 16:00:23

Is this what you were looking for?

DECLARE @cmd varchar(1000)

SET @cmd = 'echo osql -Uuser -Ppassword -Sserver -ddatabase -Q"truncate table employee select * from employee ... " > d:\temp\tsql.bat'

EXEC master..xp_cmdshell @cmd

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2005-10-19 : 16:09:10

If you want to use an input file:

DECLARE @cmd varchar(1000)

SET @cmd = 'echo osql -Uuser -Ppassword -Sserver -ddatabase ^< d:\temp\inputfile.sql > d:\temp\tsql.bat'

EXEC master..xp_cmdshell @cmd

scelamko
Constraint Violating Yak Guru

309 Posts

Posted - 2005-10-19 : 16:31:39

Kristen,
I want to capture the only instance (in most cases the machine has only one instance which is default) so it captures local instance on each system. I am not able to capture it by giving the
-S localhost\local in "servername\instancename" clause.

IS there any way you know that I can capture the local instance name.

Thanks

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-10-19 : 19:08:17

Did you read what is says in SQL Server Books Online?

-S server_name[\instance_name]

Specifies the instance of Microsoft® SQL Server™ 2000 to connect to. Specify server_name to connect to the default instance of SQL Server on that server. Specify server_name\instance_name to connect to a named instance of SQL Server 2000 on that server. If no server is specified, osql connects to the default instance of SQL Server on the local computer. This option is required when executing osql from a remote computer on the network.

CODO ERGO SUM

scelamko
Constraint Violating Yak Guru

309 Posts

Posted - 2005-10-21 : 09:17:51

Michael,

I have not give any servername/instancename so that it can pick up the servername and default instance. But it seems giving 'localhost' for servername works, but instance name it does not pick any (not even default instance). Is there something in any parameter file that I have to set the default instance name so that it picks up???

Thanks

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-10-21 : 19:58:58

I don't unserstand why you are passing it LOCALHOST.

Why don't you just omit the -S parameter, and let it pick up the default instance on the local server?

What happens when you run this command:
osql /E /Q "select Local_Server=@@servername"

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources