Email...@message[RESOLVED] - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Email...@message[RESOLVED]

Author

Topic

brendalisalowe
Constraint Violating Yak Guru

269 Posts

Posted - 2004-12-15 : 17:56:14

When I run this, the 'message' is blank, even though I have 2 cases in there. Any one see why?


declare @counter int
declare @length int
declare @UniqueID int
declare @subjname varchar(10)
declare @MyMessage varchar(1000)

	set @length = (select count(CaseNumber) from tblEmail)
	set @counter = 0
	set @UniqueID = (select top 1 UniqueID from tblEmail Order By UniqueID)
	set @subjname = (select CaseNumber from tblEmail where UniqueID = @UniqueID)
	set @MyMessage =  @subjname 
		
	while @counter < @length
	begin			
	set @counter = @counter + 1
	set @UniqueID = @UniqueID + 1
	set @subjname = (select CaseNumber from tblEmail where UniqueID = @UniqueID)
	set @MyMessage = @MyMessage + '  ' + @subjname
	end
	
		exec master..xp_sendmail 
		@recipients = 'brenda@capitaltracer.com', 
		@subject = 'Cases Imported Into The Fee Paid Table',
		@message = @MyMessage

Also, I know there is a debugger in QA, but I don't know how to use it. Can anyone help me on that?

Brenda

If it weren't for you guys, where would I be?

nr
SQLTeam MVY

12543 Posts

Posted - 2004-12-15 : 18:47:55

After the set @MyMessage statements put a
select @MyMessage
to display how it is being built.
You probably have a null CaseNumber which is losing the message.
maybe it should be
while @counter < @length - 1

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

brendalisalowe
Constraint Violating Yak Guru

269 Posts

Posted - 2004-12-17 : 17:03:09

It works now. Thanks for your help!


declare @counter int
declare @length int
declare @UniqueID int
declare @subjname varchar(10)
declare @MyMessage varchar(1000)
declare @CRLF char(2)

	set @length = (select count(CaseNumber) from tblEmail)
	set @counter = 0
	set @UniqueID = (select top 1 UniqueID from tblEmail Order By UniqueID)
	set @subjname = (select CaseNumber from tblEmail where UniqueID = @UniqueID)
	set @MyMessage =  @subjname 
	set @CRLF = char(10) + char(13) 
		
	while @counter < @length - 1 
	begin			
	set @counter = @counter + 1
	set @UniqueID = @UniqueID + 1
	set @subjname = (select CaseNumber from tblEmail where UniqueID = @UniqueID)
	set @MyMessage = @MyMessage + @CRLF + @subjname	
	end

	set @MyMessage = 'Here are the cases that were transferred over to the Fee Paid Table last night:'
			+ @CRLF  + @CRLF + @MyMessage 
	
		exec master..xp_sendmail 
		@recipients = 'brenda@capitaltracer.com', 
		@subject = 'Cases Imported Into The Fee Paid Table',
		@message = @MyMessage

Brenda

If it weren't for you guys, where would I be?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources