Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Stored Procedure Differences...

Author  Topic 

mikebird
Aged Yak Warrior

529 Posts
Posted - 2008-04-25 : 08:46:59
These do similar tasks in an 8-step job. One sends HTML emails, the other creates an HTML text file to be converted to PDF. Both work fine.

I am trying to keep a log in table MSP to flag which individual item has had this done to it. The PDF one is perfect. After the loop, the the flags are set. The email flags just don't work. Tried this inside and outside the loop, for the sake of it - maybe one will work. Can't see why not. Pasting both procs here...

CREATE PROCEDURE validateSOR_PDF AS

set ANSI_WARNINGS ON
set ANSI_NULLS ON

-- AUTOMATING Multiple SOR Code Validation Messages ---> PDF

/*

Mike Bird

7th April 2008

Takes HTML messages riddled with parameter detail and generated a text file in the IN tray of PDF Converter to pass securely to Engineers & Finance
for archive

*/

declare @FROM NVARCHAR(4000),
@FROM_NAME NVARCHAR(4000),
@TO NVARCHAR(4000),
@CC NVARCHAR(4000),
@BCC NVARCHAR(4000),
@priority NVARCHAR(10), -- for the execution parameters
@subject NVARCHAR(4000),
@message NVARCHAR(4000),
@messagefile NVARCHAR(4000),
@dumpmsg NVARCHAR(4000),
@type NVARCHAR(100),
@attachments NVARCHAR(4000),
@codepage INT,
@rc INT,
-------------------------------------
@Operative NVARCHAR(50),
@SOR NVARCHAR(100),
@email NVARCHAR(50),
@date NVARCHAR(20),
@received NVARCHAR(20),
@text1 NVARCHAR(100),
@text2 NVARCHAR(100),
@text3 NVARCHAR(100),
@jobno CHAR(10), -- for the query
@cust NVARCHAR(50),
@client NVARCHAR(10),
@address1 NVARCHAR(30),
@address2 NVARCHAR(30),
@address3 NVARCHAR(30),
@address4 NVARCHAR(30),
@address5 NVARCHAR(30),
@address6 NVARCHAR(30),
@desc NVARCHAR(50),
@quantity NVARCHAR(10),
@status NVARCHAR(12),
@cost money,
@engSig binary,
@custSig binary,
-------------------------------------
@i int, -- for the fucking LOOP
@PDF nvarchar(4000), -- for the HTML and parameters brought alltogether
@filename nvarchar(50) -- where to put the output text files for processing from HTML --> PDFs
-------------------------------------


--drop table #validSOR -- drops the table before using, so it exists for scrutiny between uses
select distinct
identity(int) as x, -- puts this current round of emails into temp table
ccs_qos_item_ref "SOR",
ccs_mee_oper_name "Operative",
ccs_cd_email "email",
ccs_quo_user_date#1 "upd_date", -- current datetime from jobcompleted once it's Updated
received "received",
ccs_not_text#1 "text1",
ccs_not_text#2 "text2",
ccs_not_text#3 "text3",
ccs_quo_ref_number#2 "jobno",
ccs_quo_site_contact "Customer",
ccs_quo_company_ref "Client",
ccs_quo_address#1 "Address1",
ccs_quo_address#2 "Address2",
ccs_quo_address#3 "Address3",
ccs_quo_address#4 "Address4",
ccs_quo_address#5 "Address5",
ccs_quo_address#6 "Address6",
ccs_qos_title "Descrip",
ccs_qos_qty#2 "Quantity",
ccs_quo_analysis_code "Status",
ccs_qos_quoted_price#6 "Cost"
into #validSOR
from [ken-sql-002].axis_europe.dbo.ccs_quotes q
inner join [ken-sql-002].hettonhosttestdatabase.dbo.job j
on q.ccs_quo_ref_number#2 = left(j.cid,10)
left outer join [ken-sql-002].axis_europe.dbo.ccs_q_sections qs
on q.ccs_quo_quote_number = qs.ccs_qos_quote_number
left outer join [ken-sql-002].axis_europe.dbo.ccs_site_meeting m
on m.ccs_mee_quote_number = q.ccs_quo_quote_number
left outer join [ken-sql-002].axis_europe.dbo.ccs_notes n
on q.ccs_quo_note_number = n.ccs_not_number
left outer join [ken-sql-002].axis_europe.dbo.ccs_company_file f
on q.ccs_quo_company_ref = f.ccs_cd_account_number
--where ccs_qos_item_ref is not NULL -- there are currently 6,065 NULLS in there...
--where ccs_quo_ref_number#2 = 'THAD003736' -- there are no NULLs in there
where q.ccs_quo_analysis_code = 'PENDING'
--where ccs_qos_item_ref in ('631501','372019','631107','O/H/R','371031','0Y0000','DECS') -- these are working SOR samples

