Hi,
I am trying to write a stored procedure in Sql Server that should send an email containing a query result everyday at 2:00 PM. How can I do this?? Im trying to use xp_sendmail but this gives me the following error:
Could not find stored procedure 'xp_sendmail'.
Plz let me know if there is any easy way to handle this.
Thanks a lot
I thought I could just copy over some asp.net code like: System.Web.Mail.MailMessage mailMessage = new System.Web.Mail.MailMessage();
But VS2005 doesn't seem to want me touching System.Web.Mail.
Any ideas?
Thanks,
Allen
I have an store procedure and I want to build an email with this store procedure to email me How can I use the email command to incorporate into my sql SP?
View 4 Replies View RelatedEveryday morning I email the sql query/stored procedure output results to the users, I was wondering if I can use some kind of t-sql code or DTS packages so that I can automate this process where I want to send the sql/stored proc results in the body of the email.
View 7 Replies View RelatedHi Everybody,
I am trying to setup a stored procedure that runs through a Reminders table and sends an email to users based on DateSent field being smaller than todays date. I have already setup the stored procedure to send the email, just having trouble looping through the recordset.
Code Snippet
CREATE PROCEDURE [dbo].[hrDB_SendEmail]
AS
BEGIN
DECLARE @FirstName nvarchar(256),
@LastName nvarchar(256),
@To nvarchar(256),
@ToMgr nvarchar(256),
@Subject nvarchar(256),
@Msg nvarchar(256),
@DateToSend datetime,
@Sent nvarchar(256),
@ReminderID int,
@RowCount int,
@Today datetime,
@Result nvarchar(256)
-- Get the reminders to send
SELECT
@ReminderID = r.intReminderID,
@DateToSend = r.datDateToSend,
@FirstName = e.txtFirstName,
@LastName = e.txtLastName,
@To = e.txtEmail,
@Subject = t.txtReminderSubject,
@Sent = r.txtSent
FROM
(auto_reminders r INNER JOIN employee e ON r.intEmployeeID = e.intEmployeeID) INNER JOIN ref_reminders t ON r.intReminderType = t.intReminderTempID
WHERE
(((r.datDateToSend)<20/12/09) AND
((r.txtSent)='False'))
-- Send the Emails
WHILE(LEN(@To) > 0)
BEGIN
EXEC @Result = sp_send_cdosysmail @To, @ToMgr, @Subject, @Msg
END
-- Mark the records as sent
IF @Result = 'sp_OAGetErrorInfo'
BEGIN
SELECT @Sent = 'Error'
END
ELSE
BEGIN
SELECT @Sent = 'True'
END
UPDATE auto_reminders
SET
auto_reminders.txtSent = @Sent, auto_reminders.datDateSent = @Today
WHERE
intReminderID = @ReminderID
END
GO
From the code you can probably tell I am new to writing stored procedures, so I apologise for any obvious errors. My major problems are :-
how to loop through each record
how to get todays date
whether the struture of the procedure is correct
Also, if you think there is an easier way or a better method, please suggest it. I am open to any suggestions you may have,
Thanks in advance
Ben
Hello everyone,I need advice of how to accomplish the following:Loop though records in a table and send an email per record. Emailrecipient, message text and attachment file name - that's all changesrecord by record.Is it doable from a stored procedure (easily I mean, or am I better offwriting a VB app)? There are so many options of sending mail from SQLserver - CDONTS, SQL MAIL TASK, xp_sendmail. What's easier to implementand set up?Thanks a lot!!!(links and fragments of sample code would be greatlyappreciated)Larisa
View 2 Replies View Relatedhi need help how to send an email from database mail on row update
from stored PROCEDURE multi update
but i need to send a personal email evry employee get an email on row update
like send one after one email
i use FUNCTION i get on this forum to use split from multi update
how to loop for evry update send an single eamil to evry employee ID send one email
i update like this
Code Snippet
:
DECLARE @id nvarchar(1000)
set @id= '16703, 16704, 16757, 16924, 17041, 17077, 17084, 17103, 17129, 17134, 17186, 17190, 17203, 17205, 17289, 17294, 17295, 17296, 17309, 17316, 17317, 17322, 17325, 17337, 17338, 17339, 17348, 17349, 17350, 17357, 17360, 17361, 17362, 17366, 17367, 17370, 17372, 17373, 17374, 17377, 17380, 17382, 17383, 17385, 17386, 17391, 17392, 17393, 17394, 17395, 17396, 17397, 17398, 17400, 17401, 17402, 17407, 17408, 17409, 17410, 17411, 17412, 17413, 17414, 17415, 17417, 17418, 17419, 17420, 17422, 17423, 17424, 17425, 17426, 17427, 17428, 17430, 17431, 17432, 17442, 17443, 17444, 17447, 17448, 17449, 17450, 17451'
UPDATE s SET fld5 = 2
FROM Snha s
JOIN dbo.udf_SplitList(@id, ',') split
ON split.value = s.na
WHERE fld5 = 3
now
how to send an EMAIL for evry ROW update but "personal email" to the employee
Code Snippet
DECLARE @xml NVARCHAR(MAX)DECLARE @body NVARCHAR(MAX)
SET @xml =CAST(( SELECT
FirstName AS 'td','',
LastName AS 'td','' ,
SET @body = @body + @xml +'</table></body></html>'
EXEC msdb.dbo.sp_send_dbmail
@recipients =''
@copy_recipients='www@iec.com',
@body = @body,
@body_format ='HTML',
@subject ='test',
@profile_name ='bob'
END
ELSE
print 'no email today'
TNX
Hi guys,I hope somebody can help as I am trying to write a procedure in SQL that will be an email reminder sent to users on every 5 th or the month if they don't submit data before.
I am quite new to sql and procedures in particular so I was hoping somebody can help.
I need to:
1.Check the day of the month, if not 5th do nothing
2.Get the date of the prevoious month (get current date - 1 month,set day to 1)this is the funny bit.
I have a field DateEntered,but users only select the month and the year on the acctual page,but when submitted it gets written as a full date and defaults to the 1st of the month.
3.Get the list of hospitals that haven't got data for the previous month
4.Email the hospitals.
DECLARE @returnDay int;
DECLARE @DateEntered datetime;
SELECT @returnDay = DatePart(day,GetDate())
If @returnDay = 5 (syntax error near 5)
BEGIN
SELECT @DateEntered = GetDate() - 30
Print DatePart(month, @DateEntered)
I was hoping somebody could look at this and help me.
thanks
hi there, i m newbie in using the ms sql server.i wouldlike to learn how to write the store procudure.i hope u all can guide me on this.
i have one table with data
appid int (PK)
leaveappid int (FK)
firstapproval int
firstapproval email varchar,
2nd approval int
2nd approval email varchar
resutl firstapproval
result secondapproval.
i would like to do a automatic send mail function where: the first mail will send to the firstapproval email.if after 2 days, the firstapproval no response(the result firstapproval is empty) then the email will automatic send to the second approval. how can i write the SP for this case? i really have no idea to start since i duno how to write the SP syntax and dont have the concept too.
hope can hear from u all soon.
thanx
Hi,I'm not sure if this is possible as i've googled everywhere, but i have aselect query that returns a customer record with their associated salesorders. I would like to automate a process which sends an email reminder toeach customer in the database, that has outstanding orders. This emailreminder should have the results of the query regarding their account.The table structure are as follows.---------------------Customer_tbl---------------------CustomerIDAccountNoNameEmailAddress---------------------Order_tbl---------------------OrderIDCustomerIDReferenceAmountDateOutstanding_flgCan anyone help?Sen.
View 2 Replies View RelatedIs there a way to send binary file stored in SQL Server as email attachment without downloading it to the file system?
View 1 Replies View RelatedHi ,
I'm just new in this SQL 2005, and I do not reallly sure the subject is right or not but as example in this link below
http://msdn2.microsoft.com/en-us/library/ms190307.aspx
I want updated to few of person of any changes in database just by sending to their emails in every 2 hours as an example. I go through the example given but I do not know the step how to run stored procedures. The Information that I want to give to them is like as:
Date From : 23/02/2008
Date To: 24/02/2008
Number of user : 3
My draft table is like this
Sequence_No Submitted_Dt Name
-------------------- ------------------- ------------------------
1 2/21/2008 4:16:45 PM John
2 2/22/2008 4:16:45 PM Dean
3 2/23/2008 4:16:45 PM Rick
4 2/24/2008 4:16:45 PM Van
thanks to all of your corcern to help me
Regards;
I have created some stored procedures that I wish to share with another user in another database. How can I extract this code, other than cut and paste, since I have quite a few? Is it possible to duplicate database object "stored procedures"? How about a script that would recreate them in the target database?
Any help would be appreciated.
Thanks!
Does anyone know how i can send an email from within a stored procedure ? Can i simply use xp_sendmail ?
thanks
i want to know how can i send emails from SQL stored procedure? is it possible?
View 3 Replies View RelatedHi
I am new to C# . I have a stored procedure which takes 4 parameters
GetSearchComplaint( Comp_ID , strViolator, strSts, FromDate, ToDate), These parameters can be null.
And i have 5 textboxes from which i send the parameters.
I am validating the input like this :System.Nullable<int> Comp_ID;
if ((txtsrchCompID.Text).Trim() == "")
{Comp_ID = null;
}else if ((txtsrchCompID.Text).Trim() == "")
{
try
{int i = int.Parse(txtsrchCompID.Text);
Comp_ID =i;
catch
{mesage += "Complaint ID is not valid";
}
}
When i run this i get this error ---'Use of unassigned local variable 'Comp_ID'
I get the same error for FromDate(DateTime) and ToDate(DateTime) . but not for string variables strViolator and strSts.
How do i pass the null value to the stored procedure? pls help..
Hi there,
My requirement is to send more than one GUID to the stored procedure.
How can I do that? If you can give me an example that will be great.
Kind regards,
Ricky
I am tring to use a stored procedure to run a function POST some data through HTTP, but failed. Here are the codes I used. Would appreciate if anyone can help me about that. Thank you very much.
Database Stored Procedure:
CREATE PROCEDURE [dbo].[USP_HTTP_POST]
@URL NCHAR(255),
@CONTENT NCHAR(1024),
@Result bit OUTPUT
AS
BEGIN
/***** Call Function to POST *****/
set @Result = NULL
EXECUTE @Result = [dbo].[udfHttpPost]
@URL,
@Content
END
Database Function:
CREATE FUNCTION [dbo].[udfHttpPost]
(@Url [nchar](255),
@Content [nchar](1024))
RETURNS [bit] WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [SqlServices].[SqlServices.HttpServices].[HttpPost]
VC# Assembly Code
using System;
using System.Configuration;
using System.Collections.Generic;
using System.Text;
using System.Net;
using System.Security;
using System.Security.Permissions;
using System.IO;
using Microsoft.SqlServer.Server;
namespace SqlServices
{
public class HttpServices
{
[SqlFunction(DataAccess = DataAccessKind.None)]
public static bool HttpPost(string Url, string Content)
{
Url = Url.TrimEnd();
Content = Content.TrimEnd();
string rawOutput = "";
//Get Access Right to Web
WebPermission p = new WebPermission(NetworkAccess.Connect,Url);
p.Assert();
//Prepare the WebRequest
WebRequest Req = (WebRequest)WebRequest.Create(Url);
Req.Timeout = 60000;
Req.Method = "POST";
Req.ContentLength = Content.Length;
Req.ContentType = "application/x-www-form-urlencoded";
StreamWriter PostWriter = new StreamWriter(Req.GetRequestStream());
PostWriter.Write(Content);
PostWriter.Close();
WebResponse Resp = Req.GetResponse();
StreamReader sr = new StreamReader(Resp.GetResponseStream());
rawOutput = sr.ReadToEnd();
sr.Close();
return true;
}
}
}
System Exception:
A .NET Framework error occurred during execution of user defined routine or aggregate 'udfHttpPost':
System.Security.SecurityException: Request for the permission of type 'System.Security.Permissions.SecurityPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
System.Security.SecurityException:
at System.Security.CodeAccessSecurityEngine.CheckNReturnSO(PermissionToken permToken, CodeAccessPermission demand, StackCrawlMark& stackMark, Int32 unrestrictedOverride, Int32 create)
at System.Security.CodeAccessSecurityEngine.Assert(CodeAccessPermission cap, StackCrawlMark& stackMark)
at System.Security.CodeAccessPermission.Assert()
at SqlServices.HttpServices.HttpPost(String Url, String Content)
I have a situation that I need to accomodate as follows:
Customer account table as a bit value as to whether to send an email
For each customer with a true value set I need to run a query against their products table to check whether the reorder value is less than current stock.
if the recordcount for this query is > 0 then I need to send the customer an email using the address in the customer file.
This process needs to be run once per week.
Can anybody offer advice, samples as to resolve this problem
Thanks
can I send a mail by stored procedure?
View 5 Replies View RelatedHello,I have a sqlserver stored procedure that calls the stored procedure sp_send_cdosysmail_htm to send reminder emails to customers. I am experiencing problems when trying to concatenate the id being renewed in the subject field. I'm always getting the message "error 170: line 10: Incorrect syntax near '+'."Does anyone know what the error means or can point me to a resource on the web? Many thanksRitao CREATE PROCEDURE dbo.SendRenewalEmail @ID INT AS BEGIN exec dbo.sp_send_cdosysmail_htm @From = 'yosemite.sam@acme.com', @To = 'road.runner@acme.com', @Cc = null, @BCC = null, @Subject = 'Order ' + @ID + 'Renewal Reminder', @Body = 'Hello roadrunner....' ENDGO
View 4 Replies View Relatedhi there,
the subject line says it all. is there a way to send nicely formatted html email from within a stored procedure that returns a dataset?
thanks!
chris
Hi !
I have a problem with the unique identifier and don't know how to solve it.
I have a stored procedure, called from my ASP.NET page, which inserts a new record into a table. I need to get the Id of the row just inserted in order to use it as a parameter of another stored procedure which inserts a new row with this value and other values.
I tried with SCOPE_IDENTITY but i don't know how to ask for this value to the first stored procedure and stored it into an ASP variable.
Dim cmd As New SqlCommand
cmd.CommandText = "Insertar_Contacto"
cmd.CommandType = CommandType.StoredProcedure
cmd.Connection = connect
Thanks!!
Folks,Using NorthWind as Example: Parent Table derived from: Categories. I added a new Column E-Mail and Selecting rows where Category Id <=3. Here is my Data.
Category ID
Category Name
Category E-mail
1
Beverages
Beverages.com
2
Condiments
Condiments.com
3
Confections
Child Table derived from: Products. I am Selecting rows where Category Id <=3. Here is my Sample Data.
Category ID
Product Name
Quantity Per Unit
1
Chang
24 - 12 oz bottles
1
Côte de Blaye
12 - 75 cl bottles
1
Ipoh Coffee
16 - 500 g tins
1
Outback Lager
24 - 355 ml bottles
2
Aniseed Syrup
12 - 550 ml bottles
2
Chef Anton's Gumbo Mix
36 boxes
2
Louisiana Hot Spiced Okra
24 - 8 oz jars
2
Northwoods Cranberry Sauce
12 - 12 oz jars
3
Chocolade
10 pkgs.
3
Gumbär Gummibärchen
100 - 250 g bags
3
Maxilaku
24 - 50 g pkgs.
3
Scottish Longbreads
10 boxes x 8 pieces
3
Sir Rodney's Scones
24 pkgs. x 4 pieces
3
Tarte au sucre
48 piesI would like to read 1st Category Id, Category E-Mail from Categories Table (ie. Category Id = 1), find that in Products Table. If match, extract matching records for that Category from Both Tables (Categories.CategoryID, Products.ProductName, Products.QuantityPerUnit) and e-mail them based on E-Mail Address from Parent (Categories ) Table. If no E-Mail Address is listed, do not create output file. In this instance Category Id = 3.Basically I want to select 1st record from Parent Table (Here is Category) and search for all matching Products in Products Table. And Create an E-mail and sending just those matching records. Repeat the same process for remaining rows from Categories Table. I am expecting my E-Mail Output like this: For Category Id: 1
Category ID
Product Name
Quantity Per Unit
1
Chang
24 - 12 oz bottles
1
Côte de Blaye
12 - 75 cl bottles
1
Ipoh Coffee
16 - 500 g tins
1
Outback Lager
24 - 355 ml bottlesFor Category Id: 2
Category ID
Product Name
Quantity Per Unit
2
Aniseed Syrup
12 - 550 ml bottles
2
Chef Anton's Gumbo Mix
36 boxes
2
Louisiana Hot Spiced Okra
24 - 8 oz jars
2
Northwoods Cranberry Sauce
12 - 12 oz jarsI am not extracting the Data for any user Interface (ie. Grid View/Form View Etc). I will just create a Command Button in an ASP.NET 2.0 form to extract Data. My Tables are in SQL 2005. I was thinking to read the Category records in a Data Reader and within the While Loop, call a SP to retrieve the matching records from Products Table. If matching records found, call System SP_Mail to send the E-mail. The drawback with that for every category records (Within While Loop) I need to call my SP to get Products Data. Will be OVERKILL? Ideally I would like extract my records with one call to a SP. Is there any way I can run a while loop inside the SP and extract Child Data based on Parent Record? Any Help or sample URL, Tutorial Page will be appreciated. Thanks
Hi all, can i send an email through SQL? i don't want to use third party software. Also, i can't configure the customer's db server. It is possible to send an email without much configuration. If configuration needed, is it possible to configure through SQL script? thx
View 8 Replies View RelatedHi,
I need to select a table from my database and send the table as an email message to a person every 10am because the table changes every day. How to it?
Hi all,
actually i m working on a store procedure in which end of the procedure when task complete it's send email to specific person, but i m having a problem using this function.
i am using
EXEC master..xp_sendmail @subject = @cmd, @recipients = @recipients, @message = @@servername
it's work fine when the outlook is configure on the server, but is there any way to send email rather then configure outlook on the server just chk internet is working send email on behalf of server,
i have more then 2 servers and outlook is configure only one server and i don't want to configure outlook on other server due to work load on server.
Thanks and looking forward.
-MALIK
How can i send email through sqlserver on Micrsoft Exchange?
View 2 Replies View RelatedHow can I send an email from sql server.
I tried the following code which gives me no errors but doesn't send the email.
Code:
CREATE Procedure sp_SMTPMail@SenderName varchar(100),@SenderAddress varchar(100),@RecipientName varchar(100),@RecipientAddress varchar(100),@Subject varchar(200),@Body varchar(8000)ASSET nocount ondeclare @oMail int --Object referencedeclare @resultcode intEXEC @resultcode = sp_OACreate 'CDONTS.NewMail', @oMail OUTif @resultcode = 0BEGINEXEC @resultcode = sp_OASetProperty @oMail, 'From', @SenderAddressEXEC @resultcode = sp_OASetProperty @oMail, 'To', @RecipientAddressEXEC @resultcode = sp_OASetProperty @oMail, 'Subject', @SubjectEXEC @resultcode = sp_OASetProperty @oMail, 'Body', @BodyEXEC @resultcode = sp_OAMethod @oMail, 'Send', NULLEXEC sp_OADestroy @oMailENDSET nocount off GO
GO
Can anyone recommend a solution to send email from sql server?
This was a P A I N to get working. Maybe someone else here is sending email and could use it.
A UNICODE Send Mail using CDOSYS with ReadReceipt and Importance...
Sub SendMail (sFromAddress, sToAddress, sCcAddress, sBccAddress, sSubject, sBody, boolReadReceipt, intImportance )
'on error resume next
Const cdoDispositionNotificationTo = "urn:schemas:mailheader:disposition-notification-to"
Const cdoReturnReceiptTo = "urn:schemas:mailheader:return-receipt-to"
dim cdoMessage, cdoConfiguration
Set cdoConfiguration = Server.CreateObject ("CDO.Configuration")
' Outgoing SMTP server
With cdoConfiguration
.Fields("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "localhost"
.Fields("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
.Fields("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
.Fields("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = 60
.Fields.Update
End With
Set cdoMessage = CreateObject("CDO.Message")
With cdoMessage
' Update the CDOSYS Configuration
SET .Configuration = cdoConfiguration
.BodyPart.charset = "unicode-1-1-utf-8"
IF boolReadReceipt Then
.Fields(cdoDispositionNotificationTo)= sFromAddress
.Fields(cdoReturnReceiptTo)= sFromAddress
End If
' Set the Importance: 0:Low, 1:Normal, 2:High
.Fields("urn:schemas:httpmail:importance").Value= intImportance
.Fields.Update
.From= sFromAddress
.ReplyTo= sFromAddress
.To= sToAddress
.Cc= sCcAddress
.Bcc= sBccAddress
.Subject= sSubject
.Textbody= sBody
.Send
End With
Set cdoMessage = Nothing
Set cdoConfiguration = Nothing
End Sub
I'm trying to implement DTS and FTP. So that when a company sends or FTP infomation to our server, it runs a script and checks to see if the infomation is correct and sends and email saying successful or unsuccessful.
View 1 Replies View Related1. I'm trying to get the notifications working on the scheduled jobs.I want to email me when the job fails. I created an operator(dmalhotr) and given me as the email address.Here is the error what I'm getting when I'm pressing the test buttonto email myself in properties dmalhotr operator (hope that makessense).Error 22022: sqlserveragent error: the sqlserveragent mail session isnot running; check the mail profile and/or the sqlserveragent servicestartup account in the sqlserveragent properties dialog.I cancel out of it. Then I go to support services --> sql mail -->right-click properties (WE HAVE LOTUS NOT EXCHANGE HERE- dont knowwhether that makes a difference).It says profile name Outlook and I click test. It says Successfullystarted (and stopped) a MAPI session with this profile.I go back to the operator and then click test to send email and I getthe same error. I started and stopped sql server agent and still getthe same error.Not sure how this works, please send me an email as to what I'm doingwrongThanks:DHRUV
View 1 Replies View RelatedHow can I have my email download automatically instead of clicking send/receive all the time?
View 1 Replies View Related