hello,
need help with a simple trigger i have been working on. the trigger automatically sends me an email out when a record is inserted, how ever i can't seem to get the row column data into the email. The part i do not understand is that I get the row column data information in the email if I update the row.
This is for 2005 SQL
Any direction would be greatly appreaciated
OneIDesigned
What I am trying to do, Extract the data from SQL table and Insert in Email Body and email to user. I got good article on Internet, I follow all steps as it is, but still I am getting error.
Here is the link : [URL] ....
But I am getting Error:
Error: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.FormatException: Index (zero based) must be greater than or equal to zero and less than the size of the argument list.
at System.Text.StringBuilder.AppendFormat(IFormatProvider provider, String format, Object[] args)
at System.String.Format(IFormatProvider provider, String format, Object[] args)
at ST_7f59d09774914001b60a99a90809d5c5.csproj.ScriptMain.Main()
[Code] ....
Does anyone have a stored procedure for sending an email (using SQLMail) when a new record is added to a database table?
I have SQLMail all setup, I just need a good example of a stored procedure or trigger that will sending an email with the details for a new record entry into a table.
thanks!
I'm generating emails using sp_send_dbmail. Everything works perfectly except for one thing. In the body of the email I need to show a link to a web page (eg http://myweb/login.aspx).
The problem is that the received email shows the "link" as plain text, ie it is not a clickable link. I've tried adding char(13) (and char(10) and both) after the link text but that doesn't help.
Is there a way to make the link text a real link when received by Outlook? (All recipients will be using Outlook if that helps).
Thanks,
John
Hi, I need help please.
I want to send an HTML Email
I have an Htm file that i want to use as my email body on Send Mail Task
MessageSourceType: File Connection
MessageSource: Email.htm
It sends the email with the file in the body but not in HTML format
it outputs the HTML tags + data
eg: <span style="font-size:9.0pt;">Period: 2008.04</span>
I used the same file in a DTS package & that works fine.
Please Assist!
Regards
I currently have a web form posting back to a SQL table using a StoredProcedure. Part of this SP is that it pulls data from another tableand inserts a new row into the registration table.I want to have a trigger on the registration table that will fire whenthe row is inserted which will use the sp_send_cdosysmail sproc to sendan e-mail to the user.However, I want to be able to include the value of one of the fieldswithin the BODY of the message. I can't find a way to includeparameters/variables within the Body of a message usingsp_send_cdosysmail and it's driving me nuts.Here's what I have in a sproc (not a trigger) that executessp_send_cdosysmail...I currently pass a parameter for the "To" e-mailaddress and that works fine._________________________________________________EXEC sp_send_cdosysmail'fromemailaddress@testcompany.com',@stremail, <--This is the Parameter passed for the "To" e-mail addy-->'Test Subject','Test Body,Additional TextAdditional Text<--THIS IS WHERE I WANT TO PUT THE PARAMETER-->Additional TextAdditional Text'__________________________________________________ _Is there any way to do this?The sp_send_cdosysmail I used is the standard MS one..Here it is forreference:Thanks for any help offered!ElliotCREATE PROCEDURE [dbo].[sp_send_cdosysmail]@From varchar(100) ,@To varchar(100) ,@Subject varchar(100)=" ",@Body varchar(4000)/************************************************** *******************This stored procedure takes the parameters and sends an e-mail.All the mail configurations are hard-coded in the stored procedure.Comments are added to the stored procedure where necessary.References to the CDOSYS objects are at the following MSDN Web site:http://msdn.microsoft.com/library/d...s_messaging.asp************************************************** *********************/ASDeclare @iMsg intDeclare @hr intDeclare @source varchar(255)Declare @description varchar(500)Declare @output varchar(1000)--************* Create the CDO.Message Object ************************EXEC @hr = sp_OACreate 'CDO.Message', @iMsg OUT--***************Configuring the Message Object ******************-- This is to configure a remote SMTP server.--http://msdn.microsoft.com/library/d...n_sendusing.aspEXEC @hr = sp_OASetProperty @iMsg,'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','1'-- This is to configure the Server Name or IP address.-- Replace MailServerName by the name or IP of your SMTP Server.EXEC @hr = sp_OASetProperty @iMsg,'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value','SMTPServer'-- Save the configurations to the message object.EXEC @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', null-- Set the e-mail parameters.EXEC @hr = sp_OASetProperty @iMsg, 'To', @ToEXEC @hr = sp_OASetProperty @iMsg, 'From', @FromEXEC @hr = sp_OASetProperty @iMsg, 'Subject', @Subject-- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'.EXEC @hr = sp_OASetProperty @iMsg, 'TextBody', @BodyEXEC @hr = sp_OAMethod @iMsg, 'Send', NULL-- Sample error handling.IF @hr <>0select @hrBEGINEXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUTIF @hr = 0BEGINSELECT @output = ' Source: ' + @sourcePRINT @outputSELECT @output = ' Description: ' + @descriptionPRINT @outputENDELSEBEGINPRINT ' sp_OAGetErrorInfo failed.'RETURNENDEND-- Do some error handling after each step if you have to.-- Clean up the objects created.EXEC @hr = sp_OADestroy @iMsgGO
View 2 Replies View RelatedWe have a DTS package that sends smtp email from an ActiveX script task. The body of the emails are in template files that we read in and then replace values relating to the customer.
I am looking for suggestions of handling this process from an SSIS package. This is what our existing code does.
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
Function Main()
'********************************************************************************************
' Email results
'********************************************************************************************
Dim oRS, objMessage, fTemplate
Set fso = CreateObject("Scripting.FileSystemObject")
Set oRS =DTSGlobalVariables("ToBeNotified").Value
oRS.MoveFirst
Do While NOT oRS.EOF
Set objMessage = CreateObject("CDO.Message")
Set objMessage2 = CreateObject("CDO.Message")
objMessage.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
objMessage2.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
'Name or IP of Remote SMTP Server
objMessage.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = DTSGlobalVariables("RemoteSMTPServer").Value
objMessage2.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = DTSGlobalVariables("RemoteSMTPServer").Value
'Server port (typically 25)
objMessage.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
objMessage2.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
'Type of authentication, NONE, Basic (Base64 encoded), NTLM
objMessage.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
objMessage2.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
'Your UserID on the SMTP server
objMessage.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/sendusername") = "CustomerService"
objMessage2.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/sendusername") = "CustomerService"
'Your password on the SMTP server
objMessage.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "cslogin"
objMessage2.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "cspassword"
objMessage.Configuration.Fields.Update
objMessage2.Configuration.Fields.Update
objMessage.Sender = DTSGlobalVariables("EmailFrom").Value
objMessage2.Sender = DTSGlobalVariables("EmailFrom").Value
objMessage.From = DTSGlobalVariables("EmailFrom").Value
objMessage2.From = DTSGlobalVariables("EmailFrom").Value
objMessage.To = oRS.Fields("ContactEmail").value
' objMessage.To = "me@ourmail.com"
objMessage2.To = DTSGlobalVariables("EmailBCC").Value
' objMessage2.To = "me@ourmail.com"
If oRS.Fields("Rejected").value = 1 Then
'Rejection Message
objMessage.Subject = "electronic request notification"
If oRS.Fields("ProductType").value = "Fixed" Then
Set htmlTextStream = fso.OpenTextFile (DTSGlobalVariables("EmailTemplateRejectedFixed").Value)
Else
Set htmlTextStream = fso.OpenTextFile (DTSGlobalVariables("EmailTemplateRejectedVariable").Value)
End If
Else
'Successful results
objMessage.Subject = "electronic confirmation #" + CSTR(oRS.Fields("ConfirmationId").value)
objMessage2.Subject = "Customer request for hard copy of contract"
'Add Terms and Conditons document
termDocument = DTSGlobalVariables("TermDocumentsRepository").Value + CSTR(oRS.Fields("TermDocument").Value) + ".pdf"
If fso.FileExists(termDocument) Then
objMessage.AddAttachment termDocument
objMessage2.AddAttachment termDocument
End If
If oRS.Fields("ProductType").value = "Fixed" Then
Set htmlTextStream = fso.OpenTextFile (DTSGlobalVariables("EmailTemplateSuccessFixed").Value)
Else
Set htmlTextStream = fso.OpenTextFile (DTSGlobalVariables("EmailTemplateSuccessVariable").Value)
End If
End If
htmlText = htmlTextStream.ReadAll
htmlText = Replace( htmlText , "[OurLogo]" , DTSGlobalVariables("EmailLogo").Value)
htmlText = Replace( htmlText , "[CustomerName]" , oRS.Fields("ContactFirstName").value + " " + oRS.Fields("ContactLastName").value)
If oRS.Fields("Rejected").value = 0 Then
htmlText = Replace( htmlText , "[ConfirmNumber]" , oRS.Fields("ConfirmationId").value)
htmlText = Replace( htmlText , "[OrderDate]" , oRS.Fields("SalesDate").value)
htmlText = Replace( htmlText , "[ProductName]" , oRS.Fields("ProductType").value)
htmlText = Replace( htmlText , "[Months]" , oRS.Fields("TermMonths").value)
htmlText = Replace( htmlText , "[Price]" , oRS.Fields("SalesPrice").value)
htmlText = Replace( htmlText , "[Units]" , oRS.Fields("SalesPriceUnit").value)
htmlText = Replace( htmlText , "[StartDate]" , oRS.Fields("FlowStartDate").value)
htmlText = Replace( htmlText , "[AccountNumber]" , oRS.Fields("AccountNumber").value)
End If
objMessage.HTMLBody = htmlText
objMessage2.HTMLBody = htmlText
objMessage.Send
If oRS.Fields("SendHardCopy").value = True AND oRS.Fields("Rejected").value = 0 Then
' Attach Instructions for Hard Copy delivery
instructionDocument = DTSGlobalVariables("TermDocumentsRepository").Value + "Instructions.txt"
set file = fso.CreateTextFile(instructionDocument, true)
file.WriteLine("The customer has requested a hard copy of their contract to be sent to them.")
file.WriteLine("")
file.WriteLine("Instructions:")
file.WriteLine("1) Print the email")
file.WriteLine("2) Print the attached Terms & Conditions document")
file.WriteLine("3) Mail both items to the address below")
file.WriteLine("")
file.WriteLine("Customer Address:")
file.WriteLine(oRS.Fields("ContactFirstName").value + " " + oRS.Fields("ContactLastName").value)
file.WriteLine(oRS.Fields("BillingAddress1").value)
If oRS.Fields("BillingAddress2").value <> "" Then
file.WriteLine(oRS.Fields("BillingAddress2").value)
End If
file.WriteLine(oRS.Fields("BillingCity").value + ", " + oRS.Fields("BillingState").value + " " +oRS.Fields("BillingZip").value)
file.Close
set file=nothing
objMessage2.AddAttachment instructionDocument
fso.DeleteFile(instructionDocument)
objMessage2.Send
End If
set objMessage = nothing
set objMessage2 = nothing
oRS.MoveNext
Loop
Main = DTSTaskExecResult_Success
End Function
All suggestions are appreciated.
SK
I would like to send the contents of a file using xp_sendmail howeverI do not want the file contents to be an attachment.I have no problem sending the file as an attachement.Can anybody give me an xp_sendmail example of how to do this.The results of a query can easily appear in the body of the email butall myattempts to include the contents of a file in the body of the emailhave not worked.TIA
View 1 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 RelatedHello everyone,
Please i need your help...
I dont know how to place the field 'strTitle and datBorrowed " in my email? Not as an attachment though....Just write it in the mail as part of message body...
I use this SQL select statement to retrieve the strTitle and datBorrowed fields
strSQL += @"Select replace(strtitle,'[Original Book] - ',''), datBorrowed from tblBooks where convert(varchar(10),datBorrowed,101) = convert(varchar(10),(getdate() - 1),101) ORDER BY strTitle asc";
Now, I have the following code to write the email
static void SendTest()
{
int iEmailLanguage = 0;
MailMessage objMail;
objMail = new MailMessage();
objMail.From = MAIL_FROM;
objMail.To =MAIL_TO;
objMail.Subject = "Books Borrowed Yesterday";
objMail.Body = Dict.GetVal(iEmailLanguage, "EMAIL_MESSAGE");
objMail.Attachments.Add(new MailAttachment(strAttachment));
SmtpMail.SmtpServer = SSMTP_SERVER;
SmtpMail.Send(objMail);
}
And the body of the email is this......
Dict.AddVal(0, "EMAIL_MESSAGE", "*** This e-mail is automatically generated. ***" +
"*** PLEASE DO NOT REPLY TO THIS E-MAIL. ***" +
"" +
"Books Borrowed Yesterday are:" +
"" +
"" +
"Thank you," +
"" +
"eLibrarian" +
"" +
"================================================== ===============" +
"" +
"This e-mail is automatically generated by the Library system." +
"Please do not reply.");
i need to put or wedge the data i got from the SQL Statement into this or after the line "Books Borrowed Yesterday are:" +
So how should i do this?
I am trying to insert a carriage return in the select statement after the web link where I had highlighted code in bold. When I insert a record into the table, I receive the email with the message body is in single line.I need the result to look like this in the message body:
ALTER TRIGGER [dbo].[SendNotification]
ON [dbo].[TicketsHashtags]
FOR INSERT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
[code]....
Code to show the package StartDateTime and EndDateTime in body of the email in ssis send email task.
View 3 Replies View RelatedI need to send an email when my 'LastRunDate' field is 30 days old (i.e. It should send an email if the LastRunField = 7/21/2007).
I would need to include the matching fields in the database (i.e. MachineID, Description, etc.) then update that field to todays date.
I have a few values in the 'Frequency' field such as Daily, Monthly, Yearly. Daily would be 24 hrs, monthly 30 days, yearly 365 days from the lastrundate.
I am new to T-SQL & need a good p[lace to start.
Any sugesstions.
Thanks.
I have created a .bat file for my sqlcmd
my sqlcmd includes a .sql file that builds up my email with a HTML body.
Is it possible to insert a variable (@customername) into the HTML body?
I am already using @maillist and select statement to create my customer email address's.
I have to send updated Employee list from employee master table to a particular email ID on every last date of Month and when a new employee is added / deleted / edited. Also need to send this as an Excel file
I tried the following but "Invalid Object name dbo.tbl_EmployeeMaster" error coming while inserting a new employee.
USE [eXact]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[trg_Email]
[Code] ....
In oracle, I use the following script in trigger:
INSERT INTO TABLE1 (CITY, STATE) VALUES (:new.city,:new.state);
I am wondering how to insert new data in SQL server. Thanks!
I have a table called drugs with a cost, date, and drugname field. I have a history table that I want to insert into anytime the fields are changed in the drugs table. I want to capture the old data along with the new cost. is that possible in a update trigger? What I have below doesn't like when it comes to setting the new cost
create trigger Updt_drugs on drugs for update as
-- updates record with sql user and timestamp
--created 1-12-02 tim cronin
declare @newcost money
begin
set @newcost = select cost from inserted
insert into drugsold ([oldcost],[cost],[cdate],[drug])
select [cost],@newcost,[cdate],[drug]
from deleted dt
end
I created a link from Oracle to SQL server.
There is a trigger with insert statment in Oracle side.
I got error message as follow when trigger is invoked:
SQL> insert into test1 values ('zerbra','brazi');
insert into test1 values ('zerbra','brazi')
*
ERROR at line 1:
ORA-02047: cannot join the distributed transaction in progress
ORA-06512: at "GGWEB.TRIGGER2", line 4
ORA-04088: error during execution of trigger 'GGWEB.TRIGGER2'
my simple trigger is as follows:
create or replace
TRIGGER TESTRI
AFTER INSERT ON TEST1
FOR EACH ROW
BEGIN
insert into test1@sqlserver (city,state) values ('what','nine');
END;
i have 2 server named A and B
in A server have database server and B have database server
in A have database named A1 with table TA1 and in B have database named B1 with table TB1
i want if i insert data into database A1 table TA1 in server A, database B1 table TB1 in server B will insert the same data to
how can do that with trigger or other ways
thx u
I have a report that gets sends out through a subscription and sometimes the report has multiple pages and all those pages appear within one email.Is it possible to set the subscription in such a way that an email is sent per page when the subscription executes.
View 2 Replies View RelatedI need to set a trigger so that an email is generated when a row is inserted into the ABC_DB_OnlineFormsConfirm table. The message will include data not only from that table, but from a related table called ABC_DB_OnlineForms. The common field between the two is FormID. This is related to an online account application process where the user has the option of completing it in one session, or saving it and coming back at a later time. Once the user has completed the first screen and clicks continue, a record is written to ABC_DB_OnlineForms. At the very end, when he clicks “Save and Complete”, a row is written to the ABC_DB_OnlineForms table. I would like the body of the email to include three separate lines, as follows. The first two pieces of data come from the OnlineForms table and the third from OnlineFormsConfirm. The field names are userid, IRSBackup and ConfirmationCode.
UserID:
Subject to IRS Backup Withholding?:
Confirmation Code:
What I have below results in an email body that contains the confirmation code all by itself. If I switch @body=@MessageBody to @body=@UserID or @body=@Backup, it will report the correct single piece of data, but I have no idea how to get all three pieces at once as separate lines including the leading descriptor (UserID:, Confirmation Code, etc.).
CREATE TRIGGER SendEmail
ON [ABC].[dbo].[ABC_DB_OnlineFormsConfirm]
AFTER INSERT
AS
Begin
Declare @MessageBody varchar(100)
[code]...
I have trigger, but not execute somedata because insert few row in every second. I use java to insert data to SQL server 2005. Data inserted to a table but not executing trigger for some data.
For example 100 data every second inserted to a table.
If insert data one by one to a table trigger fires success.
Please Help me.
Hi
I am Importing data in datatable using SSIS package . I made trigger on that table on insert. The trigger on insert is not firing on that table
Please help
Thanks
CP
Hello
Can you setup a trigger to mail some one when a record is updated in SQL server 2005?
If you can, can anyone help me?
C
Hi,
I am looking for a SQL trigger that will send email notification to a mailing list whenever a folders contents have been changed.
Can anyone help on this as I have little to no experience with SQL??
Hi all,
I have a SQL Server Job that I would like to run when I receive an email saying "Data is now ready to be imported to SQL Server" ?
Is there a way to accomplish this.
- Vivek
Hi,
I have a trigger for a table which stores email information generated from an ACCESS form. The trigger should send an auto email response to users who submitted an email to request for their password (we have forgetful users!). There is something wrong with my trigger because the auto email is sent out with a blank body... I will appreciate any advice!
Thank you!
My trigger:
CREATE TRIGGER tr_SendPassword ON PIPEmail
FOR INSERT
AS
DECLARE @Password varchar(100)
DECLARE @EmailAddress varchar(100)
DECLARE @message varchar(100)
IF (select count(*) from inserted) = 1
BEGIN
IF exists (SELECT * FROM inserted
WHERE Subject = 'Forgot my password')
BEGIN
select @Password = 'We received an email request from you for your password. Your password for SAR Search is: ' + UserRole.Password,
@EmailAddress = [User].Email
from UserRole
join inserted on UserRole.WindowsUser = inserted.WindowsUser
join [User] on [User].WindowsUser = UserRole.WindowsUser
exec master.dbo.xp_sendmail @recipients=@EmailAddress,
@subject='SAR Search password request',
@message=@Password
END
END
I am new to developing as will be evident from this post. Your help will be greatly appreciated.
I am developing an intranet for our company using ASP.NET with a SQL backend. I am currently working on a suggestion box form.
I would like to have an email sent to specific persons when a new entry is made in the suggestion table. I have been able to configure the trigger and generate the email (This was easy). Formatting the email has proven more difficult to resolve.
The format I would like is somewhat as follows:
F_NAME L_NAME submitted the following suggestion:
IDEA
BENEFIT
APPROVE | DECLINE
The items in RED are columns in the table and the Blue Underlines are hyperlinks to change the Status column in the table.
How can I generate the email to contain the data from the inserted record and in the above format.
Being new at this I only now how to send a static email advising that the entry has been made.
Any help creating the dynamic email form for this trigger will be greatly appreciated.
Lastly, what books are most helpful for SQL, ASP.NET, and VBScript referencing and examples?
Thanks
I have a basic trigger that populated an audit table, but I want to add logic to that to send an email everytime the trigger is called,Is there a easy way to add code to my basic trigger to send an email to me everytime the data changes.Thanks
View 1 Replies View RelatedCan anyone assist me?
I am trying to create a trigger within our local database that will allow me submit an email to a local exchange server. Its an internal email that our company has created for responses by candidates when the original stored procedure meets a condition of TRUE.
Is SQL 7.0 capable of sending emails to a specified address through a Trigger? I was told it could but have yet to come across any documentation backing this up.
Would anyone know the generic syntax I can use to create such a trigger?
Any suggestions would be appreciated.
Thanks in advance,
Claude Johnson
cjohnson@staffmentor.net
Hello to a new forum. I am very impressed with the level of ability I have been seeing on this forum and all the helpful posts that are going on.
I am hoping someone can help me solve my problem. I know enough SQL to be very dangerous how to create, delete, insert, update etc... and how to build queries. Here is my dilemma I need to have a piece of scheduling software send out reminder emails based on a DATETIME field. I am running SQL Server 2005, and the database mail is already configured and can successfully send out email from the management studio interface.
Does anyone know of a tutorial or can point me in the right direction to accomplish this task. I am well versed in PHP, and am hoping I might be able to get the server to trigger a PHP script or something to this extent.
I have created a trigger like this below:
CREATE TRIGGER TriggerName ON Table_Name AFTER INSERT AS
DECLARE @SERVICE varchar(40)
DECLARE @STATUS varchar(3)
SET @SERVICE=(SELECT [SERVICE] FROM inserted)
SET @STATUS=(SELECT [STATUS] FROM inserted)
[Code] ....
When I added some a new record to Table_Name with Status='X' nothing happened. I have tried to exec only script below:
EXEC msdb.dbo.sp_send_dbmail @recipients=N'moj_adres', @body= 'test', @subject = 'Subject!!!!', @profile_name = 'profil'
and I received email notification.
I need to trigger an email to user when a work order due date is approaching 1 day prior to the due date. Also I need to trigger when a user adds, delete, or modify work order to check on work order due.
Not sure how to query it in SQL.
Due Date = NeedByDate
user = AssignedTo