Solved

Second attempt to resolve a messy issue

Posted on 2011-02-23
2
259 Views
Last Modified: 2012-05-11
I have a stored procedure that creates an email for registrants for webcasts.  As part of the process a link is added in the email with the title of the webcast and a link to a site that connects the user to Adobe Connect.

The process works and then it will for no apparent reason insert CR/LF randomly in the URL string.  However if the process is run manually it always inserts the CR/LF in the same place for the same URL.

A different URL will break in a different place.

I have attached the SP and a Copy of the value from our database and that value whin inserted into the HTML email.

Any Ideas? I'm really befuddled

Running SQL Server 2000 on a Server 2003 box

















HTML TEMPLATE FILE

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Untitled Document</title>
</head>

<body>
<p>Prog_Title<strong></strong><br />
Prog_Date, Start_Time &ndash; End_Time</p>
<p><strong>Three things you need to do BEFORE tomorrow&rsquo;s (or Monday&rsquo;s) webcast:</strong></p>
<ol>
  <li>To avoid technical problems during the program, conduct  an automated <u><a href="http://www.mcle.org/on-demand-programs/webcast-technical-information.cfm">systems  check</a></u> to ensure technical compatibility and view a <u><a href="http://www.mcle.org/on-demand-programs/sample-ondemand-audio-program.cfm">demo</a></u> on our site. If you can hear and see the  demo, then your computer has the technical requirements. The separate systems check will analyze your  computer and let you know whether you have the necessary hardware and software  to run MCLE webcasts. If you don&rsquo;t have  the required software, the systems check will point you to a website to get a  free download. If you encounter any  difficulty, please call Pam Chapin, Customer Service Manager, today at (617)  896-1590.<br />&nbsp;

  </li>
  <li>Print out a copy of the <u><a href="http://www.mcle.org/includes/pdf/Webcast_Troubleshooting_Tips.pdf">troubleshooting  tips</a></u> in the event you encounter difficulty during the program.<br />
&nbsp; </li>
  <li>Download or print the written materials for the  program, which, for your convenience, are made available 24 hours in advance of  the webcast. Please note that all  written materials are available to webcast registrants electronically  only.&nbsp; </li>
</ol>
<p>Use the link below to access the  webcast page to download the materials in advance of the program and to view  the webcast at the time of the program.</p>
<p><strong>embedded_link</strong></p>
<p><strong>Questions?</strong> If you encounter  technical difficulties, please call Pam Chapin at (617) 896-1590.</p>
<p>Thank you and we hope you enjoy the MCLE Webcast experience.</p>
</body>
</html>
Stored procedure that runs nightly to send emails to anyone
who has signed up for a webcast.




SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO




ALTER     PROCEDURE SP_WEBCASTEMAILREMINDERS
as
declare @testdate datetime
declare @dateonly varchar(10)
declare @dayofweek varchar(15)
SELECT @dayofweek = DATENAME(dw, GETDATE())
select @dateonly = convert(char(10), getdate(), 101)
select @testdate =
case
	when @dayofweek = 'Monday' then dateadd(dd,2,@dateonly)
	when @dayofweek = 'Tuesday' then dateadd(dd,2,@dateonly)
	when @dayofweek = 'Wednesday' then dateadd(dd,2,@dateonly)
	when @dayofweek = 'Thursday' then dateadd(dd,4,@dateonly)
	when @dayofweek = 'Friday' then dateadd(dd,4,@dateonly)
end
select
	n.email,
	mm.meeting,
	bcu.url,
	convert(varchar(10),mm.begin_date,101) as begindate,
	convert(varchar(250),mm.description)as description,
	(SELECT
     	CONVERT(varchar(2),
          CASE
               WHEN DATEPART([hour], pf.begin_date_time) > 12 THEN CONVERT(varchar(2), (DATEPART([hour], pf.begin_date_time) - 12))
               WHEN DATEPART([hour], pf.begin_date_time) = 0 THEN '12'
               ELSE CONVERT(varchar(2), DATEPART([hour], pf.begin_date_time))
          END
	     ) + ':' +
	CONVERT(char(2), SUBSTRING(CONVERT(char(5), pf.begin_date_time, 108), 4, 2)) + ' ' + 
        CONVERT(varchar(2),
          CASE
               WHEN DATEPART([hour], pf.begin_date_time) > 12 THEN 'PM'
               ELSE 'AM'
          END
     )) AS begintime,


	(SELECT
     	CONVERT(varchar(2),
          CASE
               WHEN DATEPART([hour], pf.end_date_time) > 12 THEN CONVERT(varchar(2), (DATEPART([hour], pf.end_date_time) - 12))
               WHEN DATEPART([hour], pf.end_date_time) = 0 THEN '12'
               ELSE CONVERT(varchar(2), DATEPART([hour], pf.end_date_time))
          END
     	) + ':' +
     	CONVERT(char(2), SUBSTRING(CONVERT(char(5), pf.end_date_time, 108), 4, 2)) + ' ' + 
     	CONVERT(varchar(2),
          CASE
               WHEN DATEPART([hour], pf.end_date_time) > 12 THEN 'PM'
               ELSE 'AM'
          END
     	)) AS endtime
