October 20, 2011 at 3:07 am
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
October 20, 2011 at 3:14 am
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: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.
October 20, 2011 at 3:19 am
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.
October 20, 2011 at 4:16 am
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?
Im using sqlserver 2008 r2 standard collation, which appears not case sensetive.
October 20, 2011 at 4:17 am
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.
October 20, 2011 at 4:32 am
Suresh B. (10/20/2011)
Try this.
USE testingGO
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
October 20, 2011 at 5:12 am
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//
October 20, 2011 at 5:25 am
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
October 20, 2011 at 5:34 am
@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.
October 24, 2011 at 2:49 am
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!
October 24, 2011 at 2:55 am
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'
October 24, 2011 at 6:53 am
@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.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply