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

Execute Process Task arguments

We are attempting to use SSIS execute process task.

In a cmd.exe session the executable runs with normal arguments:

mycmd < myscript.txt > foo.txt 2>error.txt

We have attempted to several permutations of this without success. We declared variables and assigned them to stdin, stdout, stderr. However, mycmd opens in the SSIS session as if no arguments have been supplied.

Is there some expression that is required to connect the stdin/out/err variables in the Arguments within Execute Process Task. The help/examples are terse.

Redirections using < and > are not arguments, they are special symbols treated by cmd.exe.

To get the same behavior from Execute Process Task, you'll need to read content of myscript.txt into a variable and redirect program input to this variable; redirect output and errors to two other variables, then after program ends save these variables into foo.txt and error.txt.

But the easiest way to solve it is to use the 'cmd.exe' as the task executable, and supply your command (/C mycmd < myscript.txt > foo.txt 2>error.txt) as agruments for cmd.exe (/C added to instruct cmd.exe to executed specified command).|||

Thanks! We somehow missed the /C switch and that solved the problem.

Loading myscript.txt into a variable for use by stdin seems a bit problematic in our situation since it could be several hundred lines long and contain a wide spectrum of characters related to regular expressions that just seem like the perfect opportunity to tip it over.

We may look at the use of variables in the argument and see if we can move the scrum ahead.

|||Hello.

I'm trying to use the "Execute Process" task to compress several ASCII files in a folder in one ZIP archive. The command line for it looks as follows:

c:\programme\7zip\7z.exe a -bd U:\Projekte\TFG\Software\Log_storage\SCD_Demo_isHASH_34.zip SCD_Demo_isHASH_*.log

The result file has size of 2 KB.

When I put this command in the "Arguments" property of the "Execute Process" component (with "/C" switch and cmd.exe as executable), I get an empty ZIP file. What could be a reason for it? Has anyone already experienced such behaviour?

Regards,
Andrey

Execute ftp script

Hi !

We have a sql 2000 with sp2.

We wan't to execute a *.cmd file with a ftp command.
The ftp.cmd file is working ok interactive.
But if add it to enterprice manager/SQL Server Agent/Jobs we get failed.

Here are the cmd file:

@.echo off
del d:\mtdaxel\ftppmlogg.txt

date /T >> ftppmlogg.txt
time /T >> ftppmlogg.txt

ftp -n -s:ftppm.ftp >> ftppmlogg.txt
exit

Any sugestion "please" for our problem.

Regards Jan RockstedtHave you tested this script outside of SQL Server Agent jobs ?

The message you are receiving indicates that something is invalid with your ftp statement. The only thing that might be incorrect in your statement is the filename "ftppm.ftp". Since you are not using an absolute path, when the agent runs the job the path is probably incorrect. Try using an absolute path.

Good luck.|||Yes i have test it outside.

I will try the absolute path tomorrow.

Thanks !!!!

//Jan|||Yes it was the path.

Thanks rnealejr !!!!

:)|||Yes it was the path.

Thanks rnealejr !!!!

:)