-- WEB.SQL 
-- Creates MSwebtasks table, and the procedures sp_makewebtask,
--   sp_dropwebtask, and sp_runwebtask

use msdb
go
if exists (select * from sysobjects where id = object_id('MSwebtasks'))
   drop table  MSwebtasks

go
exec sp_MS_upd_sysobj_category 1
go

-- Add MSwebtasks table
create table MSwebtasks (
    procname	varchar(28)  NOT NULL,	-- Name of the procedure
    outputfile	varchar(255) NOT NULL,	-- Physical name of output file
    taskstat	bit	     NOT NULL, -- TRUE if task scheduled, FALSE if not
    wparams     image	     NULL      -- xp_runwebtask parameters
)
go
create unique index web_idxproc on MSwebtasks(procname)
go
create unique index web_idxfile on MSwebtasks(outputfile)
go
grant SELECT on MSwebtasks to public
go
grant INSERT on MSwebtasks to public
go
grant DELETE on MSwebtasks to public
go
grant UPDATE on MSwebtasks to public
go
exec sp_MS_upd_sysobj_category 2
go

-- Update the master database
use master
go
if exists (select * from sysobjects
		where sysstat & 0xf = 4
			and name = 'xp_makewebtask')
	exec sp_dropextendedproc 'xp_makewebtask'

if exists (select * from sysobjects
		where sysstat & 0xf = 4
			and name = 'xp_dropwebtask')
	exec sp_dropextendedproc 'xp_dropwebtask'

if exists (select * from sysobjects
		where sysstat & 0xf = 4
			and name = 'xp_runwebtask')
	exec sp_dropextendedproc 'xp_runwebtask'

if exists (select * from sysobjects where id = object_id('dbo.sp_makewebtask') and sysstat & 0xf = 4)
   drop procedure dbo.sp_makewebtask
go
if exists (select * from sysobjects where id = object_id('dbo.sp_dropwebtask') and sysstat & 0xf = 4)
   drop procedure dbo.sp_dropwebtask
go
if exists (select * from sysobjects where id = object_id('dbo.sp_runwebtask') and sysstat & 0xf = 4)
   drop procedure dbo.sp_runwebtask
go

exec sp_MS_upd_sysobj_category 1
go

-- Add extended stored procedures for Web Server support.
sp_addextendedproc 'xp_makewebtask', 'xpsqlweb.dll'
go
sp_addextendedproc 'xp_dropwebtask', 'xpsqlweb.dll'
go
sp_addextendedproc 'xp_runwebtask', 'xpsqlweb.dll'
go

-- sp_makewebtask: Creates and defines the Web Page Task

create procedure sp_makewebtask
@outputfile   varchar(255),
@query	      text,
@fixedfont    tinyint = 1,             -- 0/1 
@bold         tinyint = 0,             -- 0/1 
@italic       tinyint = 0,             -- 0/1 
@colheaders   tinyint = 1,             -- 0/1 
@lastupdated  tinyint = 1,             -- 0/1 
@HTMLheader   tinyint = 1,             -- 1-6 
@username    varchar(30) = NULL,
@dbname       varchar(30) = NULL,
@templatefile varchar(255) = NULL,
@webpagetitle varchar(255) = NULL,
@resultstitle varchar(255) = NULL,
@URL	      varchar(255) = NULL,
@reftext      varchar(255) = NULL,
@table_urls   tinyint = 0,             -- 0/1; 1=use table of URLs 
@url_query    varchar(255) = NULL,   
@whentype     tinyint = 1,             -- 1=now, 2=later, 3=every xday 
                                       -- 4=every n units of time 
@targetdate   int = 0,		       -- yyyymmdd as int
@targettime   int = 0,		       -- hhnnss as int
@dayflags     tinyint = 1,             -- powers of 2 for days of week 
@numunits     tinyint = 1,
@unittype     tinyint = 1,	       -- 1=hours, 2=days, 3=weeks, 4=minutes
@procname     varchar(28) = NULL,      -- name to use when making the
                                       -- task and the wrapper/condenser
                                       -- stored procs
@maketask     int = 1,		       -- Debug option: make a task? or just
				       --  make the stored procs?

@rowcnt     int = 0,		       -- max no of rows to display
@tabborder  tinyint = 1,	       -- borders around the results table
@singlerow  tinyint = 0,	       -- Single row per page
@blobfmt    text = NULL		       -- Formatting for text and image fields
as
begin
   declare @suid smallint
   declare @sspid varchar(20)
   declare @yearchar varchar(2)
   declare @monthchar varchar(2)
   declare @daychar varchar(2)
   declare @hourchar varchar(2)
   declare @minchar varchar(2)
   declare @secchar varchar(2)
   declare @currdate datetime
   declare  @retval int

