Solved

Second attempt to resolve a messy issue

Posted on 2011-02-23
2
254 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
Comment Utility
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
Comment Utility
Simply added a CR/LF to the html file in front of the URL substitute value and the issue is gone.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
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
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

771 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now