/*
 * This script creates a procedure which will shut down, and 
 * automatically restart the server. Works on Unix, NT and Win2000.
 * The procedure is named 'sp_restart_server' (there's also an 
 * alias, named 'sp_reboot', which is less typing).
 *
 * The main purpose of this procedure is to demonstrate 
 * capabilities of ASE's XP server. On the other hand, it *is* a handy
 * way to restart a server running on a different host.
 *
 *
 * Notes (Unix)
 * ------------
 * It is assumed that the RUN_<server> file is located in the default ASE install 
 * directory, and that @@servername corresponds to '<server>'. 
 *
 * On Unix, the "at" command is used to schedule the startup/shutdown.
 * The "at" syntax varies across platforms, so you may need to edit this script 
 * to use the right form of the "at" command for your situation.
 * By default, the syntax "at -s now < filename"  is used (except on Linux, where 
 * '-s' doesn't work).
 *
 * On Unix, the server is stopped with a shutdown command. This means that the
 * SA password must be specified, and that this password may hang around on disk.
 * See the remarks under "Security considerations" at the end of this file.
 *
 *
 * Notes (NT/Win2000)
 * ------------------
 * It is assumed that the ASE server to be stopped/restarted is running as 
 * an NT service, named SYBSQL_<server>, and that @@servername 
 * corresponds to '<server>'. 
 *
 * On NT/Win2000, the "at" command is used to schedule the startup/shutdown.
 * This requires that the NT task scheduler service is running.
 * (NT4.0  : Control Panel->Services->Task Scheduler->Start)
 * (Win2000: Control Panel->Administrative Tools->Services->Task Scheduler->Start)
 *
 * The start/stop will be performed on the next full minute (the NT scheduler 
 * cannot be scheduled to the second, but only to the minute).
 *
 *
 * Revision History
 * ================
 * 1998 - First version 
 * Jan 2003 - Updated for ASE 12.0+, sqsh and Linux; using the scheduler on NT.
 *
 *
 * Copyright note & Disclaimer :
 * =============================
 * This software is provided "as is" -- no warranty.
 * This software is for demonstration purposes only. It may not work correctly 
 * and/or reliably in a production environment. 
 * You can use this software free of charge for your own professional, 
 * non-commercial purposes. 
 * You are not allowed to sell this software or use it for any commercial 
 * purpose. You may (re)distribute only unaltered copies of this software, which
 * must include this copyright note.
 *
 * Please send any comments, bugs, suggestions etc. to the below email address.
 *
 * (c) 1998-2003 Copyright Rob Verschoor/Sypron B.V.
 *                    P.O.Box 10695
 *                    2501 HR Den Haag
 *                    The Netherlands
 *
 *                    Email: rob@sypron.nl
 *                    WWW  : http://www.sypron.nl
 *----------------------------------------------------------------------------- 
 */
 
use sybsystemprocs
go

-- we need to be at ASE 11.5 or later; if not, abort this script
if isnull(object_id("master.dbo.systimeranges"),99) >= 99
begin
   print ""
   print ""
   print "****************************************"
   print "****************************************"
   print " This script requires ASE 11.5 or later."
   print "****************************************"
   print "****************************************"
   print " "
   print " "
   print ""
   set background on  -- terminate this script now
end
go

/*
 * procedure to shut down, and then automatically restart XP server
 */
if object_id("sp_restart_server") <> NULL
   drop proc sp_restart_server
go
print "Creating 'sp_restart_server' ..."
go

create procedure sp_restart_server
/* Copyright (c) 1998-2003 Rob Verschoor/Sypron B.V. */
  @p_sapasswd varchar(40) = NULL