into #tmp1
from meet_master mm
inner join order_lines ol on ol.product_code = mm.meeting + '/reg'
inner join orders o on o.order_number = ol.order_number
inner join name n on n.id = o.bt_id
inner join product_function pf on pf.product_code = mm.meeting + '/reg'
inner join BinaryCreatedURL bcu on bcu.id = n.id and bcu.product_code = mm.meeting
where right(mm.meeting,3) = 'WBC' and mm.begin_date = @testdate
order by mm.begin_date, n.last_name
select * from #tmp1

--
-- Use (Gasp!!) cursor to process records from dataset
--

declare @embedded varchar(800)
declare @email varchar(100)
declare @Meeting varchar(10)
declare @url varchar(800)
declare @begindate varchar(10)
declare @description varchar(250)
declare @begintime varchar(10)
declare @endtime varchar(10)
DECLARE @FileName varchar(255)
DECLARE @ExecCmd VARCHAR(255)
DECLARE @y INT
DECLARE @x INT
DECLARE @FileContents VARCHAR(8000)

declare @body varchar(2000)
DECLARE db_cursor CURSOR FOR  
SELECT email,meeting,url,begindate,description,begintime,endtime
FROM #tmp1

OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @email,@meeting,@url,@begindate,@description,@begintime,@endtime 

WHILE @@FETCH_STATUS = 0   
BEGIN 
--
-- Open template file
--
	CREATE TABLE #tempXML(PK INT NOT NULL IDENTITY(1,1), ThisLine VARCHAR(255))
	SET @FileName = 'c:\email_reminder.html'
	SET @ExecCmd = 'type ' + @FileName

	SET @Filecontents = ''

	INSERT INTO #tempXML EXEC master.dbo.xp_cmdshell @ExecCmd
	SELECT @y = count(*) from #tempXML

	SET @x = 0
	WHILE @x <> @y
    	BEGIN
        	SET @x = @x + 1
        	SELECT @FileContents = @FileContents + ThisLine from #tempXML WHERE PK
		 = @x and thisline is not null
    	END
--
-- Perform substitutions
--
  	set @embedded = '<u><a href="' + @url + '">' + @description + '</a></u>'

	select @filecontents = replace(@filecontents,'Prog_Title',@description)
	select @filecontents = replace(@filecontents,'Prog_Date',@begindate)
	select @filecontents = 	replace(@filecontents,'Start_Time',@begintime)
	select @filecontents = 	replace(@filecontents,'End_Time',@endtime)
	select @filecontents = 	replace (@filecontents,'embedded_link',@embedded)

	declare @rc int
	exec @rc = master.dbo.xp_smtp_sendmail
	@FROM		= N'customerservice@mcle.org',
	@TO			= @email,
	@BCC			= N'jkingston@mcle.org',
	@subject		= N'MCLE Webcast – Important Information for Tomorrow’s (or Monday’s) Webcast',
	@message		= @filecontents,
	@type			= N'text/html',
	@server 		= N'mclemail01'
	select RC = @rc 
	drop table #tempXML
        FETCH NEXT FROM db_cursor INTO @email,@meeting,@url,@begindate,@description,@begintime,@endtime    
END   

CLOSE db_cursor   
DEALLOCATE db_cursor 
drop table #tmp1
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

Open in new window

Stored procedure that runs nightly to send emails to anyone
who has signed up for a webcast.




SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO




ALTER     PROCEDURE SP_WEBCASTEMAILREMINDERS
as
declare @testdate datetime
declare @dateonly varchar(10)
declare @dayofweek varchar(15)
SELECT @dayofweek = DATENAME(dw, GETDATE())
select @dateonly = convert(char(10), getdate(), 101)
select @testdate =
case
	when @dayofweek = 'Monday' then dateadd(dd,2,@dateonly)
	when @dayofweek = 'Tuesday' then dateadd(dd,2,@dateonly)
	when @dayofweek = 'Wednesday' then dateadd(dd,2,@dateonly)
	when @dayofweek = 'Thursday' then dateadd(dd,4,@dateonly)
	when @dayofweek = 'Friday' then dateadd(dd,4,@dateonly)