--select * from #validSOR

--declare mail CURSOR FOR #validSOR

------------------------------------------------------------------

SELECT @i = MIN(x) -- Set start point
FROM #validSOR

WHILE @i IS not NULL
BEGIN -- Start here


select @SOR = SOR, -- Values into variables
@Operative = Operative,
@email = email,
@date = upd_date, -- current datetime from jobcompletedUpdate
@received = received,
@text1 = text1,
@text2 = text2,
@text3 = text3,
@jobno = jobno,
@cust = Customer,
@client = Client,
@address1 = Address1,
@address2 = Address2,
@address3 = Address3,
@address4 = Address4,
@address5 = Address5,
@address6 = Address6,
@desc = Descrip,
@quantity = Quantity,
@status = Status,
@cost = Cost
from #validSOR
where x = @i -- selecting the row under scrutiny is set to the @i variable keeping count


select @PDF = '<HTML><B>Confirmation of Works Completed</B><BR><HR WIDTH="100%" SIZE="1" COLOR="#CCCCCC"><TABLE WIDTH="60%"BORDER="1" BORDERCOLOR="#CCCCCC" CELLPADDING="4" CELLSPACING="0" ><TR><TD><B>Operative : </B>'+@Operative+'</TR></TD><TR><TD><B>Date / Time : </B>'+@date+'</TD></TR><TR><TD><B>Details of work carried out... </B>'+@text1+' '+@text2+' '+@text3+'</TD></TR><TR><TD><B>Job Number : </B>'+@jobno+'</TD></TR><TR><TD><B>System Received Date : </B>'+@received+'</TD></TR><TR><TD><B>
Current System Status : </B>'+@status+'</TD></TR><TR><TD><B>Customer Name : </B>'+@cust+'</TD></TR><TR><TD><B>Address : </B>'+@address1+', '+@address2+', '+@address3+', '+@address4+', '+@address5+', '+@address6+'</TD></TR><TR><TD><B>Signature of Customer : </B></TD></TR><TR><TD><B>Signature of Engineer : </B></TD></TR><TR><TD><B>
Report Further Action : </B></TD></TR></TABLE><TABLE WIDTH="60%" BORDER="1" BORDERCOLOR="#CCCCCC" CELLPADDING="5" CELLSPACING="0" ><TR><TD><B>SOR Code</TD></B><TD><B>
Description</TD></B><TD><B>Quantity</TD></B><TD><B>Check / Valid</TD></B><TD><B>Omit Code</TD></B></TD></TR><TR><TD>'+@SOR+'</TD><TD>'+@desc+'</TD><TD>'+@quantity+'</TD><TD>[___]</TD><TD>[___]</TD></TD></TR></TABLE><TABLE WIDTH="60%" BORDER="0" BORDERCOLOR="#CCCCCC" CELLPADDING="0" CELLSPACING="20" ><TD></TD><TR><TD><P Align="Right">______________________________________</TD></TR></P><TR><TD><P Align="Right"><B>Signature of Supervisor</B></TD></TR></P></TABLE></HTML>'

select @filename = 'd:\DATA\PDFQUEUES\IN\'+@Operative+'.txt' -- this is correct. Right directory. Great filenames!

--exec [ken-sql-002].master.dbo.xp_cmdshell @PDF

exec [ken-sql-002].ConstructMobile.dbo.sp_appendToFile @filename, @PDF

SELECT @i = min(x)
FROM #validSOR
WHERE x > @i
END

update msp
set PDF_convert_flag = 1 -- to show in the MSP table if the PDFs have been generated per row
from [ken-sql-001].dataloads.dbo.mobile_solution_process msp
inner join #validSOR
on #validSOR.jobno = msp.work_order
GO

===============================================

CREATE PROCEDURE validateSOR AS


-- AUTOMATING Multiple SOR Code Validation by HTML email