as
begin
declare @line varchar(80)
declare @cmd  varchar(200)
declare @fname  varchar(40)
declare @at_option  varchar(5)
declare @at_time char(5), @at_dt datetime, @at_secs int
declare @exec_path  varchar(50)
declare @nt int, @dollar char(1)

   -- make sure this is 11.5 or later
   if isnull(object_id("master.dbo.systimeranges"),99) >= 99
   begin
      print "This procedure only works for ASE version 11.5 or later."
      return (-1)
   end

   -- get a "$" character ('sqsh' would expand this by itself...)
   select @dollar = char(36)

   -- figure out if we're on NT or Unix
   select @nt = charindex("/NT", @@version)

   -- check server name is defined
   if @@servername = NULL
   begin
      print "The @@servername must be defined."
      print "Run ""sp_addserver SERVERNAME, local"", and restart the server"
      return (-1)
   end

   /* 
    * check XP server is set up correctly
    */
   if not exists 
     ( select * from master..sysservers
       where srvname = @@servername + "_XP" )
   begin
      print "XP server must be set up in master..sysservers."
      select @line = "Run ""sp_addserver " + @@servername + 
                     "_XP, NULL"" for this."
      print @line 
      select @line = "Also make sure " + @@servername + 
                     "_XP is defined in the "
      print @line 
      if @nt = 0
         print "interfaces file."      
      else
         print "SQL.INI file."
      
      return (-1)
   end

   /* 
    * check "xp_cmdshell context" config option is set to 0
    * we could do this automatically, but then it would be less instructive
    * for the user...
    */
   if ( select cc.value 
        from master..sysconfigures co,
             master..syscurconfigs cc
        where co.name = "xp_cmdshell context"
          and co.config = cc.config ) != 0
   begin
      select @line = "You must first modify the ""xp_cmdshell context"" configuration"
      print @line 
      print "parameter using the following command:"
      print " sp_configure ""xp_cmdshell context"", 0 "
      return (-1)
   end

   /* 
    * All checks have been done...
    * Set up the commands to be executed by XP server
    */
   if @nt != 0
   begin
      /* we're on NT */

      -- if the SA password has been specified, ignore it
      if @p_sapasswd != NULL
      begin
         print "Note: the 'sa' password is ignored on NT"
      end


      /*
       * create a .BAT file containing "net stop" and "net start" commands
       * to stop/start the server service. This works because a server is
       * always created as a service name SYBSQL_<SERVERNAME>.
       * This is why @@servername must be defined for this procedure to run
       *
       * First set up filename for file to be used. As for the directory,
       * C:\ is usually writable for anyone.
       */
      select @fname = "C:\" + @@servername + "_restart"

      /*
       * set up command string to create file
       */
      select @cmd = "echo net stop SYBSQL_" + @@servername + " > " + @fname + ".bat" + 
                    " & " + /* NT command separator */
                    "echo net start SYBSQL_" + @@servername + " >> " + @fname + ".bat"

      /*
       * create the file
       */
      exec xp_cmdshell @cmd, no_output

      --
      -- finally, execute the file. The NT scheduler is used
      -- for this, so the 'Task Scheduler' service should be running
      --
      -- Timing is on the next minute; when this is less than 5 seconds away, 
      -- the minute after that will be used
      --
      select @at_dt = dateadd(ss, 65, getdate())
      select @at_time = convert(char(5), @at_dt, 8)
      select @at_secs = datediff(ss, getdate(), convert(varchar,@at_dt,100))

      select @cmd = 'at ' + @at_time + ' cmd /c \"' + @fname + '.bat >' + @fname + '.out\"'
      print "This ASE server will be stopped and restarted in %1! seconds (at %2!:00) ...", @at_secs, @at_time
      exec xp_cmdshell @cmd, no_output
   end
   else  
   begin
      /* we're on Unix */
      
      -- first check the SA password has been specified
      if @p_sapasswd = NULL
      begin
         print "Please specify the 'sa' password"
         return (-1)
      end

      /*
       * create a Bourne shell file containing a shutdown command through ISQL
       * followed by a 'startserver' to restart the server.
       * Restarting involves the servername, which is why @@servername 
       * must be defined for this procedure to run.
       * Note: the ISQL file contains the SA password ! (also see remark at end)
       *
       * First set up filename for file to be used. As for the directory,
       * /tmp is usually writable for anyone.
       */
      select @fname = "/tmp/" + @@servername + "_restart.sh"

      /*
       * set up command string to create file
       */
      select @cmd = "rm -f " + @fname + 
                    " ; " + /* Unix Bourne shell command separator */
                    "echo ""isql -Usa -P" + @p_sapasswd + " -S" + @@servername +
                    " << --EOF-- "" > " + @fname 

      exec xp_cmdshell @cmd, no_output

      select @cmd = 'echo "shutdown" >> ' + @fname + 
                    ' ; ' + 
                    'echo "go" >> ' + @fname +
                    ' ; ' + 
                    'echo "--EOF--" >> ' + @fname 

      exec xp_cmdshell @cmd, no_output

      -- determine path to ASE RUN_server file
      select @exec_path = @dollar + "SYBASE/install/"         -- 11.9 or before
      if isnull(object_id("dbo.sysqueryplans"),99) < 99
         select @exec_path = @dollar + "SYBASE/" + 
	                     @dollar + "SYBASE_ASE/install/"  -- 12.0 or later

      select @cmd = 'echo "' + @exec_path + 'startserver -f ' + 
                               @exec_path + 'RUN_' + @@servername + 
			       '" >> ' + @fname 

      exec xp_cmdshell @cmd, no_output

      /*
       * Finally, execute the file. Because XP server doesn't seem 
       * to be very good at running a program in the background (using '&'),
       * the shell file just created will be kicked off through 'at -s now < file'.
       * Modify this command syntax if it it different on your system.
       */
      select @at_option = "-s"
      if charindex("Linux", @@version) > 0 
         select @at_option = ""  -- '-s' does not seem to work on Linux...

      select @cmd = "at " + " now < " + @fname 
      print "This ASE server will now be stopped and restarted..."
      exec xp_cmdshell @cmd, no_output
   end   

   /*
    * Security considerations:
    * ------------------------
    * Note that the .BAT or .sh file is not deleted automatically. 
    * Especially in the case of Unix, this deserves some attention, as
    * this file contains the SA password. 
    * The only way to get rid of this file may be to (before executing the file) 
    * issue an "at" command (exists both on NT and on Unix, BTW) which 
    * schedules a delete of this file which should execute "sometime" after 
    * the server has hopefully been restarted... 
    * This is left as an exercise for you, reader !
    */

   /* 
    * Ready...
    */
   return (0)
end
go
grant execute on sp_restart_server to public
go


if object_id("sp_reboot") <> NULL
   drop proc sp_reboot
go
print "Creating 'sp_reboot' ..."
go
create procedure sp_reboot
/* Copyright (c) 1998-2003 Rob Verschoor/Sypron B.V. */
  @p_sapasswd varchar(40) = NULL
as
   exec sp_restart_server @p_sapasswd
go
grant execute on sp_reboot to public
go

print "Ready."
go