Automated welcome email for new users

Question

Automated welcome email for new users

vinodmmec916

SSC Veteran

Points: 205
More actions
August 4, 2014 at 1:00 am

#310426

Hi community,
I want to know whether we can create an automated html format mail for new users and it has to be immediate as soon as a new user will be there a welcome email will be sent to him .
Is it possible with sql server, because we can generate mails for job failures and all. I hope will get some positive reply asap.
Thanks and Regards
Vinod Kumar

Viewing 15 posts - 1 through 15 (of 18 total)

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

Ant-Green SSC Guru Points: 113563 More actions · Answer 1

Yes, create a trigger on the "users" table for insert, so when a row is inserted then it sends a mail via sp_send_dbmail

vinodmmec916 SSC Veteran Points: 205 More actions · Answer 2

Can u tell me how to create the code , I am a fresher so don't know much about this , please help

thanks 🙂

Koen Verbeeck SSC Guru Points: 259197 More actions · Answer 3

vinodmmec916 (8/4/2014)
Can u tell me how to create the code , I am a fresher so don't know much about this , please help
thanks 🙂

Did you google for "create trigger"?

Did you check the documentation for sp_send_dbmail?

Why don't you try to write some code first, and then come back if you have any issues.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Koen Verbeeck SSC Guru Points: 259197 More actions · Answer 4

Here are some starting points:

SQL Server track DDL CREATE USER

Email alerts when new databases are created in SQL Server[/url]

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

vinodmmec916 SSC Veteran Points: 205 More actions · Answer 5

Hi all,

I have tried creating a database mail , i have configured the db mail server , and when i have executed the following query

declare @body1 varchar(4000)

set @body1 = '<head>

<title> Embedded Logo Example</title>

</head>

<body>

<table><tr><td valign="top" align="left">MyHeader</td></tr>

</table>

</body>'

EXEC msdb.dbo.sp_send_dbmail

@profile_name='E Mail',

@recipients='abc@def.com',

@subject = 'SQl 2008 email test',

@body = @body1,

@body_format = 'HTML',

@file_attachments = '?E:\clip-art-flowers-285113.jpg'

i am getting error as

Msg 22051, Level 16, State 1, Line 0

Attachment file ?E:\clip-art-flowers-285113.jpg is invalid.

kindly suggest some answers

Thanks

Vinod Kumar

Ant-Green SSC Guru Points: 113563 More actions · Answer 6

Ant-Green

SSC Guru

Points: 113563

August 6, 2014 at 4:44 am

#1735560

Does that file exist on the SQL servers E drive?

vinodmmec916 SSC Veteran Points: 205 More actions · Answer 7

nopes , that is in my local machines E drive , Actually i just need to check weather i ll get the image also in the email . so is there any way by which i can check this .

Ant-Green SSC Guru Points: 113563 More actions · Answer 8

Put the file on the SQL servers E drive. Use UNC shares instead ensuring that the SQL service account has the necessary rights to read from the share

Not a HTML guy so couldn't tell you if you would get the image in the body or not

vinodmmec916 SSC Veteran Points: 205 More actions · Answer 9

okay let me try using this approach , thanks for the help .

thanks and Regards

Vinod kumar

vinodmmec916 SSC Veteran Points: 205 More actions · Answer 10

Hi all, as i have already discussed with u guys that i need to create a trigger for the email sending

so i need to ask that suppose there is a table users in which i am inserting user details , so i need to check that whenever a new user which does not exists in the user table is getting added in the user table an email will be sent ,

this email part i will manage by calling some sp. but what will be the condition which will check weather the new inserted record is already there in the table or not .

thanks and regards

Vinod Kumar

vinodmmec916 SSC Veteran Points: 205 More actions · Answer 11

Hi all, as i have already discussed with u guys that i need to create a trigger for the email sending

so i need to ask that suppose there is a table users in which i am inserting user details , so i need to check that whenever a new user which does not exists in the user table is getting added in the user table an email will be sent ,

this email part i will manage by calling some sp. but what will be the condition which will check weather the new inserted record is already there in the table or not .

thanks and regards

Vinod Kumar

Ant-Green SSC Guru Points: 113563 More actions · Answer 12

Would all depend on the DDL of the users table?

Do you know what this is? Can you provide it along with sample data, indexes and how your data loads happen? Do they happen in batches? Single rows at a time?

vinodmmec916 SSC Veteran Points: 205 More actions · Answer 13

Hi all ,

with help from u guys n my lil effort i am able to configure an html formatted email , thanks a lot for this .

but now there is one more requirement in which i want to check weather i can check the status of the mails sent or unsent , i am actually checking it from msdb.dbo.sysmail_event_log and after checking i have to update the status in another table. but sometimes the mail will take more time to be sent so hence after the execution of sp_send_dbmail when i update the status it shows unsent because the server takes some time to send the mail , hence i have even tried to give some delay but even that is not working in all the cases.

So kindly suggest me something by which i can set the mail status correctly , one way is creating abatch job whcih will check the status , is there some other way ?

Thanks n Regards

Vinod Kumar

Ant-Green SSC Guru Points: 113563 More actions · Answer 14

for each mail you send capture the mail id in a table and poll the mail table for the status, when it changes update the table to say its been sent.