/*

Mike Bird

14th March 2008

Creates a temp table containing a list of Operatives with their email addresses & COW details
These are individually sent by email, using xp_smtp_sendmail, in HTML format, inserting details captured by variable per loop
Where the jobs are temporarily set to PENDING status within this COW via PDA job

*/

declare @FROM NVARCHAR(4000),
@FROM_NAME NVARCHAR(4000),
@TO NVARCHAR(4000),
@CC NVARCHAR(4000),
@BCC NVARCHAR(4000),
@priority NVARCHAR(10), -- for the execution parameters
@subject NVARCHAR(4000),
@message NVARCHAR(4000),
@messagefile NVARCHAR(4000),
@dumpmsg NVARCHAR(4000),
@type NVARCHAR(100),
@attachments NVARCHAR(4000),
@codepage INT,
@rc INT,
-------------------------------------
@Operative NVARCHAR(50),
@SOR NVARCHAR(100),
@email NVARCHAR(50),
@date NVARCHAR(20),
@received NVARCHAR(20),
@text1 NVARCHAR(100),
@text2 NVARCHAR(100),
@text3 NVARCHAR(100),
@jobno CHAR(10), -- for the query
@cust NVARCHAR(50),
@client NVARCHAR(10),
@address1 NVARCHAR(30),
@address2 NVARCHAR(30),
@address3 NVARCHAR(30),
@address4 NVARCHAR(30),
@address5 NVARCHAR(30),
@address6 NVARCHAR(30),
@desc NVARCHAR(50),
@quantity NVARCHAR(10),
@status NVARCHAR(12),
@cost money,
@engSig binary,
@custSig binary,
-------------------------------------
@i int -- for the fucking LOOP
-------------------------------------


--drop table #validSOR -- drops the table before using, so it exists for scrutiny between uses
select distinct
identity(int) as x, -- puts this current round of emails into temp table
ccs_qos_item_ref "SOR",
ccs_mee_oper_name "Operative",
ccs_cd_email "email",
ccs_quo_user_date#1 "upd_date", -- current datetime from jobcompleted once it's Updated
received "received",
ccs_not_text#1 "text1",
ccs_not_text#2 "text2",
ccs_not_text#3 "text3",
ccs_quo_ref_number#2 "jobno",
ccs_quo_site_contact "Customer",
ccs_quo_company_ref "Client",
ccs_quo_address#1 "Address1",
ccs_quo_address#2 "Address2",
ccs_quo_address#3 "Address3",
ccs_quo_address#4 "Address4",
ccs_quo_address#5 "Address5",
ccs_quo_address#6 "Address6",
ccs_qos_title "Descrip",
ccs_qos_qty#2 "Quantity",
ccs_quo_analysis_code "Status",
ccs_qos_quoted_price#6 "Cost"
into #validSOR
from [ken-sql-002].axis_europe.dbo.ccs_quotes q
inner join [ken-sql-002].hettonhosttestdatabase.dbo.job j
on q.ccs_quo_ref_number#2 = left(j.cid,10)
left outer join [ken-sql-002].axis_europe.dbo.ccs_q_sections qs
on q.ccs_quo_quote_number = qs.ccs_qos_quote_number
left outer join [ken-sql-002].axis_europe.dbo.ccs_site_meeting m
on m.ccs_mee_quote_number = q.ccs_quo_quote_number
left outer join [ken-sql-002].axis_europe.dbo.ccs_notes n
on q.ccs_quo_note_number = n.ccs_not_number
left outer join [ken-sql-002].axis_europe.dbo.ccs_company_file f
on q.ccs_quo_company_ref = f.ccs_cd_account_number
--where ccs_qos_item_ref is not NULL -- there are currently 6,065 NULLS in there...
--where ccs_quo_ref_number#2 = 'THAD003736' -- there are no NULLs in there
where q.ccs_quo_analysis_code = 'PENDING'
--where ccs_qos_item_ref in ('631501','372019','631107','O/H/R','371031','0Y0000','DECS') -- these are working SOR samples

--select * from #validSOR

--declare mail CURSOR FOR #validSOR

------------------------------------------------------------------

SELECT @i = MIN(x) -- Set start point
FROM #validSOR

WHILE @i IS not NULL
BEGIN -- Start here


