specialoffer.sql

基于sqlserver2k,reporting service的扩展(auth,render,deliver==)应用（mssql,RS,AS,BI）

CREATE  PROCEDURE spUpdateRssFeed( @SpecialOfferID  int, @Response varchar(8000) out)
AS

DECLARE
  @Url varchar(1000)
 ,@obj   int
 ,@hr   int
 ,@status int
 ,@msg varchar(255)

   set 	@Url = 'http://localhost/AWReporterWeb/Chapter9/Campaigner.asmx/StartCampaign?CampaignID=' + CAST(@SpecialOfferID AS VARCHAR(10))
   exec @hr = sp_OACreate 'MSXML2.ServerXMLHttp', @obj out
   if @hr < 0 begin raiserror('sp_OACreate MSXML2.ServerXMLHttp failed', 16,1) return end

   exec @hr = sp_OAMethod @obj, 'Open', NULL, 'GET', @Url, false
   if @hr <0 begin set @msg = 'sp_OAMethod Open failed' goto eh end

   exec @hr = sp_OAMethod @obj, 'send'
   if @hr <0 begin  set @msg = 'sp_OAMethod Send failed' goto eh end


   exec @hr = sp_OAGetProperty @obj, 'status', @status OUT
   if @hr <0 begin  set @msg = 'sp_OAMethod read status failed' goto eh end

   if @status <> 200  begin set @msg = 'sp_OAMethod http status ' + str(@status) goto eh end

   exec @hr = sp_OAGetProperty @obj, 'responseText', @response OUT
   if @hr <0 begin  set @msg = 'sp_OAMethod read response failed' goto eh end

   exec @hr = sp_OADestroy @obj

   return
eh:
  exec @hr = sp_OADestroy @obj
  Raiserror(@msg, 16, 1)
  return
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

/* Create the trigger */
CREATE TRIGGER trgSpecialOffer ON [dbo].[SpecialOffer] 
AFTER INSERT
AS

/* Get the new special offer id. */
DECLARE	 @SpecialOfferID int
SELECT	 @SpecialOfferID = SpecialOfferID
FROM 		 inserted


DECLARE	@Result varchar(8000)
EXEC		spUpdateRssFeed @SpecialOfferID, @Result OUT
PRINT		@Result
GO

/* TO CALL BY SP
declare @response varchar(8000)
exec spUpdateRssFeed 2, @response out
print @response

TO CALL BY TRIGGER

INSERT INTO [AdventureWorks2000].[dbo].[SpecialOffer]([Description], [DiscountPct], [Type], [Category], [ActiveDate], [ExpirationDate], [MinQty], [MaxQty])
VALUES('Test Sales', 0.3, 'New Product', 'Customer', '01/27/2004', '02/27/2004', 2,4)
*/