end
select
	n.email,
	mm.meeting,
	bcu.url,
	convert(varchar(10),mm.begin_date,101) as begindate,
	convert(varchar(250),mm.description)as description,
	(SELECT
     	CONVERT(varchar(2),
          CASE
               WHEN DATEPART([hour], pf.begin_date_time) > 12 THEN CONVERT(varchar(2), (DATEPART([hour], pf.begin_date_time) - 12))
               WHEN DATEPART([hour], pf.begin_date_time) = 0 THEN '12'
               ELSE CONVERT(varchar(2), DATEPART([hour], pf.begin_date_time))
          END
	     ) + ':' +
	CONVERT(char(2), SUBSTRING(CONVERT(char(5), pf.begin_date_time, 108), 4, 2)) + ' ' + 
        CONVERT(varchar(2),
          CASE
               WHEN DATEPART([hour], pf.begin_date_time) > 12 THEN 'PM'
               ELSE 'AM'
          END
     )) AS begintime,


	(SELECT
     	CONVERT(varchar(2),
          CASE
               WHEN DATEPART([hour], pf.end_date_time) > 12 THEN CONVERT(varchar(2), (DATEPART([hour], pf.end_date_time) - 12))
               WHEN DATEPART([hour], pf.end_date_time) = 0 THEN '12'
               ELSE CONVERT(varchar(2), DATEPART([hour], pf.end_date_time))
          END
     	) + ':' +
     	CONVERT(char(2), SUBSTRING(CONVERT(char(5), pf.end_date_time, 108), 4, 2)) + ' ' + 
     	CONVERT(varchar(2),
          CASE
               WHEN DATEPART([hour], pf.end_date_time) > 12 THEN 'PM'
               ELSE 'AM'
          END
     	)) AS endtime
into #tmp1
from meet_master mm
inner join order_lines ol on ol.product_code = mm.meeting + '/reg'
inner join orders o on o.order_number = ol.order_number
inner join name n on n.id = o.bt_id
inner join product_function pf on pf.product_code = mm.meeting + '/reg'
inner join BinaryCreatedURL bcu on bcu.id = n.id and bcu.product_code = mm.meeting
where right(mm.meeting,3) = 'WBC' and mm.begin_date = @testdate
order by mm.begin_date, n.last_name
select * from #tmp1

--
-- Use (Gasp!!) cursor to process records from dataset
--

declare @embedded varchar(800)
declare @email varchar(100)
declare @Meeting varchar(10)
declare @url varchar(800)
declare @begindate varchar(10)
declare @description varchar(250)
declare @begintime varchar(10)
declare @endtime varchar(10)
DECLARE @FileName varchar(255)
DECLARE @ExecCmd VARCHAR(255)
DECLARE @y INT
DECLARE @x INT
DECLARE @FileContents VARCHAR(8000)

declare @body varchar(2000)
DECLARE db_cursor CURSOR FOR  
SELECT email,meeting,url,begindate,description,begintime,endtime
FROM #tmp1

OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @email,@meeting,@url,@begindate,@description,@begintime,@endtime 

WHILE @@FETCH_STATUS = 0   
BEGIN 
--
-- Open template file
--
	CREATE TABLE #tempXML(PK INT NOT NULL IDENTITY(1,1), ThisLine VARCHAR(255))
	SET @FileName = 'c:\email_reminder.html'
	SET @ExecCmd = 'type ' + @FileName

	SET @Filecontents = ''

	INSERT INTO #tempXML EXEC master.dbo.xp_cmdshell @ExecCmd
	SELECT @y = count(*) from #tempXML

	SET @x = 0
	WHILE @x <> @y
    	BEGIN
        	SET @x = @x + 1
        	SELECT @FileContents = @FileContents + ThisLine from #tempXML WHERE PK
		 = @x and thisline is not null
    	END
--
-- Perform substitutions
--
  	set @embedded = '<u><a href="' + @url + '">' + @description + '</a></u>'

	select @filecontents = replace(@filecontents,'Prog_Title',@description)
	select @filecontents = replace(@filecontents,'Prog_Date',@begindate)
	select @filecontents = 	replace(@filecontents,'Start_Time',@begintime)
	select @filecontents = 	replace(@filecontents,'End_Time',@endtime)
	select @filecontents = 	replace (@filecontents,'embedded_link',@embedded)

	declare @rc int
	exec @rc = master.dbo.xp_smtp_sendmail
	@FROM		= N'customerservice@mcle.org',
	@TO			= @email,
	@BCC			= N'jkingston@mcle.org',
	@subject		= N'MCLE Webcast – Important Information for Tomorrow’s (or Monday’s) Webcast',
	@message		= @filecontents,
	@type			= N'text/html',
	@server 		= N'mclemail01'
	select RC = @rc 
	drop table #tempXML
        FETCH NEXT FROM db_cursor INTO @email,@meeting,@url,@begindate,@description,@begintime,@endtime    
END   

CLOSE db_cursor   
DEALLOCATE db_cursor 
drop table #tmp1
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

Open in new window

before-and-after.txt
0
Comment
Question by:Jeff_Kingston
2 Comments
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
ID: 34963413
If you go to the website for the xp_smtp_sendmail you will read an explanation for the email limitation of 998 bytes and a workaround.
0
 

Author Closing Comment

by:Jeff_Kingston
ID: 35086336
Simply added a CR/LF to the html file in front of the URL substitute value and the issue is gone.
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question