T-SQL how to add a line feed to a string that is sent as an email

[DougP]

I would like to have each persons name on a separate line.
instead of: Names > Castro, Pavel<br/>Espinal Guerrero, Danilo<br/> ...
I also tried + CHAR(13) + CHAR(10) and that did'nt work either.

	DECLARE mycursor CURSOR LOCAL FAST_FORWARD FOR 
	Select  Distinct top 5 Email from #TEMP
			OPEN mycursor  
			  -- Always true  
			 WHILE 1 = 1  
			 BEGIN     
			  -- Get next record from cursor   
			FETCH NEXT FROM mycursor        
            INTO @Email
            IF @@FETCH_STATUS <> 0    
				break 
			Else
				Begin
				Select @Names = @Names + ResourceLastName + ', ' + ResourceFirstName + '<br/>'					                          from #TEMP
				Where Email= @Email
				end
				Select @Email + ' Names > ' +	@Names 
				Set @strBody = @Email + ' Names > ' +	@Names
			 --Send the email to managers
				EXECUTE ToolsTeam.[dbo].[uspSendEmail] 
				   @strFromName
				  ,'test1@one.verizon.com' --,test2@verizon.com' --@strTo
				  ,@strCC
				  ,@strBcc
				  ,@strSubject
				  ,@strBody
				  ,@strSmtpServer
				  ,@bReturnCode OUTPUT
				  ,@strErrorMsg OUTPUT
				set @Names = '
			end
  CLOSE mycursor 
  DEALLOCATE mycursor

DougP

 

[gmmastros]

Can you post the code for ToolsTeam.dbo.uspSendEmail ?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[DougP]

sorry I don't have it, was given to me to use. I wanted to send email and they said use this,

Is the issue in there maybe? its converting it to string?

Let me see what I can find out.

DougP

 

[gmmastros]

It would certainly be helpful to know what is in that stored procedure. I mean... it coudl be doing anything, right?

Usually, email routines have a "body format" that allows you to send text emails or HTML emails. If it's text, then you should use CHAR(13) + Char(10). If it's HTML, then you should use "<br/>".

For example...

http://msdn.microsoft.com/en-us/library/ms190307.aspx

Since you tried both, I am suspicious of the uspSendEmail code.



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

try running this:

ToolsTeam.dbo.sp_helptext 'uspSendEmail'

Depending on permissions, this may show you the code underneath the send email procedure. Cross your fingers before running this.


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[DougP]

gmmastros,
ToolsTeam.dbo.sp_helptext 'uspSendEmail' I get message saying:
Msg 15197, Level 16, State 1, Procedure sp_helptext, Line 107
There is no text for object 'uspSendEmail'.

but... If I change it to
ToolsTeam.dbo.sp_help 'uspSendEmail' , I get the following:

Parameter_name Type Length Prec Scale Param_order Collation
@strFromName nvarchar 4096 2048 NULL 1 SQL_Latin1_General_CP1_CI_AS
@strTo nvarchar 4096 2048 NULL 2 SQL_Latin1_General_CP1_CI_AS
@strCc nvarchar 4096 2048 NULL 3 SQL_Latin1_General_CP1_CI_AS
@strBcc nvarchar 4096 2048 NULL 4 SQL_Latin1_General_CP1_CI_AS
@strSubject nvarchar 4096 2048 NULL 5 SQL_Latin1_General_CP1_CI_AS
@strBody nvarchar -1 0 NULL 6 SQL_Latin1_General_CP1_CI_AS
@strSmtpServer nvarchar 4096 2048 NULL 7 SQL_Latin1_General_CP1_CI_AS
@bReturnCode bit 1 1 NULL 8 NULL
@strErrorMsg nvarchar 4096 2048 NULL 9 SQL_Latin1_General_CP1_CI_AS

Also I emailed the person who gave me the SP to get some insight.

DougP

 

[gmmastros]

This means you don't have permissions to view the code. sp_help is only showing the parameter definitions, which can be mildly helpful except in this case. There doesn't seem to be a parameter to control the body format.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[DougP]

Ok here is what I found out, I was given a chunk of text in Excel, about 10 lines. It has carriage return, line feeds in it.
I created an ODBC link in Access 2010 to the SQL table in question. I "drug" the ROW down in Access tall enough to see the whole thing. I copied and pasted the text from Excel into EmailBody column in the first row in Access. so the table only has one record. it shows all the lines just like they should appear in Access.
The column EmailBody is a nvarchar(max) data type in the SQL table.
I added this line > Set @strBody =(Select EmailBody from SOWEmailManagers) to my stored procedure.
also this SET @vbcrlf = CHAR(13)
So when it sends the email it has carriage return in it.
If I add something to @strBody with a @vbcrlf it keeps the formatting and continues the carriage return line feed just fine.
If I however try and put the @strBody into another variable, say Set @myVar = @strBody + @SomeHyperlinkVar + @vbcrlf
it will not put a carriage return on the end of it.
So the original Stored Procedure uspSendEmail, is untouched.
Thanks all.

EXECUTE ToolsTeam.[dbo].[uspSendEmail] 
                   @strFromName
                  ,'test1@test.com' --,test2@test.com' --@strTo
                  ,@strCC
                  ,@strBcc
                  ,@strSubject
                  [highlight #FCE94F],@strBody[/highlight]
                  ,@strSmtpServer
                  ,@bReturnCode OUTPUT
                  ,@strErrorMsg OUTPUT

DougP

 

[DougP]

another thought, this works too; adds CR LF to email just fine?
which uses "REPLACE" function.

DECLARE @Email  nvarchar(150)
DECLARE @vbCrLf nvarchar(255)
DECLARE @Names nvarchar(MAX)

SET @vbCrLf = CHAR(13)
				Begin
					
					Select @Names = @Names + '|' + ResourceLastName + ', ' + ResourceFirstName  
							from #TEMP
					Where Email= @Email
					[highlight #FCE94F]SET @Names =(SELECT REPLACE(@Names,'|',@vbCrLf))[/highlight]
					--Print @Names
					Insert into dbo.SOWEmailManagerListResource
					(Email,Names) Values (@Email,@Names)
				end
				
				Select @Email + ' Names > ' +	@Names 
				set @strBody = @Email
				Select @strBody = @strBody +  Names from SOWEmailManagerListResource Where Email =@Email
				Set @strBody = @strBody + @vbCrLf 
				Set @strBody = @strBody + 'Click Link below to go to Managers Approval screen' + @vbCrLf 
				Set @strBody = @strBody + @ManagerLink + @vbCrLf 
				
			 --Send the email to managers
				EXECUTE ToolsTeam.[dbo].[uspSendEmail] 
				   @strFromName
				  ,test@test.com'
				  ,@strCC
				  ,@strBcc
				  ,@strSubject
				  ,@strBody
				  ,@strSmtpServer
				  ,@bReturnCode OUTPUT
				  ,@strErrorMsg OUTPUT

DougP