I want to include the package name that is being executed in the message body of the email, How can i do this ?
I have tried to set an output parameter with Variable Name "System:ackageName", but i get the message :
Variable "System:ackageName" cannot be used as an out parameter or return value in a parameter or return value in parameter ....
I have developed a B2B Application which is successfully running, now the problem is that my client says that he need a process which sends report of total orders on daily bases.
I am using SQL Server and think that SQL Mail is a good idea, I can find many articles about which needs Outlook for that which is much difficult on the web server. Remember I need to send email only not to receive.
The following SP adds a record to a table. I want to then send an email from within this SP to notify someone that their login is blocked. I can get their email address easily with a simple select but how does the xp_sendmail procedure actually fit in ?? I've tried adding it in just before the raiserror part but it doesn't work ? The raiserror part is used to trigger an alert to notify me but i need to also notify the client. Can anyone see what i am doing wrong please. Many thanks.
I want to send an email to all the addresses that are contained in a table. I have tried using xp_sendmail, but I just get an error saying that xp_sendmail cannot be found. Any ideas???
Ok I am going to try and explain this best as possible. I have a report that gets everyones name who has not filled in attendance for a class. what they want is when the report is ran an email gets sent out. so I have written the sql to create a temp table that will get the distinct email.... so lets say the table consists of
My code below works fine when run from my pc (changed all the values forobvious reasons). The code is placed inside a DTS task via VBS scripting.But when I try to run directly from the server where sqlserver is installed,the script fails.I have SMTP running, but there is no outlook installed.Can someone please advise what I am missing.ThanksBobSet objEmail = CreateObject("CDO.Message")objEmail.From = "send@test.com"objEmail.To = "receive@test.com"objEmail.Subject = "TEST SUBJECT"objEmail.AddAttachment "\server est.csv"objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "SERVER_NAME"objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "username"objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "userpwd"objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25objEmail.Configuration.Fields.UpdateobjEmail.Sendset objEmail = nothing
If SQL is restarted for any reason, I need SQL server to send emails to others letting them know that the server is has restarted once it comes back up.
Is there an Error # that I can build an Alert on? Ideas???
Does anyone know how to send emails from within a trigger without the use of a mail server. I know that xp_sendmail can be used if a mail server, such as MS Exchange Server is available and SQL Server is set up as an e-mail client. However is there a way to send mail to the outside world without the use of a mail server? (Perhaps there is a third party product or extended stored procedure that can give me this ability?)
I would like to send email from Sqlserver 7.0, and I don't have a MS exchange server(I have a smtp server). Do I need to have MS exchange server and if not, how can I get around this? Can Outlook express be of any use?
I'm trying to send email through Integration Services (based on when a package has been executed successfully) however I get this error message:
SSIS package "CopyEmailTable.dtsx" starting. Error: 0xC002F304 at Send Mail Task, Send Mail Task: An error occurred with the following error message: "Failure sending mail.". Task failed: Send Mail Task SSIS package "CopyEmailTable.dtsx" finished: Success.
Sending email via Mgmt Studio works fine, but not through Integ Services.
Hi. I found this article on 4GuysFromRolla (http://www.4guysfromrolla.com/webtech/tips/t051706-1.shtml) about sending an email when the database is updated. Well, I need to take this example a bit further. We are allowing people to enter their email address on our site to receive email updates on their case. I have all of that working in the database. But I don't know how to modify their example to loop through a listing of email addresses who want to receive an email when the case has been updated. Does anybody know how I can accomplish this? Thanks for all your help!
Follow-up to my original post.Is it possible for the "objEmail.To" to lookup the values from a sqlservertable?At the moment, I type the email address separated by a semi-colon.TIA~Set objEmail = CreateObject("CDO.Message")objEmail.From = "send@test.com"objEmail.To = "receive@test.com"objEmail.Subject = "TEST SUBJECT"objEmail.AddAttachment "\server est.csv"objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "SERVER_NAME"objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "username"objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "userpwd"objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25objEmail.Configuration.Fields.UpdateobjEmail.Sendset objEmail = nothing
HiTrying to send email from sql server seems a very hard task, can iteven be done without using exchange? all examples I can find relies onexchange but I would rather send it to any SMTP server since we do notuse exchange.rgdsMatt
I have a website that I want to put a contact form on so I can allow users to send emails from it. I have SQL Server as my db but my hosting co. won't allow me to use the Database Mail procedure to send emails (msdb.dbo.sp_send_dbmail I think it's called). I will have to write my own procedure to do it instead. Can anyone point me in the direction of some good resources where I might get some info on how to do this?
I have done some fairly basic things with SSIS before, but I have now got a requirement where I need to send an email to a particular email address if a table (NewSubjects) has any data in it, can anyone give me any pointers on how I can achieve this with SSIS.
Public Class ScriptMain Public Sub Main() Dim msg As MailMessage Dim smtp As SmtpClient
Dim dt As Data.tbl_clients Dim ad As New Data.OleDb.OleDbDataAdapter dt = New System.Data.tbl_clients ad.Fill(dt, Dts.Variables("User::AllFilesFound").Value)
Dim data As String
For Each row As Data.DataRow In dt.Rows For Each column As Data.DataColumn In dt.Columns data = data & (column.StaffNo & " : " & row(column.Ordinal).ToString()) & "<br>" Next Next
Dim sendNotify As String sendNotify = "<pre>Cards to be printed " & Today() & " : <br><br>" & data.ToString() & ""
msg = New MailMessage( _ "my email", _ "my email again", _ "Subject: Cards to be printed", sendNotify) smtp = New SmtpClient("smtp client") smtp.Credentials = CredentialCache.DefaultNetworkCredentials msg.IsBodyHtml = True smtp.Send(msg) Dts.TaskResult = Dts.Results.Success End Sub End Class
I got the following error: The query failed to parse. An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Add a name or single space as the alias name.
I would like to send an email notification after data has been imported into the table. Any response will be highly appreciated.
I just wondered that now SQL Mail is classed as a legacy solution for email, does anyone know how to set up Notification Services to send email once a job has completed e.g. Backup or reindex?
I have tried to find a tutorial but with no joy so far!
I'm not a newbie, but I've not been able to find out how to do thisparticular task, and BOL isn't being too cooperative.My application sits on a SQL Server backend (SQL Server 2000).The application is a Document Distribution system. Some subscribershave elected to be notified by e-mail that a new revision of a documentis available.I want to run a daily job that would search for new revisions and emailany electronic subscribers. Pseudo code example:DECLARE MailList CURSOR FORSELECTEMailAddressFROMCustomersINNER JOIN DocumentDistribution ONCustomers.CustomerID = DocumentDistribution.CustomerIDWHEREDocumentDistribution.DocumentID = @DocumentID ANDCustomers.EMailEnabled = 1OPEN MailListFETCH NEXT FROM MailListWHILE @@FETCH_STATUS = 0BEGIN/* Format and send the email to the customer here */FETCH NEXT FROM MailListENDCLOSE MailListDEALLOCATE MailListWhat do I need to do on the server to make this work?Many thanks in advanceEdward--The reading group's reading group:http://www.bookgroup.org.uk
I am new to reporting services. I am currently on the maintenance of few reports that are developed.
Any subscribed report I attempt to deliver via email is failing with a strange error.
"Failure sending mail: The report server has encountered a configuration error. See the report server log files for more information."
I am not able to understand where this log file is stored..where can I find this? Please let me know.. and if someone has faced this error before then what could be the reason? I am using SQL Server 2005 on Windows Server 2003 R2 standard edition.
I need some help in configure our SQL 2K & 2K5 servers to send notification on job failers. Have created operators on SQL 2k but when i try to do a test mail it fails saying mai session is not running. I go to properties of the Sql agent to turn on mail session but it is grayed out. Can someone please direct me to the steps I need to take when setting up sql server with email capability. Do I have to install office (outlook on the sql server box) in order for emailing to work?
Whats the best way to send a small query result using Send Email Task. I have log table not more then 50 rows with 3 columns I need to query this table and send email.
I tried using Exe SQL Task and in result set I tried to store in variable so that I can attached that variable as email source. I think query result I can't store in string variable like this. Any simple way?
We have a ASP.Net 2.0 web application and need to send out emails. We have an EmailQueue table in our database where email gets stored before it is send out. We are looking at the pros and cons of using Database Mail in SQL Server 2005 to send out our emails. Should we use SQL or our web app to send email???Tips, tricks, articles, experiences, opinions are greatly appreciated. Newbie
Hi there, I am wondering if the following is possible.
I would like to have a scheduled job that would check every day a date field of every record (on a specific table). If that date correspond to today then I would like to send an email with the information of that record.
Is this possible?
Btw if I mentionned Job Scheduling it's because I don't know if there is other ways available for such purpose, so if you think of an other way please let me know.
HI, Does any one know how I could send all the alerts, email notifications from the SQL Server to my Sprint PCS Phone. Is there any software for that. I know that it can be done by MSN mobile. Just checking whether it can be directly sent to my phone rather than going to hotmail which is then forwared to my cell phone. Let me know. Thanks, Cinday
I am getting this error when sending an excel file via email.
Failure sending mail: The Report Server has encountered a configuration error; more details in the log files
The scheduled job which emails an excel spreadsheet has been working and now it doesn't. I've done some research and this KB article is close to the error that I'm seeing however the win 32 error 87 is different. http://support.microsoft.com/?kbid=842423
Has anyone any idea's? I have not applied the hotfix for SSRS 2000 sp2.
Log file has this.
ReportingServicesService!library!7b4!09/28/2006-15:00:08:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException: The Report Server has encountered a configuration error; more details in the log files, AuthzInitializeContextFromSid: Win32 error: 87; Info: Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException: The Report Server has encountered a configuration error; more details in the log files ReportingServicesService!library!7b4!09/28/2006-15:00:08:: i INFO: Initializing EnableExecutionLogging to 'True' as specified in Server system properties. ReportingServicesService!emailextension!7b4!09/28/2006-15:00:08:: Error sending email. Microsoft.ReportingServices.Diagnostics.Utilities.RSException: The Report Server has encountered a configuration error; more details in the log files ---> Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException: The Report Server has encountered a configuration error; more details in the log files at Microsoft.ReportingServices.Authorization.Native.GetAuthzContextForUser(IntPtr userSid) at Microsoft.ReportingServices.Authorization.Native.IsAdmin(String userName) at Microsoft.ReportingServices.Authorization.WindowsAuthorization.IsAdmin(String userName, IntPtr userToken) at Microsoft.ReportingServices.Authorization.WindowsAuthorization.CheckAccess(String userName, IntPtr userToken, Byte[] secDesc, ReportOperation requiredOperation) at Microsoft.ReportingServices.Library.Security.CheckAccess(ItemType catItemType, Byte[] secDesc, ReportOperation rptOper) at Microsoft.ReportingServices.Library.RSService._GetReportParameterDefinitionFromCatalog(CatalogItemContext reportContext, String historyID, Boolean forRendering, Guid& reportID, Int32& executionOption, String& savedParametersXml, ReportSnapshot& compiledDefinition, ReportSnapshot& snapshotData, Guid& linkID, DateTime& historyDate) at Microsoft.ReportingServices.Library.RSService._GetReportParameters(String report, String historyID, Boolean forRendering, NameValueCollection values, DatasourceCredentialsCollection credentials) at Microsoft.ReportingServices.Library.RSService.RenderAsLiveOrSnapshot(CatalogItemContext reportContext, ClientRequest session, Warning[]& warnings, ParameterInfoCollection& effectiveParameters) at Microsoft.ReportingServices.Library.RSService.RenderFirst(CatalogItemContext reportContext, ClientRequest session, Warning[]& warnings, ParameterInfoCollection& effectiveParameters, String[]& secondaryStreamNames) at Microsoft.ReportingServices.Library.RenderFirstCancelableStep.Execute() at Microsoft.ReportingServices.Diagnostics.CancelablePhaseBase.ExecuteWrapper() --- End of inner exception stack trace --- at Microsoft.ReportingServices.Diagnostics.CancelablePhaseBase.ExecuteWrapper() at Microsoft.ReportingServices.Library.RenderFirstCancelableStep.RenderFirst(RSService rs, CatalogItemContext reportContext, ClientRequest session, JobTypeEnum type, Warning[]& warnings, ParameterInfoCollection& effectiveParameters, String[]& secondaryStreamNames) at Microsoft.ReportingServices.Library.ReportImpl.Render(String renderFormat, String deviceInfo) at Microsoft.ReportingServices.EmailDeliveryProvider.EmailProvider.ConstructMessageBody(IMessage message, Notification notification, SubscriptionData data) at Microsoft.ReportingServices.EmailDeliveryProvider.EmailProvider.CreateMessage(Notification notification) at Microsoft.ReportingServices.EmailDeliveryProvider.EmailProvider.Deliver(Notification notification)
We 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.
'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
'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"
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"
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
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
I have a DTS package that I'm moving over to SSIS. In place of migrating this package, I've choosen to recreate it. This package moves data from an Informix database to a SQL database.
In the old package the first task was to make a simple connection to the Informix database and if the task failed, it would send an email and stop the package.
The biggest reason for this is because the Unix server that I'm getting the Informix data from forces the user passwords to be reset ever 90 days. So in my old package, if I forgot to change the password and the connection started to fail it would send me an email.
In my new package, SSIS performs a validation before starting. There are a number of task that uses the connection to the Informix database. Under testing, if I put in a bad password, the validation process generates a validation error. I've tried catching this validation error using the Error Handling events but I've had no luck. I can send out errors PreValidation and PostValidation but OnError appears not to fire under a validation error.
Might anyone have any suggestions on a proper way to validate and be able to send out email notification if a connection fails? Any assistance would be appreciated.
I have everything set up with SQL Server 2000 and Outlook 2000 and theprocedure works fine but the message sits in my inbox. When it openthe e-mail it says this message has not been sent. I just click sendand the e-mail sends. Is there any reason I have to manually sent thee-mail after the xp_sendmail procedure works and should send thee-mail itself.ThanksJohn
Hi I have difficulty in sending message to different reciepients. I am using SSIS package to send in the parameter. If anybody could help to resolve will be great.
Thanks
declare @MailMsg varchar(1000) select @MailMsg = 'Hi there, Here are the Documents Nos. and details Status.
