Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2006
    Posts
    21

    Unanswered: 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.

    Thanks

  2. #2
    Join Date
    Sep 2006
    Posts
    21
    Hello

    I have got it working.

    Just I need to use :

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

    Thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •