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?
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??
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 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
Can 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 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.
I'm tryng to set up a CLR Trigger, when executing i get this error:
Msg 6522, Niveau 16, État 1, Procédure Trigger_EnvoiEmail, Ligne 1 Une erreur .NET Framework s'est produite au cours de l'exécution de la routine ou de la fonction d'agrégation définie par l'utilisateur 'Trigger_EnvoiEmail' : System.Security.SecurityException: Request for the permission of type 'System.Net.Mail.SmtpPermission, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed. System.Security.SecurityException: at System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet) at System.Security.CodeAccessPermission.Demand() at System.Net.Mail.SmtpClient.Initialize() at System.Net.Mail.SmtpClient..ctor(String host) at AffYEmailQueue.MailHelper.SendMailMessage(String from, String destinataire, String bcc, String cc, String subject, String Htmlbody, String Textbody, Int32 idAFFY) at AffYEmailQueue.Triggers.EnvoiEmail()
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
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.
Trying to find tsql for TRIGGER which will send me email, if someone rename database in test/dev environment. I found it for CREATE and DROP database trigger but could not find for RENAME database.
I have a report that gets a Customer_Number parameter and sends a mail with that customer's data.I want my users to be able to get this report's results by sending an email to a certain email address with a customer number in the topic.
I 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.
My question is it possible to send a report developed in reporting services via email as either a attachment or imbedded in the email, from an insert trigger placed on a table in sql. If so could you please help with the string that would be need to achieve this. The report will need to have one or two parameters passed to it from the table. The report is built and working through reporting services already. I was impressed with the subscription services that reporting services has in place and would like to utilise the reports further by auto emailing out when a new record is placed in the table. To be sent out either as an attachment or imbedded in the email it self. Thanks for any assistance in advance David
Is there a way to subscribe SSRS report using dynamic parameters for email and trigger the report from autosys job so that report should generate the exact time the job is triggered.Let me describe, my SSRS report should be triggered on success of one autosys job. i need to send email parameter and time of report schedule from this autosys job.
Total SQL newbie here. Hope I'm posting in the correct forum.
I have a Exchange 2003 server that archives all email to a large(55GB) SQL database. I have a situation where I have to export several emails from that database. However at this point I am clueless on how to accomplish this.
Having a problem with email subscriptions in SSRS 2005 running on Win2003 server. The email subscription is attempting to email an excell attachment of the report. The report uses a shared data source with stored credentials. Configured email in Reporting Services config manager to use SMTP server OurServer.OurDomain.com Even wrote a test vb.net program to send email using that SMTP server and was successful - yet Reporting Services subscription fails with following error:
Failure sending mail: The report server has encountered a configuration error. See the report server log files for more information.
Combed the Reporting Services error logs but found nothing pertaing to email - SMTP - Subscriptions or anything close - I'm stuck but we really need this as I am emailing 3 reports daily by the manual process of running the reports - exporting to excell - and emailing - UHG!!
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.
Under IIS SMTP I can set bounced email redirect etc. how to do that with dbmail, the idea is I can get the list of bounced emails somewhere so I can create a report.
Hi all;I need to generate an automatic mail alert based on the SQL server 2005 database to list of user (email) four times year based In this table there are dates according to that date it need to send email automatically four time each email after three month. I would be great full if you can find me solution for this issue. Thanks reem
Hi, My name is Vinh, I am a new bee in SQL Server 2005. I am using template script (see below) from SQL Server to create account but when I am right click in database mail for testing email and I got the message, could not connect to mail server.
Below I am trying to use smtp to connect but I know in my company we are using Exchange Mail Server. will that make a lot different?
Please help me,
Thank you very much,
sp_configure 'database mail xps', 1 GO reconfigure GO
------------------------------------------------------------- -- Database Mail Simple Configuration Template. -- -- This template creates a Database Mail profile, an SMTP account and -- associates the account to the profile. -- The template does not grant access to the new profile for -- any database principals. Use msdb.dbo.sysmail_add_principalprofile -- to grant access to the new profile for users who are not -- members of sysadmin. -------------------------------------------------------------
-- Profile name. Replace with the name for your profile SET @profile_name = 'TestProfile';
-- Account information. Replace with the information for your account.
SET @account_name = 'vdang'; SET @SMTP_servername = 'smtp.cgdnow.com'; SET @email_address = 'vdang@cdgnow.com'; SET @display_name = 'Vinh, Dang';
-- Verify the specified account and profile do not already exist. IF EXISTS (SELECT * FROM msdb.dbo.sysmail_profile WHERE name = @profile_name) BEGIN RAISERROR('The specified Database Mail profile (<profile_name,sysname,SampleProfile>) already exists.', 16, 1); GOTO done; END;
IF EXISTS (SELECT * FROM msdb.dbo.sysmail_account WHERE name = @account_name ) BEGIN RAISERROR('The specified Database Mail account (<account_name,sysname,SampleAccount>) already exists.', 16, 1) ; GOTO done; END;
-- Start a transaction before adding the account and the profile BEGIN TRANSACTION ;
IF @rv<>0 BEGIN RAISERROR('Failed to create the specified Database Mail account (<account_name,sysname,SampleAccount>).', 16, 1) ; GOTO done; END
-- Add the profile EXECUTE @rv=msdb.dbo.sysmail_add_profile_sp @profile_name = @profile_name ;
IF @rv<>0 BEGIN RAISERROR('Failed to create the specified Database Mail profile (<profile_name,sysname,SampleProfile>).', 16, 1); ROLLBACK TRANSACTION; GOTO done; END;
-- Associate the account with the profile. EXECUTE @rv=msdb.dbo.sysmail_add_profileaccount_sp @profile_name = @profile_name, @account_name = @account_name, @sequence_number = 1 ;
IF @rv<>0 BEGIN RAISERROR('Failed to associate the speficied profile with the specified account (<account_name,sysname,SampleAccount>).', 16, 1) ; ROLLBACK TRANSACTION; GOTO done; END;
We have a new SSRS 2005 instance running with a number of reports. We have setup a subscription on one of these to run at 8am every Monday. However this is not working. No email is being sent and it does not appear the subscription is being run, nothing is ever written in the Last Run column.
I have looked in the logs and the only thing I can see is the following warning
w3wp!extensionfactory!5!13/02/2006-14:42:03:: w WARN: The extension Report Server Email does not have a LocalizedNameAttribute. w3wp!extensionfactory!5!13/02/2006-14:42:03:: w WARN: The extension Report Server FileShare does not have a LocalizedNameAttribute.
We had this working succesfully with SSRS 2000. The new instance was a clean install.
I tried installing SQL 2005 Dev Edition on my Win 2000 Professional machine. That failed. But now I cannot send out emails or even open a simple Email task from DTS. This is something that worked perfectly fine before I attempted the SQL 2005 installation. It seems that the SQL 2005 installation somehow messed up the the MAPI profile. The exact error message that I get when trying to execute or open a Email task in DTS is: CAnnot load MAPI Interface layer for DTS. Please make sure that semmap90.dll is installed.
I have SSRS 2005 installed. User wants to change email address on subscription to send to external customer or others in our organization.
If I modify right "Manage individual subscriptions" to enabled for his security profile, the email address box is greyed-out.
I tried changing value <SendEmailToUserAlias>xxxx</ SendEmailToUserAlias> to FALSE in rsreportserver.config, with SSRS restart, but address box is greyed-out.
If I modify right "Manage all subscriptions" enabled for his security profile, the address box is not greyed-out, but can now see-edit everyone's subscriptions for that report.
How can I turn on address box is not greyed-out, but not open view of ALL subscriptions to my users?
I've got a report server that I recently inherited. I'll be the first to admit that I'm not an expert on SSRS, but I've figured out a lot in the past few weeks.
One issue I'm having (apparently it's always been there, but nobody ever bothered to fix it in the past) has to do with the report subscriptions and linked reports. We have an SSL cert inside IIS. I have SSRS configured to "require SSL connections" in the Report Server Virtual Directory tab.
So the user gets an email with a link to a report. But the link has an "http://" as opposed to "https://." When you click on the link, it throws your standard "page must be viewed on a secure channel." When I manually append an S to the url, it works great.
There has to be a way to automagically add the S to the url, but I can't seem to figure out how. Anyone ever run into this before? Google has not been good to me this morning either.
Hi, My name is Vinh, I am a new bee in SQL Server 2005. I am using template script (see below) from SQL Server to create account but when I am right click in database mail for testing email and I got the message, could not connect to mail server.
Below I am trying to use smtp to connect but I know in my company we are using Exchange Mail Server. will that make a lot different?
Please help me,
Thank you very much,
sp_configure 'database mail xps', 1 GO reconfigure GO
------------------------------------------------------------- -- Database Mail Simple Configuration Template. -- -- This template creates a Database Mail profile, an SMTP account and -- associates the account to the profile. -- The template does not grant access to the new profile for -- any database principals. Use msdb.dbo.sysmail_add_principalprofile -- to grant access to the new profile for users who are not -- members of sysadmin. -------------------------------------------------------------
-- Profile name. Replace with the name for your profile SET @profile_name = 'TestProfile';
-- Account information. Replace with the information for your account.
SET @account_name = 'vdang'; SET @SMTP_servername = 'smtp.cgdnow.com'; SET @email_address = 'vdang@cdgnow.com'; SET @display_name = 'Vinh, Dang';
-- Verify the specified account and profile do not already exist. IF EXISTS (SELECT * FROM msdb.dbo.sysmail_profile WHERE name = @profile_name) BEGIN RAISERROR('The specified Database Mail profile (<profile_name,sysname,SampleProfile>) already exists.', 16, 1); GOTO done; END;
IF EXISTS (SELECT * FROM msdb.dbo.sysmail_account WHERE name = @account_name ) BEGIN RAISERROR('The specified Database Mail account (<account_name,sysname,SampleAccount>) already exists.', 16, 1) ; GOTO done; END;
-- Start a transaction before adding the account and the profile BEGIN TRANSACTION ;
IF @rv<>0 BEGIN RAISERROR('Failed to create the specified Database Mail account (<account_name,sysname,SampleAccount>).', 16, 1) ; GOTO done; END
-- Add the profile EXECUTE @rv=msdb.dbo.sysmail_add_profile_sp @profile_name = @profile_name ;
IF @rv<>0 BEGIN RAISERROR('Failed to create the specified Database Mail profile (<profile_name,sysname,SampleProfile>).', 16, 1); ROLLBACK TRANSACTION; GOTO done; END;
-- Associate the account with the profile. EXECUTE @rv=msdb.dbo.sysmail_add_profileaccount_sp @profile_name = @profile_name, @account_name = @account_name, @sequence_number = 1 ;
IF @rv<>0 BEGIN RAISERROR('Failed to associate the speficied profile with the specified account (<account_name,sysname,SampleAccount>).', 16, 1) ; ROLLBACK TRANSACTION; GOTO done; END;
Kind of a newby sql question, but here goes:I have a sql 2005 database that I have a job that runs Sunday morning at 12:30 am. I set it up using SQL Svr Mgt Studio 2005. Under the Management directory I set up Database Mail to work with my local SMTP server. I can send a test email just fine.I then set myself up as an operator with my email address. (Under operators directory) I then went back to the properties of the job I set up, and under 'notification', chose e-mail operator (me) when Job Succeeds. The job runs, itt suceeds, but NO email!It flat out won't work. there are NO entries in teh( email) log for errors either. Anyone? TIA Dan OR is it better to script these jobs using xml? I don't have time to learn a new thing right now, just need it to work!
