SQL send Birthday email from query
0
I have a database "Insurance" with a table "Customer"
CustomerID
FirstName
LastName
Birthday
Email
I created a query to see the birthdays today
use Insurance
go
select FirstName,LastName,Email, [Birthday]
from Customer
WHERE DAY([Birthday]) = DAY(GETDATE())
AND MONTH([Birthday]) = MONTH(GETDATE())
This works fine.
Next I created the Database Mail account, and changed the code to:
use Insurance
go
DECLARE
@out_desc VARCHAR(1000),
@out_mesg VARCHAR(10)
DECLARE @name VARCHAR(20),
@birthdate datetime,
@email NVARCHAR(50)
DECLARE @body NVARCHAR(1000)
DECLARE C1 CURSOR READ_ONLY
FOR
SELECT [FirstName], [Birthday], [Email]
FROM Customer
OPEN C1
FETCH NEXT FROM C1 INTO
@name, @birthdate, @email
WHILE @@FETCH_STATUS = 0
BEGIN
IF DATEPART(DAY,@birthdate) = DATEPART(DAY,GETDATE())
AND DATEPART(MONTH,@birthdate) = DATEPART(MONTH,GETDATE())
BEGIN
SET @body = '<b>Happy Birthday ' + @name +
'</b><br />Many happy returns of the day'
+ '<br /><br />Customer Relationship Department'
EXEC sp_send_dbmail
'sender@gmail.com',
'password',
@email,
'Birthday Wishes',
@body,
'htmlbody',
@output_mesg = @out_mesg output,
@output_desc = @out_desc output
PRINT @out_mesg
PRINT @out_desc
END
FETCH NEXT FROM C1 INTO
@name, @birthdate, @email
END
CLOSE C1
DEALLOCATE C1
I receive a error:
Msg 2812, Level 16, State 62, Line 57
Could not find stored procedure 'sp_send_dbmail'.
The stored procedure sp_send_dbmail is located in the system database and not in the Insurance database maybe this is the fault? How can I change that?
sql-server query database-mail
edited 9 secs ago
Michael Green
14.7k83060
asked 13 mins ago
jimmyjimmy
1
New contributor
jimmy is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
0
I have a database "Insurance" with a table "Customer"
CustomerID
FirstName
LastName
Birthday
Email
I created a query to see the birthdays today
use Insurance
go
select FirstName,LastName,Email, [Birthday]
from Customer
WHERE DAY([Birthday]) = DAY(GETDATE())
AND MONTH([Birthday]) = MONTH(GETDATE())
This works fine.
Next I created the Database Mail account, and changed the code to:
use Insurance
go
DECLARE
@out_desc VARCHAR(1000),
@out_mesg VARCHAR(10)
DECLARE @name VARCHAR(20),
@birthdate datetime,
@email NVARCHAR(50)
DECLARE @body NVARCHAR(1000)
DECLARE C1 CURSOR READ_ONLY
FOR
SELECT [FirstName], [Birthday], [Email]
FROM Customer
OPEN C1
FETCH NEXT FROM C1 INTO
@name, @birthdate, @email
WHILE @@FETCH_STATUS = 0
BEGIN
IF DATEPART(DAY,@birthdate) = DATEPART(DAY,GETDATE())
AND DATEPART(MONTH,@birthdate) = DATEPART(MONTH,GETDATE())
BEGIN
SET @body = '<b>Happy Birthday ' + @name +
'</b><br />Many happy returns of the day'
+ '<br /><br />Customer Relationship Department'
EXEC sp_send_dbmail
'sender@gmail.com',
'password',
@email,
'Birthday Wishes',
@body,
'htmlbody',
@output_mesg = @out_mesg output,
@output_desc = @out_desc output
PRINT @out_mesg
PRINT @out_desc
END
FETCH NEXT FROM C1 INTO
@name, @birthdate, @email
END
CLOSE C1
DEALLOCATE C1
I receive a error:
Msg 2812, Level 16, State 62, Line 57
Could not find stored procedure 'sp_send_dbmail'.
The stored procedure sp_send_dbmail is located in the system database and not in the Insurance database maybe this is the fault? How can I change that?
sql-server query database-mail
edited 9 secs ago
Michael Green
14.7k83060
asked 13 mins ago
jimmyjimmy
1
New contributor
jimmy is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
0
0
0
I have a database "Insurance" with a table "Customer"
CustomerID
FirstName
LastName
Birthday
Email
I created a query to see the birthdays today
use Insurance
go
select FirstName,LastName,Email, [Birthday]
from Customer
WHERE DAY([Birthday]) = DAY(GETDATE())
AND MONTH([Birthday]) = MONTH(GETDATE())
This works fine.
Next I created the Database Mail account, and changed the code to:
use Insurance
go
DECLARE
@out_desc VARCHAR(1000),
@out_mesg VARCHAR(10)
DECLARE @name VARCHAR(20),
@birthdate datetime,
@email NVARCHAR(50)
DECLARE @body NVARCHAR(1000)
DECLARE C1 CURSOR READ_ONLY
FOR
SELECT [FirstName], [Birthday], [Email]
FROM Customer
OPEN C1
FETCH NEXT FROM C1 INTO
@name, @birthdate, @email
WHILE @@FETCH_STATUS = 0
BEGIN
IF DATEPART(DAY,@birthdate) = DATEPART(DAY,GETDATE())
AND DATEPART(MONTH,@birthdate) = DATEPART(MONTH,GETDATE())
BEGIN
SET @body = '<b>Happy Birthday ' + @name +
'</b><br />Many happy returns of the day'
+ '<br /><br />Customer Relationship Department'
EXEC sp_send_dbmail
'sender@gmail.com',
'password',
@email,
'Birthday Wishes',
@body,
'htmlbody',
@output_mesg = @out_mesg output,
@output_desc = @out_desc output
PRINT @out_mesg
PRINT @out_desc
END
FETCH NEXT FROM C1 INTO
@name, @birthdate, @email
END
CLOSE C1
DEALLOCATE C1
I receive a error:
Msg 2812, Level 16, State 62, Line 57
Could not find stored procedure 'sp_send_dbmail'.
The stored procedure sp_send_dbmail is located in the system database and not in the Insurance database maybe this is the fault? How can I change that?
sql-server query database-mail
edited 9 secs ago
Michael Green
14.7k83060
asked 13 mins ago
jimmyjimmy
1
New contributor
jimmy is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
I have a database "Insurance" with a table "Customer"
CustomerID
FirstName
LastName
Birthday
Email
I created a query to see the birthdays today
use Insurance
go
select FirstName,LastName,Email, [Birthday]
from Customer
WHERE DAY([Birthday]) = DAY(GETDATE())
AND MONTH([Birthday]) = MONTH(GETDATE())
This works fine.
Next I created the Database Mail account, and changed the code to:
use Insurance
go
DECLARE
@out_desc VARCHAR(1000),
@out_mesg VARCHAR(10)
DECLARE @name VARCHAR(20),
@birthdate datetime,
@email NVARCHAR(50)
DECLARE @body NVARCHAR(1000)
DECLARE C1 CURSOR READ_ONLY
FOR
SELECT [FirstName], [Birthday], [Email]
FROM Customer
OPEN C1
FETCH NEXT FROM C1 INTO
@name, @birthdate, @email
WHILE @@FETCH_STATUS = 0
BEGIN
IF DATEPART(DAY,@birthdate) = DATEPART(DAY,GETDATE())
AND DATEPART(MONTH,@birthdate) = DATEPART(MONTH,GETDATE())
BEGIN
SET @body = '<b>Happy Birthday ' + @name +
'</b><br />Many happy returns of the day'
+ '<br /><br />Customer Relationship Department'
EXEC sp_send_dbmail
'sender@gmail.com',
'password',
@email,
'Birthday Wishes',
@body,
'htmlbody',
@output_mesg = @out_mesg output,
@output_desc = @out_desc output
PRINT @out_mesg
PRINT @out_desc
END
FETCH NEXT FROM C1 INTO
@name, @birthdate, @email
END
CLOSE C1
DEALLOCATE C1
I receive a error:
Msg 2812, Level 16, State 62, Line 57
Could not find stored procedure 'sp_send_dbmail'.
The stored procedure sp_send_dbmail is located in the system database and not in the Insurance database maybe this is the fault? How can I change that?
sql-server query database-mail
sql-server query database-mail
edited 9 secs ago
Michael Green
14.7k83060
asked 13 mins ago
jimmyjimmy
1
New contributor
jimmy is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
edited 9 secs ago
Michael Green
14.7k83060
asked 13 mins ago
jimmyjimmy
1
New contributor
jimmy is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
edited 9 secs ago
Michael Green
14.7k83060
edited 9 secs ago
Michael Green
14.7k83060
edited 9 secs ago
Michael Green
14.7k83060
14.7k83060
asked 13 mins ago
jimmyjimmy
1
New contributor
jimmy is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 13 mins ago
jimmyjimmy
1
asked 13 mins ago
jimmyjimmy
1
1
New contributor
jimmy is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
New contributor
jimmy is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
jimmy is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
add a comment |
0
active
oldest
votes
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
jimmy is a new contributor. Be nice, and check out our Code of Conduct.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f231116%2fsql-send-birthday-email-from-query%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
jimmy is a new contributor. Be nice, and check out our Code of Conduct.
draft saved
draft discarded
jimmy is a new contributor. Be nice, and check out our Code of Conduct.
jimmy is a new contributor. Be nice, and check out our Code of Conduct.
jimmy is a new contributor. Be nice, and check out our Code of Conduct.
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f231116%2fsql-send-birthday-email-from-query%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
