I have below code to send email in HTML table format with store procedure. from my
Database = "CreditControl"
Table = "Testing$"
and code as below
DECLARE @bodyMsg nvarchar(max)
DECLARE @subject nvarchar(max)
DECLARE @tableHTML nvarchar(max)
DECLARE @recipients nvarchar(max)
DECLARE @profile_name nvarchar(max)
[code]....
hi 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 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
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?)
Many thanks for any response.
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
View 2 Replies View RelatedHow can I input parameter for Store Procedure like In ('Value1','Value2')
Example :
Create procedure GetUsers
@Users nvarchar(200)
as
Select * from Users where UserID in (@Users)
A DB2 store procedure returns two data sets, when executed from SSMS, using linked server. Do we have any simple way to save the two data sets in two different tables ?
View 1 Replies View RelatedHi,
I need a help from you all on Sending Email on some specific event from SQL SERVER 2005. Any Idea how Do I proceed?
Any help will be appreciated.....
Thanks,
Rahul Jha
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.
View 1 Replies View RelatedI have subscription by email problem on the following configuration:
Windows XP prof.
SQL Server 2005 Enterprise
Configured Database Mail with remote SMTP server to send email successfully
Configured SSRS to use remote SMTP server
Edited the Rsreportserver.config file as following (by the instructions from MSDN)
<Configuration>
<RSEmailDPConfiguration>
<SMTPServer>smtp.broadband.rogers.com</SMTPServer>
<SMTPServerPort>25</SMTPServerPort>
<SMTPAccountName></SMTPAccountName>
<SMTPConnectionTimeout></SMTPConnectionTimeout>
<SMTPServerPickupDirectory></SMTPServerPickupDirectory>
<SMTPUseSSL></SMTPUseSSL>
<SendUsing>2</SendUsing>
<SMTPAuthenticate>0</SMTPAuthenticate>
<From>My email Address</From>
<EmbeddedRenderFormats>
<RenderingExtension>MHTML</RenderingExtension>
</EmbeddedRenderFormats>
<PrivilegedUserRenderFormats></PrivilegedUserRenderFormats>
<ExcludedRenderFormats>
<RenderingExtension>HTMLOWC</RenderingExtension>
<RenderingExtension>NULL</RenderingExtension>
<RenderingExtension>RGDI</RenderingExtension>
</ExcludedRenderFormats>
<SendEmailToUserAlias>True</SendEmailToUserAlias>
<DefaultHostName></DefaultHostName>
<PermittedHosts></PermittedHosts>
</RSEmailDPConfiguration>
</Configuration>
I scheduled my subscription and tested it. I didn't receive any mail from the setting email address, but got an error message in ReportServerService log file as following.
Error sending mail, CDO error -2147220978, will not resend
Error sending email. System.Runtime.InteropServices.COMException (0x8004020E): The server rejected the sender address. The server response was: 530 authentication required - for help go to http://help.yahoo.com/help/us/mail/pop/pop-11.html
I did a little search, but couldn't find any solution for it. Is anybody successfully to get the Reporting Services subscription by email function working through MS SQL Server Management Studio or if you have a solution for this issue, would you mind to share the experience with me. I will be very appreciated.
Is it possible to store an email in SQL server 2000? Do we need to define our own data type for that, if yes then how? or do we have to store it as an object, again how? I want to save email after sending it by my ASP .NET application and then retrieve it at a later stage.......Anyone please help?
View 2 Replies View RelatedI 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.
Thanks, any reply will be highly appreciated.
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.
CREATE PROCEDURE usp_log_BlockedLogin_ins
@LoginName varchar(50),
@IP_Address varchar(15),
@Failed_Pswd varchar(50)
AS
BEGIN
DECLARE @LoginID int
SET @LoginID = (SELECT fa_loginID FROM tbl_Login WHERE fs_LoginName = @LoginName)
INSERT into tbl_Login_Audit_Trail
(
fi_LoginID,
fs_IP_Address,
fd_DateTime,
fb_Blocked,
fs_Failed_LoginName,
fs_Failed_Password,
fb_FailedAttempt
)
VALUES
(
@LoginID,
@IP_Address,
current_timestamp,
1,
NULL,
@Failed_Pswd,
1
)
END
BEGIN
RAISERROR (50001,10,1) WITH LOG -- this is used to trigger an alert
END
GO
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???
Many Thanks
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
jacob.ostop@synergit.com
xxxx@yyyy.com
gagsag@shsna.com
what i need to do now is get those three address and add them to a string that would be in the format of
jacob.ostop@synergit.com ; xxxx@yyyy.com ; gagsag@shsna.com
so that i can put that in the TO field of my email.
my problem is that i dont know how to get them each 1 at a time and put in semi colons..
can someone please help me!
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
View 3 Replies View RelatedWhile writing store procedure in db most of Time i will Use common Table to write select queries for selecting more than seven table whether it reduce speed performance or it won't
;with cte
{
}
From my one app, a dynamic where clause will generate like below.
//where ordercity='london' and orderby='smith'
//where orderamount > 100 and shipcity='new york'
From server, I created a store procedure as below.
SELECT * FROM [Order] WHERE @whereSql
How to complete the store procedure so that I don't need to pass parameters one by one...
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???
Thank you,
tw
Hello,
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?
Thanks,
yi
Hi,
I have a table or two in a MS SQL DB. One table has a document name, when it was last uploaded and how often it should be looked at/reviewed.
e.g.
name.doc
01/09/2005
Weekly
1
fish.doc
01/08/2005
Daily
2
Each document is associated with a user (another table) so in the above example 1 and 2 refer to theprimary key of the 'admin' table to get a user.
I want to be able to automatically send an email to users notifying them to check their documents on whatever interval they have set.
I hope this make sense!
JJ
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.
Any help in this is greatly appreciated!
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!
View 1 Replies View Relatedi'm using
EXEC @hr = sp_OASetProperty @object, 'HTMLBody', 'some text'
for sending email from my sql sever.
how can i make my content dynamic. So instead of 'some text' i would like to have data (results) from database; eg.: select * from table1.
is this possible anyhow?
thank you in advance
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
View 1 Replies View RelatedThere are about 10 select statements in a store procedure.
All select statements are need.
Is it possible to output only the result of last select statement?
In one store procedure I do insert same data into two tables (They have the same structure): OrderA and OrderB
insert into OrderA select * from OrderTemp
insert into OrderB select * from OrderTemp
And then got an error for code below.
"Multi-part identifier "dbo.orderB.OrderCity" could not be bound
IF dbo.OrderB.OrderCity=''
BEGIN
update dbo.OrderB
set dbo.orderB.OrderCity='London'
END
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?
Hi,
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.
Thanks,
Gavin,
When I used the following code:
Imports System
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.Net.Mail
Imports System.Net
Imports System.Data
Imports System.IO
Imports Microsoft.SqlServer.Dts.Pipeline
Imports System.Text
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.
Many thanks,
Rupa
Hi All
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!
Thanks
Gopher
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
View 1 Replies View Related
Hi,
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.
Cheers
Abhi..