Microsoft Internet Business Development Kit/PRODUCT_CD.iso/sqlsvr/i386/web.sql

Text File | 1995-12-14 | 15.9 KB | 503 lines

-- WEB.SQL

-- Creates sp_makewebpage, which does essentially the same thing as the
-- Web Page Wizard, except it does not create triggers.


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

create procedure sp_makewebpage
@outputfile   varchar(255),
@query1       varchar(255),
@query2       varchar(255) = NULL,
@query3       varchar(255) = NULL,
@query4       varchar(255) = NULL,
@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) = 'dbo',
@dbname       varchar(30) = NULL,
@templatefile varchar(255) = '?',
@webpagetitle varchar(255) = 'Microsoft SQL Server Web Page Wizard',
@resultstitle varchar(255) = 'Query Results',
@URL          varchar(255) = '?',
@reftext      varchar(255) = '?',
@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 = -1,                -- yyyymmdd as int
@targettime   int = -1,                -- 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
@procname     varchar(28) = NULL,      -- name to use when making the
                                       -- task and the wrapper/condenser
                                       -- stored procs
@maketask     bit = 1                  -- make a task? or just make the
                                       -- stored procs?
as
begin

   declare @rc int
   declare @taskname varchar(24)
   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 @freqtype_ int
   declare @freqinterval_ int
   declare @freqsubtype_ int
   declare @freqsubinterval_ int
   declare @freqrelativeinterval_ int
   declare @freqrecurrencefactor_ int
   declare @activestartdate_ int
   declare @activestarttimeofday_ int
   declare @complete varchar(255)
   declare @buf varchar(255)
   declare @taskid int
   declare @suid smallint
   declare @myusername varchar(30)
   declare @sspid varchar(20)

   declare @i              tinyint
   declare @j              tinyint
   declare @c              char(1)
   declare @newquery1      varchar(255)
   declare @newquery2      varchar(255)
   declare @newquery3      varchar(255)
   declare @newquery4      varchar(255)
   declare @ch_fixedfont   char(1)
   declare @ch_bolditalic  char(1)
   declare @ch_colheaders  char(1)
   declare @ch_lastupdated char(1)
   declare @ch_HTMLheader  char(1)