-- Check for valid @dbname if supplied
   if (@dbname <> NULL)
      if (not(exists(select * from master..sysdatabases where name = @dbname)))
      begin
	 RAISERROR(16854,11,1)
         return (9)
      end

-- override the @username value if suid != 1 (sa)
-- if @dbname is NULL, supply a default

   select @suid = suid from master..sysprocesses where spid = @@spid
   if ((@suid <> 1) or (@dbname = NULL))
   begin
      select @sspid = convert(varchar(20),@@spid)
      if (@dbname = NULL)
      begin
	  select @dbname = d.name from
	      master..sysdatabases d, master..sysprocesses p
	      where d.dbid = p.dbid and spid = @@spid
      end
      if (@suid <> 1)
      begin
	  exec("insert into msdb..MSWork (spid,value1) select @@spid, name from "
	      +@dbname+
	      "..sysusers u, master..sysprocesses p where u.suid = p.suid and spid = "
	      +@sspid)
	  select @username = convert(varchar(30),value1) from msdb..MSWork
	      where spid = @@spid
	  delete from msdb..MSWork
	      where spid = @@spid
	  if (@username = NULL)
	  begin
	     RAISERROR(16853,11,1)
	     return (9)
	  end
      end
      else
	select @username = 'dbo'
   end
   else
   begin
	select @username = 'dbo'
   end

-- Generate @procname if not supplied
   if (@procname=NULL)
      begin
         select @currdate = getdate()
         select @yearchar = substring(convert(varchar(4),datepart(yy,@currdate)),3,2)
         select @monthchar = replicate("0",2-datalength(convert(varchar(2),datepart(mm,@currdate))))+convert(varchar(2),datepart(mm,@currdate))
         select @daychar = replicate("0",2-datalength(convert(varchar(2),datepart(dd,@currdate))))+convert(varchar(2),datepart(dd,@currdate))
         select @hourchar = replicate("0",2-datalength(convert(varchar(2),datepart(hh,@currdate))))+convert(varchar(2),datepart(hh,@currdate))
         select @minchar = replicate("0",2-datalength(convert(varchar(2),datepart(mi,@currdate))))+convert(varchar(2),datepart(mi,@currdate))
         select @secchar = replicate("0",2-datalength(convert(varchar(2),datepart(ss,@currdate))))+convert(varchar(2),datepart(ss,@currdate))
         select @procname = "Web_"+@yearchar+@monthchar+@daychar+@hourchar+@minchar+@secchar+convert(varchar(20),@@spid)
      end

   select @retval = 1

-- Create the Web task
   execute @retval = master..xp_makewebtask  @outputfile, @query, @username, @procname, @dbname,
	    @fixedfont, @bold, @italic, @colheaders, @lastupdated, @HTMLheader,
	    @templatefile, @webpagetitle, @resultstitle, @URL, @reftext,
	    @table_urls, @url_query, @whentype, @targetdate, @targettime,
	    @dayflags, @numunits, @unittype, @rowcnt, @maketask, @tabborder,
	    @singlerow, @blobfmt
    if (@retval <> 1)
	begin
	    select @procname = 'xp_makewebtask'
	    RAISERROR(@retval, 11, 1, @procname)
	end

   return @retval

end
go


-- sp_dropwebtask: Drops a previously created Web Page Task

create procedure sp_dropwebtask
	    @procname varchar(28) = NULL,
	    @outputfile	varchar(255) = NULL
as
begin
    declare	@retval int

    -- At least one of the parameters have to be NOT NULL
    if ( (@procname=NULL) and (@outputfile = NULL) )
	begin
	    RAISERROR(16801,11,1)
	    return(1)
	end
    exec @retval = master..xp_dropwebtask @procname, @outputfile
    if (@retval <> 1)
	begin
	    select @procname = 'sp_dropwebtask'
	    RAISERROR(@retval,11,1, @procname)
	end
    return @retval
end
go

-- sp_runwebtask: Runs a previously created Web Page Task and creates the
--		    web page

create procedure sp_runwebtask
	    @procname varchar(28) = NULL,
	    @outputfile	varchar(255) = NULL
as
begin
    declare	@retval int

    -- At least one of the parameters have to be NOT NULL
    if ( (@procname=NULL) and (@outputfile = NULL) )
	begin
	    RAISERROR(16803,11,1)
	    return(1)
	end
    exec @retval = master..xp_runwebtask @procname, @outputfile
    if (@retval <> 1)
	begin
	    select @procname = 'sp_runwebtask'
	    RAISERROR(@retval,11,1, @procname)
	end
    return @retval
end
go

grant execute on sp_makewebtask to public
go
grant execute on sp_dropwebtask to public
go
grant execute on sp_runwebtask to public
go
exec sp_MS_upd_sysobj_category 2
go
