Generate A Email Using Smtp Server (was Help With Query....)
Jan 21, 2005
Help with query: I currently cannot be alerted by SQL Mail so I would like to take the script that was generated by SQL server and using the store procedure sp_sqlsmtpmail to generate a email using smtp server to alert me. The store procedure does work. I would like to know if this is possible.
Thanks
Lystra
-- Script generated on 1/21/2005 10:04 AM
-- By: MAMSIsa
-- Server: (local)
IF (EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'Demo: Full tempdb'))
---- Delete the alert with the same name.
EXECUTE msdb.dbo.sp_delete_alert @name = N'Demo: Full tempdb'
BEGIN
EXECUTE msdb.dbo.sp_add_alert @name = N'Demo: Full tempdb', @message_id = 9002, @severity = 0, @enabled = 1, @delay_between_responses = 10, @include_event_description_in = 5, @database_name = N'tempdb', @category_name = N'[Uncategorized]'
Then
Exec sp_sqlsmtpmail
@vcTo = 'lwilliams@Huc.com',
@vcBody ='Check out problem Immediataly.',
@vcSubject ='DOCSITE01FDK - Full Tempdb Log'
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.
I want to setup a SMTP using gmail. How to configure it, it says,
In your email client software, under Outgoing mail, set the SMTP server to smtp.gmail.com. Set the your username is yourgooglemailname@gmail.com and make sure "Use username and password" is checked. Also check off "TLS" under "Use secure connection." I tried to do this in code:
Dim htmlMessage As MailMessage
Dim mySmtpClient As SmtpClient
htmlMessage = New MailMessage(From, SendTo, Subject, Body)
htmlMessage.IsBodyHtml = IsBodyHTML
mySmtpClient = New SmtpClient(Server)
Dim myCred As New System.Net.NetworkCredential("myemail@gmail.com", "mypassword") 'CredentialCache
Hi, I have RS 2000 running on windows 2003 server. I want user to subscribe to reports and receive reports via email. Initially SMTP was not installed for IIS 6.0, so I installed it and the right way (I had done it on different machine before and was succefull in that) but on this particular machines ...I don't see "Default SMTP Virtual Server" node when I open IIS manager. This is the third day I am working on this issue ..please help me out guys, prashant
I am running a SSIS package under a trusted account and I am running into a problem when I am executing a email task. The error is an operation timeout which was reported in the log. I am using windows authentication on the connection manager. Other email task configured the exact same way run correctly in the same run. Also when I execute task from BIDS the email is sent. Is there a bug in SSIS which would cause this behavior or some kind of exchange server configuration issue? Any help would be appreciated. Thanks, Greg
Hello dear i am using windows 2003 server where i have installed smtp server now i want to configure with remote stmp so i can send an email to any domain.
I am wanting to fire-off an email with the failed jobs anytime they are deposited into a table. My syntax fires off an email even when the table does not contain data, it just sends a blank email. this will only generate an email if teh table contains data?
if exists (Select from FailedJobs) exec msdb.dbo.sp_send_dbmail @profile_name = 'DatabaseMail' @recipients = 'asdfasdfsdf@aafas.com' @from_address = 'asdfasdfacasca@cc.com' @query = 'Select * from failedjobs' @subject = 'List Of Failed Jobs' @attach_query_result_as_file = 1;
I want to write a query to generate a report. I have a date column which will be holding all the business dates. No dates of Saturday and Sunday are allowed. What I am looking for is, I want to get the result of every 5th business day of each month. A month could start with any day. I just want only the 5th business day.
I wish to send e-mail from my SQL Server, however, I do NOT want to use Exchange or MS Mail. I am looking for a very simple SMTP component that I can call from SQL.
Here's a challenge that is killing me:I've got 2 web servers and a SQL Server and about 5,000 'users' whostay connected to the site all day. I have a page that is supposed tobe 'real-time', so to do this, I have a 1px frame that refreshes every15 seconds (so the other frame doesn't have to reload all the time--thetop only reloads when a new record or a changed record hits the db).The real time data can be filtered in about 8 different ways.Currently, I have each user querying a table that contains 1 record,including the max ID and the most current insert/update posting date.The browser contains a cookie with that date. When the browser receivesnotification that there is some new info on the server, it refreshesthe top page and reloads the data. This is happening for all users. So,I thought to eliminate the 5,000 users running the same (or closevariations) of the same query each time a records is inserted/updated,that I would generate an XML file with the current day's data.In a dev environment this works 'ok'. I'm doing this by running anActiveX job on the SQL Server that calls a stored proc (FOR XML) andwrites the content to a file. Then from the web servers, I'm queryingthis file for the new timestamp and then if newer than the cookie,grabbing the XML (using the httprequest in the ASP XMLDOM) and usingXSLT to transform the data instead of parameterizing the queries.Theoretically I love this solution. Problems happen in a LIVEenvironment where either the file is being written to or the job isn'table to run. When 2 records are trying to be written within the samesecond, the file isn't being written (or maybe that the http requestingthe XML is keeping the file locked?)....anyway...this is a HUGE problethat I can't seem to solve. Once we roll to .NET I think storing thedataset in cache and updating cache (still don't know how I'll triggerthat without each user checking the db)....Long winded, sorry...help?
Just trying out xp_smtp_sendmail for the first time.I get the error:Error: connecting to server smarthostOK, so I don't have the server parameter for SMTP server set upcorrectly.I don't even know what an SMTP Server is! Is this an e-mail providerthat provides SMTP functionality, or an application I need to install?
Hello,Does anyone know how I can set SQL Server up so that after it hascompleted a backup sucessfully it will send the completion report toan e-mail address via SMTP.Unfortunatly we cannot install MS OUTLOOK Express or similar.Is this possible?Many thanks in advance,Allan Martin
I'm trying to make my SEND MAIL TASK in SSIS dynamic, by retrieving SMTP Server name (amongst other email message information) from a table.
I've got an SP that is getting the information into package variable's, and when I don't try and have a dynamic server name (i.e. I use a specified SMTP server in my SMPT Server connection), then the email get sent off.
I'm trying to use an expression to build alter the send mail to use the server name I have in a user variable.
I've tried using the expression for the property: SmtpConnection set to: RTRIM( @[User::MailServer])
I also tried without the RTRIM - I only added it because the error messages seem to include a full 'string' length, and there doesn't seem to be a 'varchar' data type for SSIS variables.
so: a> Is SMTPConnection the property I need to be setting? None of the others seem correct b> If SMTPConnection IS the property I need, does the value give the SMTP Server Name, or just the Name of the connection from the Connection Manager's?
Thanks
Part-Answer to: b> The SMPTConnection property sets the name of the CONNECTION to be used. That Connection needs to set the name of the server. So n ow, the question becomes, how do I configure that from SQL (I know how to use package configurations from XML files).
I am trying to create standard subscriptions using local SMTP server. When i create a subscription with no parameters, the reports and delivered via email. If I try to subscibe to a report with parameters and setting the parameter values , the subscription fails. The Data Source credentials are stored., The same report without parameters is delivered.
Here is the log.
ReportingServicesService!library!10!10/17/2007-12:10:07:: i INFO: Initializing EnableExecutionLogging to 'True' as specified in Server system properties. ReportingServicesService!emailextension!10!10/17/2007-12:10:07:: Error sending email. Microsoft.ReportingServices.Diagnostics.Utilities.RSException: An error has occurred during report processing. ---> Microsoft.ReportingServices.ReportProcessing.ProcessingAbortedException: An error has occurred during report processing. ---> Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Query execution failed for data set 'DS1'. ---> System.InvalidOperationException: ExecuteReader: CommandText property has not been initialized at System.Data.SqlClient.SqlCommand.ValidateCommand(String method, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) at Microsoft.ReportingServices.DataExtensions.SqlCommandWrapperExtension.ExecuteReader(CommandBehavior behavior) at Microsoft.ReportingServices.ReportProcessing.ReportProcessing.RuntimeDataSetNode.RunDataSetQuery()
Hi, When i try to send an email from SQL 2000 reporting services, i get error "The underlying connection was closed: Unable to connect to the remote server" . In a different environment the same settings is working. The only difference is that in first case, SMTP server and SQL 2000 reporting server are in different domain. But i have tested SMTP server connectivity from the reportserver using DOS command. It works good. Please help me
my package requires a send mail task and i must use a secure non-windows smtp server. can someone please tell me how to configure the smtp connection manager so i can do this? thanks.
I'm having trouble in configuring Yahoo's SMTP for Database Mail. Here is my current settings:
Email Address: xxx@yahoo.com Server Name: smtp.mail.yahoo.com Port Number: 465 This server requires a secure connection (SSL) - check Basic Authentication Username: xxx@yahoo.com Password: xxx
When I tried sending a test email, it didn't work. But when I changed my SMTP to use Gmail, it worked!
Hi all,At this moment I'm trying to get database mail working. According tosome people it should be easy. Well...that is not the case for me.I'm having the following error:The mail could not be sent to the recipients because of the mail serverfailure. (Sending Mail using Account 2 (2006-11-24T08:48:15). ExceptionMessage: Cannot send mails to mail server. (Command not implemented.The server response was: Command not Supported).)SQL Server 2005 is installed on a separate server and the SMTP Serveris Lotus Notes. connecting with port 25.Start and stopping the SQL Server and SQL Server agent i tried already.i tried sysmail_stop_sp and sysmail_start_sp.Sometimes when starting or stopping the Mail with the followingcommands :exec sysmail_stop_spGOexec sysmail_start_spGOI will get the following error:Msg 233, Level 20, State 0, Line 0A transport-level error has occurred when sending the request to theserver. (provider: Shared Memory Provider, error: 0 - No process is onthe other end of the pipe.)So any help would be appreciated.Hennie
I have an issue with SMTP server " send mail task ". we see the following error [Send Mail Task] Error: An error occurred with the following error message: "The SMTP server requires a secure connection or the client was not authenticated.In SMTP connection manager what we should give in SMTP SERVER, if we use gmail id's in send mail task editor.
Hello everybody,I was configuring a SqlDataSource control using SQL Authentication mode.I first added a database file (testdb.mdf) through Solution Explorer-Add New Items. Then through Database Explorer I created a table named "info"Then while configuring the SqlDataSource control I used the SQL Authentication mode and attached the "testdb.mdf" database file.Test Connection showed success. But when I hit the Ok button of the wizard it displayed the following error message:Failed to generate a user instance of SQL Server. Only an integrated connection can generate a user instance.While configuring the SqlDataSource control I clicked "New Connection". Under Data Source section I tried both Microsoft SQL Server and Microsoft SQL Server Database File. And in both the cases I attached a databese file(testdb.mdf). Plz enlighten me on this.Thanks and Regards,Sankar.
I need to write a sql that generate the hierarchy in an organization.
Below an example
emplid empname supervisor_id superv_name 1 subu null null 2 vid 1 sub 3 ram 4 satis 4 satis 2 vid
I need an output to this query as below and also one important the supervisor ie supervisor_id and name is null is the top level,every employee also has to report to him and also to his all above supervisors.
Whoever joining new to org the hierarchy
empid empname supervisor_id superv_name 3 ram 4 satis 4 satis 2 vid 2 vid 1 subu 4 satis 1 subu 3 ram 1 subu 3 ram 2 vid 5 kumar 1 subu 5 kumar 4 satis 5 kumar 2 vid 1 subu null null
For example, i have a query result like this: UserID Key 1 AN 1 AC 1 MN 2 KK 2 VC I would like to generate result like this: UserID Keys 1 AN*AC*MN 2 KK*VC Is there any way to do it in sp?
I've registered with GoDaddy for my site. I've used Visual Web Dev 2005 with SQL Server Mgmt Express 2005 to create my database etc. Now with GoDaddy, I need to "recreate" the database on their server again, but I don't want to go throug the whole process again. I just want to use their query editor to generate the database online.GoDaddy mentions a "personal-sql" file that SQL Server 2000 generates, but I can't find anything like that with what I have. Thanks.