select @SOR = SOR, -- Values into variables
@Operative = Operative,
@email = email,
@date = upd_date, -- current datetime from jobcompletedUpdate
@received = received,
@text1 = text1,
@text2 = text2,
@text3 = text3,
@jobno = jobno,
@cust = Customer,
@client = Client,
@address1 = Address1,
@address2 = Address2,
@address3 = Address3,
@address4 = Address4,
@address5 = Address5,
@address6 = Address6,
@desc = Descrip,
@quantity = Quantity,
@status = Status,
@cost = Cost
from #validSOR
where x = @i -- selecting the row under scrutiny is set to the @i variable keeping count


select distinct
@FROM = N'DO_NOT_REPLY@axiseurope.com', -- sets all the parameters to use below
@FROM_NAME = N'DO_NOT_REPLY@axiseurope.com',
@TO = N'michael.bird@axiseurope.com', -- this string needs to be the @email variable eventually...
@CC = N'',
@BCC = N'',
@priority = N'Normal',
@subject = N'Validation Required for SOR Codes',
@message = N'<HTML><B>Confirmation of Works Completed</B><BR><HR WIDTH="100%" SIZE="1" COLOR="#CCCCCC"><TABLE WIDTH="60%"BORDER="1" BORDERCOLOR="#CCCCCC" CELLPADDING="4" CELLSPACING="0" ><TR><TD><B>Operative : </B>'+@Operative+'</TR></TD><TR><TD><B>Date / Time : </B>'+@date+'</TD></TR><TR><TD><B>Details of work carried out... </B>'+@text1+' '+@text2+' '+@text3+'</TD></TR><TR><TD><B>Job Number : </B>'+@jobno+'</TD></TR><TR><TD><B>System Received Date : </B>'+@received+'</TD></TR><TR><TD><B>
Current System Status : </B>'+@status+'</TD></TR><TR><TD><B>Customer Name : </B>'+@cust+'</TD></TR><TR><TD><B>Address : </B>'+@address1+', '+@address2+', '+@address3+', '+@address4+', '+@address5+', '+@address6+'</TD></TR><TR><TD><B>Signature of Customer : </B></TD></TR><TR><TD><B>Signature of Engineer : </B></TD></TR><TR><TD><B>
Report Further Action : </B></TD></TR></TABLE><TABLE WIDTH="60%" BORDER="1" BORDERCOLOR="#CCCCCC" CELLPADDING="5" CELLSPACING="0" ><TR><TD><B>SOR Code</TD></B><TD><B>
Description</TD></B><TD><B>Quantity</TD></B><TD><B>Check / Valid</TD></B><TD><B>Omit Code</TD></B></TD></TR><TR><TD>'+@SOR+'</TD><TD>'+@desc+'</TD><TD>'+@quantity+'</TD><TD>[___]</TD><TD>[___]</TD></TD></TR></TABLE><TABLE WIDTH="60%" BORDER="0" BORDERCOLOR="#CCCCCC" CELLPADDING="0" CELLSPACING="20" ><TD></TD><TR><TD><P Align="Right">______________________________________</TD></TR></P><TR><TD><P Align="Right"><B>Signature of Supervisor</B></TD></TR></P></TABLE></HTML>',
@messagefile = 'c:\Program Files\Common Files\System\Mapi\1033\NT\MikeHTML.htm',
@dumpmsg = 'c:\Program Files\Common Files\System\Mapi\1033\NT\dumpmsg.log',
@type = N'text/html',
@attachments = N'',
@codepage = 0

exec @rc = [ken-sql-001].master.dbo.xp_smtp_sendmail -- does the email sending
@FROM = @FROM,
@TO = @TO,
@CC = @CC,
@BCC = @BCC,
@priority = @priority,
@subject = @subject,
@message = @message,
--@messagefile = @messagefile,
@dumpmsg = @dumpmsg,
@type = @type,
@attachments = @attachments,
@codepage = @codepage,
@server = N'192.168.25.22'

-- Delete current row from #validSOR
--DELETE
--FROM #validSOR
--WHERE x = @i

update msp
set email_sent_check_flag = 1 -- to show in the MSP table if the emails have been sent for each row
from [ken-sql-001].dataloads.dbo.mobile_solution_process msp
where @jobno = work_order

-- Get next least row identifier, larger than current
SELECT @i = min(x)
FROM #validSOR
WHERE x > @i
END

/*
update msp
set email_sent_check_flag = 1 -- to show in the MSP table that the emails have been sent
from [ken-sql-001].dataloads.dbo.mobile_solution_process msp
inner join #validSOR
on #validSOR.jobno = msp.work_order
*/

select RC = @rc
GO
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site