-- check args

   if ((@fixedfont <> 0) and (@fixedfont <> 1))
   begin
      RAISERROR(16947,11,0)
      return (1)
   end
   if ((@bold <> 0) and (@bold <> 1))
   begin
      RAISERROR(16948,11,0)
      return (2)
   end
   if ((@italic <> 0) and (@italic <> 1))
   begin
      RAISERROR(16949,11,0)
      return (3)
   end
   if ((@colheaders <> 0) and (@colheaders <> 1))
   begin
      RAISERROR(16950,11,0)
      return (4)
   end
   if ((@lastupdated <> 0) and (@lastupdated <> 1))
   begin
      RAISERROR(16951,11,0)
      return (5)
   end
   if ((@HTMLheader < 1) or (@HTMLheader > 6))
   begin
      RAISERROR(16952,11,0)
      return (6)
   end
   select @rc = 0
   exec @rc = sp_validname @username_
   if (@rc = 1)
   begin
      RAISERROR(16954,11,0)
      return (8)
   end
   if (@dbname != NULL)
      if (not(exists(select * from master..sysdatabases where name = @dbname)))
      begin
         RAISERROR(16955,11,0)
         return (9)
      end
   if ((@whentype < 1) or (@whentype > 4))
   begin
      RAISERROR(16956,11,0)
      return (10)
   end
   if ((@unittype < 1) or (@unittype > 3))
   begin
      RAISERROR(16957,11,0)
      return (11)
   end
   if ((@whentype = 2) and (@targetdate < 19000101))
   begin
      RAISERROR(16958,11,0)
      return (12)
   end
   if (((@whentype = 2) or (@whentype = 3)) and (@targettime = -1))
   begin
      RAISERROR(16959,11,0)
      return (13)
   end
   if ((@whentype = 3) and (@dayflags = 0))
   begin
      RAISERROR(16960,11,0)
      return (14)
   end
   if ((@whentype = 3) and (@numunits < 1))
   begin
      RAISERROR(16961,11,0)
      return (15)
   end
   if ((@whentype = 4) and (@numunits < 1))
   begin
      RAISERROR(16962,11,0)
      return (16)
   end
   if ((@whentype = 4) and ((@unittype < 1) or (@unittype > 3)))
   begin
      RAISERROR(16963,11,0)
      return (17)
   end
   if ( ((@URL = '?') and (@reftext <> '?')) or
        ((@URL <> '?') and (@reftext = '?')) or
        ((@table_urls = 1) and (@url_query is null)) or
        ((@table_urls = 0) and (@url_query is not null)) )
   begin
      RAISERROR(16964,11,0)
      return (18)
   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)
      select @dbname = d.name from
      master..sysdatabases d, master..sysprocesses p
      where d.dbid = p.dbid
         and spid = @@spid
      exec("insert into msdb..MSWork (spid,value1) select @@spid, name from "+@dbname+"..sysusers u, master..sysprocesses p where u.uid = p.uid and spid = "+@sspid)
      if (@suid != 1)
         select @username_ = convert(varchar(30),value1) from msdb..MSWork
      delete from msdb..MSWork
      where spid = @@spid
   end

   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 @taskname=@procname
   select @complete="exec "+@procname+";2"

   if (@whentype = 1)   -- now
   begin
      select @freqtype_ = 2
      select @freqinterval_ = 0
      select @freqsubtype_ = 1
      select @freqsubinterval_ = 0
      select @freqrelativeinterval_ = 1
      select @freqrecurrencefactor_ = 1
      select @activestartdate_ = 0
      select @activestarttimeofday_ = 0
      -- one-time tasks kill themselves & their stored procs
      select @complete = @complete + " drop procedure "+@procname+" exec msdb..sp_droptask '"+@taskname+"'"
   end

   if (@whentype = 2)   -- later
   begin
      select @freqtype_ = 1
      select @freqinterval_ = 0
      select @freqsubtype_ = 1
      select @freqsubinterval_ = 0
      select @freqrelativeinterval_ = 1
      select @freqrecurrencefactor_ = 1
      select @activestartdate_ = @targetdate
      select @activestarttimeofday_ = @targettime
      -- one-time tasks kill themselves & their stored procs
      select @complete = @complete + " drop procedure "+@procname+" exec msdb..sp_droptask '"+@taskname+"'"
   end

   if (@whentype = 3)   -- every xday
   begin
      select @freqtype_ = 8
      select @freqinterval_ = @dayflags
      select @freqsubtype_ = 1
      select @freqsubinterval_ = 0
      select @freqrelativeinterval_ = 1
      select @freqrecurrencefactor_ = 1
      select @activestartdate_ = 0
      select @activestarttimeofday_ = @targettime
   end

   if ((@whentype = 4) and (@unittype = 1))  -- every n hours
   begin
      select @freqtype_ = 4
      select @freqinterval_ = 1
      select @freqsubtype_ = 8
      select @freqsubinterval_ = @numunits
      select @freqrelativeinterval_ = 1
      select @freqrecurrencefactor_ = 1
      select @activestartdate_ = 0
      select @activestarttimeofday_ = 0
   end

   if ((@whentype = 4) and (@unittype = 2))  -- every n days
   begin
      select @freqtype_ = 4
      select @freqinterval_ = @numunits
      select @freqsubtype_ = 1
      select @freqsubinterval_ = 0
      select @freqrelativeinterval_ = 1
      select @freqrecurrencefactor_ = 1
      select @activestartdate_ = 0
      select @activestarttimeofday_ = @targettime
   end

   if ((@whentype = 4) and (@unittype = 3))  -- every n weeks
   begin
      select @freqtype_ = 8
      select @freqinterval_ = @dayflags
      select @freqsubtype_ = 1
      select @freqsubinterval_ = 0
      select @freqrelativeinterval_ = 1
      select @freqrecurrencefactor_ = @numunits
      select @activestartdate_ = 0
      select @activestarttimeofday_ = @targettime
   end

   select @ch_fixedfont=convert(char(1),@fixedfont)
   select @ch_bolditalic=convert(char(1),@bold+2*@italic)
   select @ch_colheaders=convert(char(1),@colheaders)
   select @ch_lastupdated=convert(char(1),@lastupdated)
   select @ch_HTMLheader=convert(char(1),@HTMLheader)

   if (@table_urls = 1)
   begin
       select @URL = '~TABLE~'
       select @reftext = @url_query
   end


   select @j = datalength(@query1)
   select @i = 1
   select @newquery1 = ""
   select @buf = ""

   while (@i <= @j)
   begin
      select @c = substring(@query1,@i,1)

      if (@c = char(13))
         select @c = char(11)
      else
         if (@c = char(10))
            select @c = char(12)
         else
            if (@c = "'")
               select @c = char(8)
            else
               if (@c = '"')
                  select @c = char(9)
               else
                  if (@c = " ")
                  begin
                     select @newquery1 = @newquery1 + @buf
                     select @buf = ""
                  end

      select @buf = @buf + @c
      select @i = @i + 1
   end

   if (@buf != "")
      select @newquery1 = @newquery1 + @buf

   if (@query2=NULL)
      select @newquery2=NULL
   else
      begin
         select @j = datalength(@query2)
         select @i = 1
         select @newquery2 = ""
         select @buf = ""

         while (@i <= @j)
         begin
            select @c = substring(@query2,@i,1)

            if (@c = char(13))
               select @c = char(11)
            else
               if (@c = char(10))
                  select @c = char(12)
               else
                  if (@c = "'")
                     select @c = char(8)
                  else
                     if (@c = '"')
                        select @c = char(9)
                     else
                        if (@c = " ")
                        begin
                           select @newquery2 = @newquery2 + @buf
                           select @buf = ""
                        end

            select @buf = @buf + @c
            select @i = @i + 1
         end

         if (@buf != "")
            select @newquery2 = @newquery2 + @buf
      end

   if (@query3=NULL)
      select @newquery3=NULL
   else
      begin
         select @j = datalength(@query3)
         select @i = 1
         select @newquery3 = ""
         select @buf = ""

         while (@i <= @j)
         begin
            select @c = substring(@query3,@i,1)

            if (@c = char(13))
               select @c = char(11)
            else
               if (@c = char(10))
                  select @c = char(12)
               else
                  if (@c = "'")
                     select @c = char(8)
                  else
                     if (@c = '"')
                        select @c = char(9)
                     else
                        if (@c = " ")
                        begin
                           select @newquery3 = @newquery3 + @buf
                           select @buf = ""
                        end

            select @buf = @buf + @c
            select @i = @i + 1
         end

         if (@buf != "")
            select @newquery3 = @newquery3 + @buf
      end


   if (@query4=NULL)
      select @newquery4=NULL
   else
      begin
         select @j = datalength(@query4)
         select @i = 1
         select @newquery4 = ""
         select @buf = ""

         while (@i <= @j)
         begin
            select @c = substring(@query4,@i,1)

            if (@c = char(13))
               select @c = char(11)
            else
               if (@c = char(10))
                  select @c = char(12)
               else
                  if (@c = "'")
                     select @c = char(8)
                  else
                     if (@c = '"')
                        select @c = char(9)
                     else
                        if (@c = " ")
                        begin
                           select @newquery4 = @newquery4 + @buf
                           select @buf = ""
                        end

            select @buf = @buf + @c
            select @i = @i + 1
         end

         if (@buf != "")
            select @newquery4 = @newquery4 + @buf
      end

 exec("CREATE PROCEDURE "+@procname+" WITH ENCRYPTION AS "+@newquery1+@newquery2+@newquery3+@newquery4)
 if (not exists (select 1 from sysobjects where type = 'P' and name = @procname)) return(26)


   exec("CREATE PROCEDURE "+@procname+";2 WITH ENCRYPTION AS exec master..xp_cmdshell 'mssqlwpw "+@username_+"!"+@dbname+"!"+@ch_fixedfont+@ch_bolditalic+@ch_colheaders+@ch_lastupdated+@ch_HTMLheader+"0!"+@templatefile+"!"+@webpagetitle+
   "!"+@resultstitle+"!"+@URL+"!"+@reftext+"!"+@outputfile+"!exec "+@procname+"'")


   if (@maketask = 1)
   begin
      select @rc = 0
      exec @rc = msdb..sp_addtask @name=@taskname, @subsystem="TSQL",
           @server=NULL, @username=@username_, @databasename=@dbname,
           @enabled=1,@freqtype=@freqtype_, @freqinterval=@freqinterval_,
           @freqsubtype=@freqsubtype_, @freqsubinterval=@freqsubinterval_,
           @freqrelativeinterval=@freqrelativeinterval_,
           @freqrecurrencefactor=@freqrecurrencefactor_,
           @activestartdate=@activestartdate_, @activeenddate=99991231,
           @activestarttimeofday=@activestarttimeofday_,
           @activeendtimeofday=235900,
           @runpriority=0, @emailoperatorname=NULL,
           @retryattempts=1, @retrydelay=5, @command=@complete,
           @loghistcompletionlevel=2, @emailcompletionlevel=0,
           @description=NULL, @tagadditionalinfo=NULL, @tagobjectid=NULL,
           @tagobjecttype=NULL

      if (@rc > 0)
      begin
          RAISERROR(16948,11,0,@rc)
          return (27)
      end
   end

   if (@maketask = 1) and (@whentype = 1)
      begin
         select @taskid = id from msdb..systasks where name = @taskname
         if ((@taskid = NULL) or (@taskid = 0))
            RAISERROR(16965,11,0)
         else
         begin
            exec @rc = sp_schedulersignal 'O', @taskid
            if (@rc != 0)
               RAISERROR(16966,11,0,@rc)
         end
      end
end
go

grant execute on sp_makewebpage to public
go