Database mail question

Question

Database mail question

memymasta

SSCrazy

Points: 2373
More actions
October 20, 2011 at 3:07 am

#390662

I'm investigating a possible mailing system that will send automated emails to our customers.
Came across database mail function in sqlserver 2008 r2 and it looks really promising.
I'm new to tsql and having some problems understanding how to assign a variable to recipients.
I get 'Must declare the scalar variable "@myEmailString".' error on this row -> @recipients=@myEmailString, in the tsql code below
declare@MyEmailString varchar(MAX)
USE testing
select@MyEmailString = coalesce(@MyEmailString + ';', '') + coalesce(useremail, '')
fromusers
USE msdb
GO
EXEC sp_send_dbmail @profile_name='test',
@recipients=@myEmailString,
@subject='Test message',
@body='testing testing'
thanks for your time

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

derek.colley SSCrazy Eights Points: 8040 More actions · Answer 1

Be careful with this. One of my customers wrote a VBScript-based app to send automated e-mails using ASP and coded in plaintext connection strings (passwords and all) to a SQL Server database which had grown, uncontrolled, from just a few tables into a monster. Now, with all the inherent problems, I've recommended it's ripped up and replaced with something off-the-shelf.

Database Mail might be one way to go but have you considered using the pre-built assemblies in Visual Studio etc to handle the e-mail build and transmission tasks, and simply send back a call to a stored procedure in the database, along with the parameters, to retrieve the info instead?

---

Note to developers:
CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
So why complicate your code AND MAKE MY JOB HARDER??!:crazy:

Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.

Ant-Green SSC Guru Points: 113507 More actions · Answer 2

What is the collation of the server / database you are using?

It may be a simple thing as if you have a case sensitive (CS) collation, you have declared the variable as @MyEmailString, but using @myEmailString in the sp_send_dbmail

if it is CS then M and m are two different letters for SQL and will say you dont have a variable declared.

memymasta SSCrazy Points: 2373 More actions · Answer 3

@Derek

Thats pretty much the options i got, either write a ASP VBScript app or use Database Mail together with SSA Jobs... Trying to figure out wich one is most suited.

Could you suggest any off-the-shelf product that could work?

@anthony-2

Im using sqlserver 2008 r2 standard collation, which appears not case sensetive.

Suresh B. SSC-Insane Points: 22986 More actions · Answer 4

Try this.

USE testing

GO

declare@MyEmailString varchar(MAX)

select@MyEmailString = coalesce(@MyEmailString + ';', '') + coalesce(useremail, '')

fromtesting.dbo.users

EXEC msdb.dbo.sp_send_dbmail @profile_name='test',

@recipients=@myEmailString,

@subject='Test message',

@body='testing testing'

Don't use GO after declaring a variable.

Ant-Green SSC Guru Points: 113507 More actions · Answer 5

Suresh B. (10/20/2011)
Try this.
USE testing
GO
declare@MyEmailString varchar(MAX)
select@MyEmailString = coalesce(@MyEmailString + ';', '') + coalesce(useremail, '')
fromtesting.dbo.users
EXEC msdb.dbo.sp_send_dbmail @profile_name='test',
@recipients=@myEmailString,
@subject='Test message',
@body='testing testing'
Don't use GO after declaring a variable.

Good point Suresh, I didn't notice that. The variable will be removed from memory after the go as its the end of the batch and doesnt need to be carried over into the next batch

Ryan007 SSCrazy Eights Points: 8275 More actions · Answer 6

what about multiple rows in testing.dbo.users? mail will be sent sent to the first one. Rest will be missed.

Ryan
//All our dreams can come true, if we have the courage to pursue them//

Suresh B. SSC-Insane Points: 22986 More actions · Answer 7

Sumanta Roy (10/20/2011)
what about multiple rows in testing.dbo.users? mail will be sent sent to the first one. Rest will be missed.

I think this takes care of that:

select @MyEmailString = coalesce(@MyEmailString + ';', '') + coalesce(useremail, '')

from testing.dbo.users

memymasta SSCrazy Points: 2373 More actions · Answer 8

@suresh

That did the trick, thank you!

Now it works to send emails to several recepients, however it doesnt seem very scalable... next step, figure out how to create a loop that inserts email addresses into recipients.

Ill keep you updated.

memymasta SSCrazy Points: 2373 More actions · Answer 9

New day, new challenges...

Here's how the script looks like, as it is now i create a temp table and populate it with data (id,email, firstname), then it loops through every record and sends an email to coresponding address.

declare @MyId char( 11 )

declare @MyEmail varchar(MAX)

declare @MyFirstname varchar(MAX)

use testing

set rowcount 0

select id,firstname,useremail into #MyTempTable from users

set rowcount 1

select @MyId = id from #MyTempTable

while @@rowcount <> 0

begin

set rowcount 0

select @MyEmail = useremail,@MyFirstname=firstname from #MyTempTable where id = @MyId

EXEC msdb.dbo.sp_send_dbmail @profile_name='test',

@blind_copy_recipients=@MyEmail,

@subject=@MyFirstname+' have a good day!' ,

@body='testing testing'

delete #MyTempTable where id = @MyId

set rowcount 1

select @MyId = id from #MyTempTable

end

set rowcount 0

DROP TABLE #MyTempTable

I have problem with this row, where im trying to add a variable to the string... i get this error "Incorrect syntax near '+'."

@subject=@MyFirstname+' have a good day!' ,

Thanks for your time!

Suresh B. SSC-Insane Points: 22986 More actions · Answer 10

The reason is expression is NOT allowered there.

Try this:

select @MyEmail = useremail,@MyFirstname=firstname+' have a good day!' from #MyTempTable where id = @MyId

EXEC msdb.dbo.sp_send_dbmail @profile_name='test',

@blind_copy_recipients=@MyEmail,

@subject=@MyFirstname ,

@body='testing testing'

memymasta SSCrazy Points: 2373 More actions · Answer 11

@suresh Ty, you were right once again.

I found it useful to use: set @Subject_tets = 'some text' + @variable + 'some text', instead of having it in the select clause.