Solved

Second attempt to resolve a messy issue

Posted on 2011-02-23
2
267 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

635 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