Executing osql commands through batch file

Hello

I have a script ,which runs with osql

The script is :

osql -E
declare @.cmd nvarchar(1000)
declare @.cmd2 nvarchar(1000)
declare @.state1 varchar(100)
declare @.message varchar(100)
set @.message = ''
-- Build command to determine state of SQLSERVERAGENT service on Master Server
SET @.CMD = 'create table #state (state varchar(2000))' + char(10) +
'declare @.cmdx varchar(1000)' + char(10) +
'insert into #state EXEC master..xp_servicecontrol ''''QueryState'''', ''''SQLSERVERAGENT'' +
+ char(10) + 'select @.state=state from #state' + char(10) +
'drop table #state'
-- Build command to execute command that determines state of service being monitored
set @.cmd2 = 'declare @.state varchar(100)' + char(10) +
'exec ' + rtrim(@.@.servername) + '.master.dbo.sp_executesql N''' + @.CMD + ''',' +
'N''@.state varchar(100) out'',' +
'@.state out' + char(10) +
'set @.state1 = @.state'
-- Execute command and return state of service being monitored
exec master.dbo.sp_executesql @.cmd2,N'@.state1 varchar(100) out',@.state1 out
-- Is the service that was monitored not
IF (UPPER(@.state1) <> 'RUNNING.')
--if @.state1 <1 'Running.'
begin
-- Display message that primary monitor is down
select @.message = @.message+char(13)+ @.@.servername + ' -' + 'Sql Server Agent Not Running'+char(13)
print 'Master server "' + rtrim(@.@.servername) + '" for monitoring is not available.'
exec master.dbo.xp_smtp_sendmail

It works fine when I run it on the command line prompt.
And I receive a mail , if the server agent is running.

But when I save it as bat file and try to run , it stops and doesnot even give an error.

Can anyone let me know what I can do.

ThanksHello

I have got it working.

Just I need to use :

osql -E -iC:\serveragent.sql -oC:\outputfile.txt

Thanks

Executing commands in parallel

I have the need to run multiple SQL commands (like osql and bcp) in parallel
from a single bat file. To describe this a little clearer...
While executing a single .BAT file, I want to be able to execute 3 OSQL
commands in parallel. Currently I have to run these 3 serially and it takes
too long.
Is there any way from within a single bat file that I could have 3 OSQL
commands running at the same time?
Thanks in advance."TJT" <T_homas.T_odd@.smed.com> wrote in message
news:OHrmXephDHA.604@.TK2MSFTNGP10.phx.gbl...
> I have the need to run multiple SQL commands (like osql and bcp) in
parallel
> from a single bat file. To describe this a little clearer...
> While executing a single .BAT file, I want to be able to execute 3 OSQL
> commands in parallel. Currently I have to run these 3 serially and it
takes
> too long.
> Is there any way from within a single bat file that I could have 3 OSQL
> commands running at the same time?
>
start osql.exe . . .
start osql.exe . . .
start osql.exe . . .
From a command prompt type
start /?
for help with the start command.
Davidsql

Executing "Shell" commands.

Hi,
IIRC it is possible to execute shell commands in SQL server, is this the
case?
I intend to give public access to a test server (MSDE). My main concern is
that people could use SQLServer shell commands as a back door to hack my
operating system.
I'm not too concerned at this stage about securing the data though, since it
is only for testing & debugging.
Please advise, many thanks.
--
Mike Collier BSc( Hons) Comp Sci
Offer: Get a copy of AdoAnywhere Browser FREE when you register for the
forum.
http://www.adoanywhere.com/forumlook up xp_cmdshell in BOL, do not use an SA level account for your web
service accounts
Ray Higdon MCSE, MCDBA, CCNA
--
"Mike Collier" <mike@.adoanywhere.com> wrote in message
news:Om0r3jh3DHA.2888@.tk2msftngp13.phx.gbl...

quote:

---

> Hi,
> IIRC it is possible to execute shell commands in SQL server, is this the
> case?
> I intend to give public access to a test server (MSDE). My main concern is
> that people could use SQLServer shell commands as a back door to hack my
> operating system.
> I'm not too concerned at this stage about securing the data though, since

---

it

quote:

---

> is only for testing & debugging.
> Please advise, many thanks.
> --
> Mike Collier BSc( Hons) Comp Sci
> Offer: Get a copy of AdoAnywhere Browser FREE when you register for the
> forum.
> http://www.adoanywhere.com/forum
>
>

---

|||> look up xp_cmdshell in BOL, do not use an SA level account for your web

quote:

---

> service accounts

---

Thank you, very much appreciated.
Mike.

Execute multiple sql commands with one trip to the database.

Ive got a List<sqlCommand> and I want to execute all the commands in it, but only using one trip to the database. I dont need any return values from any of these commands. Most of the commands are update/insert/delete. Sometimes after a insert command there may be a command "set @.NEWID =identity_insert" to facilitate the next insert to a related table, but thats the only other type of command to insert/update/delete.

I am using microsofts enterprise data application block. I want to execute all the commands in the list with just one trip to the database. Would the following code achieve this :

db = DatabaseFactory.CreateDatabase("wlmAdmin");
foreach (SqlCommand sc in saveCommands)
{
db.ExecuteNonQuery(sc);
}

or would this make multiple trips to the database? Im not 100% on whats going on behind the db object. If I create the db Item in the constructor of my class using "db = DatabaseFactory.CreateDatabase("wlmAdmin");" would this mean that every instance of that class would maintain an open database connection throughout its scope/lifetime?

Ive created the class to retrieve all data using a single stored procedure and then when saving, the class builds a List of SqlCommands which as Ive mentioned I want to execute in one trip, to make the number of trips to the database as small as possible. Because of this I don't need the db object maintain an open connection to the database all the time. However It doesnt seem to have any dispose methods so Im not clear on how it operates.

In the case that the code above opens a connection for each command, how would I got about batch executing the queries in one trip?

Thanks,

C

the code sample you provided would make seperate trips to the database for each Command in your saveCommands list. If you need to batch them all together with only a single trip to the database, you might want to consider using a Stored Procedure.

|||

Try with stored procedure.... It is a best solution for your problem...

|||

You can include multiple sql commands just by separating them with semi-colons -- but then it's very hard to track down problems (where did it fail?). You will be much better off using a stored proc as the other posters suggest. Be sure to include good error handling at all steps by checking @.@.error. You will probably want to wrap the whole thing inside a transaction. Be sure also to do these 2 sets:

set nocount on -- this stops needless traffic between sql server an you telling you how many rows were affected, there are other, better ways to get this info

set xact abort on -- the only way to guarantee that a sql error will cause proc termination and a rollback (assuming you've done a begin tran)

|||

Ok, so how do I go about setting up a stored procedure which takes multiple (variable) sqlcommands as arguments.

One of the main reasons Im using sqlcommands is so I can use sqlParameter objects to avoid sql injection, so If possible I want to use the commands, rather than a lot of sql strings seperated by semi-colons.

Im not too advanced on Stored Procedures, so all details are much appreciated :-)

Thanks,

C

|||

Here's a generic "template" -- This examples here will take care of 95% of what you need to do. Note that I may have some typos so if you get a syntax error that's probably why. You don't send the commands as arguments, you put them inside the proc.

Note especially:
- Error handling -- use of @.@.error and @.@.rowcount to determine whether things went bad
- Wrapping everything in a transaction and rolling back in the event of a bad result -- most important!
- Use of OUTPUT variables -- you can use as many of these as you want to return data to the caller -- just be sure to set the parameter type to Output in the caller
- Use of PRINT statement (during debugging)

If you google "sql server stored procedure" you will get many, many examples.

You can write procs in VS or in Enterprise Manager or for that matter in notepad. Unless sql server debugging is turned on (and it isn't in many shops because of permissioning issues) there is no real debugging available

create proc MyProc (IntVar as int, Char50Var as Char(50), VarCharVar as varcahr(255), FloatVar as float ReturnVar1 as int OUTPUT, ReturnVar2 as char(10) OUTPUT) as
set nocount on
set xactabort on

begin tran
declare @.ErrNum int,
@.NumRows int,
@.ErrMsg varchar(255)

--do something--
/* Check error code -- be sure to save it to a local variable first so it does not get overwritten */
select @.ErrNum = @.error
if @.ErrNum <> 0
begin
RAISERROR ('Error occurred...blah, blah, blah',16,1) -- the 16,1 are just there because they have to be, you can use any value
ROLLBACK TRAN
RETURN
end

/* Use print statements liberally when you are debugging to find out what's happening'
/* print 'blah, blah, blah' */

--do something--
select @.ErrNum = @.error
if @.ErrNum <> 0
begin
RAISERROR ('Error occurred...blah, blah, blah',16,1) -- the 16,1 are just there because they have to be, you can use any value
ROLLBACK TRAN
RETURN
end
select @.NumRows = @.@.rowcount
if @.NumRows <> 1
begin
select @.ErrMsg = 'Error: Expected to update 1 row only but ' + convert(varchar(20),@.NumRows) + ' were updated instead!'
RAISERROR (@.ErrMsg ,16,1) -- the 16,1 are just there because they have to be, you can use any value
ROLLBACK TRAN
RETURN
end

etc.
...
...
...
commit tran
go

grant exec on dbo.MyProc to ....user or windows group......
go

NOTE: To change a proc later you can drop and recreate it or use Alter Proc instead of Create Proc.
Alter Proc retains all the permissions

|||

Hi,

Thanks again for the reply. Im still not 100% here. Lets say I have 15 sqlCommands in my List<sqlCommand>. Now I want to send all these to the stored procedure at once how do I do this? There is no way in .net to called a stored prcedure with sqlCommands as parameters, so thats the first thing I dont understand? Each sqlCommand has different Parameters, a different number of Parameters and each operates on different tables.

You say I dont call the procedures as commands, I put them inside the procedure. Does this mean I write the procedures dynamically in my code, call create on them, and then execute them, all from my .net application? I think I must have this wrong.

If I say for example my class has created 2 commands for me to execute :

delete from Table1 where id = @.id (1 Parameter of type int)

update Table1 set uname = @.uname, lname=@.lname where id = @.id (2 parameters, 2 nvarchar, 1 int)

How do I create one stored procedure that will accept both these in one trip to the database and execute them. Again thanks for the feedback so far.

C

|||

staplebottom:

Lets say I have 15 sqlCommands in my List<sqlCommand>.

Either:

1. Make 15 separate calls, in which case you need to use ado to manage your transaction (you said they should all go together). If no transaction issue then just make 15 calls

2. Create 1 proc with all 15 commands and pass in all the parms you need

3. Send 1 call with 15 different 'exec ...' commands, separated by commas -- but then it's very hard to figure out whether anything went wrong and if so, which one.

|||

With that example, I would say you are trying to re-invent the dataadapter class, the dataset class, or a combination of both.

|||

So Im doing things wrong so. My aim was to just hit the database once to select all the data, one stored procedure, which then saves all the data into a dataset with a number of tables.

Then I manually check which tables are changed and create a command for each insert / update and delete. I understand that the dataset has this functionality, but felt that I might be able to reduce the number of trips to the database by implementing this myself for my own classes. So I created a list of commands for the changes to the dataset and thought I might be able to execute them all with one trip to the database. If this isnt possible I suppose I have one more question before I revert to using a few stored procedures (one for each table).

If I convert the sql commands to one long sql string and send it to the database as one command :

1. If I put a value into a sqlParameter say as Varchar and then read it back into a string, will it be safe from causing Sql Injections?

2. Will one long string of sql text e.g (update table set f =2; insert into table1 (test) values ('x') ; etc etc ..), seperated by by semi-colons execute faster than executing the sqlcommands using a trip each time.

3. If I have a command in the semi-colon delimited string, set @.newid = identity_insert; will this actually work ok, storing the value in newid for the next few commands to use?

Thanks,

C

|||

I think you're worrying too much about making trips to the database. Keep things simple. Trying to do everything in one command will only get you into trouble.

staplebottom:

If I have a command in the semi-colon delimited string, set @.newid = identity_insert; will this actually work ok, storing the value in newid for the next few commands to use?

No, I don't believe it will because you aren't creating any context for @.newid to exist in

|||

Yeah I think your right. I think Ill just try use a few stored procedures. Well it was at least a learning experience. Thanks for the feedback.

|||

1. No

2. Yes

3. Yes, btw it'd be SET @.newid=identity_insert(); -- Notice the parenthesis

BTW, the dataadapter will batch up multiple commands into a batch for sqlserver to execute (Reducing the roundtrips). Unfortunately, I see no way of extending/allowing this functionality outside of the dataadapter as Microsoft has marked everything you'd need as notinheritable, etc. Shame.

Execute Dos Commands in T-SQL Script

Friends,
I know there is a way to execute DOS commands inside T-Sql script, but can't
seem to find it. For example: using the MOVE command inside a stored
procedure to move .txt files into another directory after they've been
imported.
Can someone point me in the right direction?
Thanks ...The thing you are looking for is called xp_cmdshell
Have a look in the BOL for the details. You need to be symin to run this
command
kind regards
Greg O
Need to document your databases. Use the first and still the best AGS SQL
Scribe
http://www.ag-software.com
"bill_morgan" <billmorgan@.discussions.microsoft.com> wrote in message
news:BBD58F20-E45C-418D-9B5A-E1221B81BE05@.microsoft.com...
> Friends,
> I know there is a way to execute DOS commands inside T-Sql script, but
> can't
> seem to find it. For example: using the MOVE command inside a stored
> procedure to move .txt files into another directory after they've been
> imported.
> Can someone point me in the right direction?
> Thanks ...
>|||Thanks, Greg ... I did run into that one, but wasn't getting it to work righ
t
(am running it on my own version of SQL Server so shouldn't be having an
Admin problem).
Now that I'm sure that's what I need to pursue, I'll give it another try.
"GregO" wrote:

> The thing you are looking for is called xp_cmdshell
>
> Have a look in the BOL for the details. You need to be symin to run th
is
> command
>
> --
> kind regards
> Greg O
> Need to document your databases. Use the first and still the best AGS SQL
> Scribe
> http://www.ag-software.com
> "bill_morgan" <billmorgan@.discussions.microsoft.com> wrote in message
> news:BBD58F20-E45C-418D-9B5A-E1221B81BE05@.microsoft.com...
>
>|||Basic question I assume is also true, you are a SysAdmin on the server?
Where are you trying to make calls to? If it is on a network share, then
your SQL Server service account needs permissions there. If you are running
under a local system account, then you will have to grant the machine
account access to the share.
Here is a basic statement that should return values for you:
exec xp_cmdshell 'dir c:'
"bill_morgan" <billmorgan@.discussions.microsoft.com> wrote in message
news:CC18C513-D5DB-44DB-B849-BFB29965B1A8@.microsoft.com...
> Thanks, Greg ... I did run into that one, but wasn't getting it to work
> right
> (am running it on my own version of SQL Server so shouldn't be having an
> Admin problem).
> Now that I'm sure that's what I need to pursue, I'll give it another try.
> "GregO" wrote:
>