Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Second attempt to resolve a messy issue

Posted on 2011-02-23
2
Medium Priority
?
275 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 2000 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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

916 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