is it possible to use send mail task to send results of a query via email? if it can be sent then what would the result look like? will it be like excel type format or fixed length? or comma delimited?
and obviously -- how can I accomlish it?
thanks
Hi,I'm not sure if this is possible as i've googled everywhere, but i have aselect query that returns a customer record with their associated salesorders. I would like to automate a process which sends an email reminder toeach customer in the database, that has outstanding orders. This emailreminder should have the results of the query regarding their account.The table structure are as follows.---------------------Customer_tbl---------------------CustomerIDAccountNoNameEmailAddress---------------------Order_tbl---------------------OrderIDCustomerIDReferenceAmountDateOutstanding_flgCan anyone help?Sen.
View 2 Replies View RelatedI’m running a data integrity procedure from an agent job that is a scheduled weekly maintenance task which emails the results of my query, and is working properly.
I would however like this to only receive the email it the query contains results, and not send it no records are found to prompt me to take action.
My code less the personal information
EXEC msdb.dbo.sp_send_dbmail
@profile_name
= '',
@recipients
= '',
@subject
= 'Database Integrity - Import Errors',
[Code] ....
possible to send Pivot query results as automated database email ?
View 3 Replies View Related"Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression"..It is about a Stored Procedure, which not only should output the number of opportunities with status "Pending" or "Accepted" but also the name of the opportunities with these statuses. Following points:
1. The SELECT that outputs the names of the opportunities may in some cases return more than one value. I created then the CURSOR CUR_TEST.
2. The code should return the names of Opportunities, namely after "The opportunities are:" and these must be sent to the recipients of an email. How can I do this, so the Names of the Opportunities and the rest of the email can be sent by email?. I mean, the concatenation with SET or SELECT doesn't work. I get as email only the output of the last row of the SELECT with cursor CUR_TEST.
3. I add an example how the output should be.
The following is the code:
BEGIN
declare
@V_USER VARCHAR(20),
@V_NAME VARCHAR(100),
@V_QUANTITY INT,
@V_EMAIL VARCHAR(60),
@V_BODY VARCHAR(MAX),
@V_QUANTITY_ACTIVE int,
[code]...
An example how the Output in the email body should be:
Dear MyName,
You have 47 Opportunities with status Pending.
The Opportunities are:
Name of Opportunity 1
Name of Opportunity 2
Name of Opportunity 3
Hi,
Does anyone have an example of how to send the results of a query using sql mail? I m using sql server 2000 and have never used sql mail before.
Hope someone can help
Thanks
Hello,
I want to email the results of the query. The query output can be email either as the HTML body of the email to be sent or as a excel attachement.
I am wondering as to what would be the best way to achive this.
a) I could have a email client module in C# or VB.NET 2.0, which would fetch the query resultset and store it in a dataset. I format it as HTML body and send the email using System.NET. For excel, can use XSLT
Please suggest if there is any other approach that would be more suitable.
Thanks.
SQL Server is new to me.
I am using SQL Server 7.0 Query analyzer. I need to send my query results to a flat file. My select statement is 'SELECT * from table_a'.
Can someone help?
I have posted this on previous forums, but the below code is a VB.net CLR stored proc that will accepts two parameters, a SELECT.... FOR XML query and a file path, and saves the resulting xml result into a file specified by the parameter. After compiling the VB code into a .dll than the T-SQL code to import the assembly, create the CLR proc and test it is included underneath. Hope some will find this useful
Code Snippet
'Requires the Trial or Release version of Visual Studio .NET 2005 Professional (or greater).
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Imports System.Xml
Partial Public Class outputxml
<Microsoft.SqlServer.Server.SqlProcedure()> _
Public Shared Sub outputxml(ByVal XmlData As SqlXml, ByVal Filename As SqlString)
'Create the variables to hold the values that are supplied by the parameters
'input by the stored procedure
Dim xmlDoc As New XmlDocument()
Dim output As SqlPipe = SqlContext.Pipe()
Try
'Load the result set into the XmlDoc Variable and then save the results in the
'path provided by the stored procedure. The values are provided by the
'input parameters of the stored procedure
xmlDoc.LoadXml(XmlData.Value)
xmlDoc.Save(Filename.Value)
Catch ex As Exception
'If an error occurs catch the message and pipe it back to SQL
output.Send(ex.Message.ToString)
End Try
End Sub
End Class
T-SQL CODE BEGINS HERE
/*Alter the database to set trustworthy on in order
to allow the ability to set the assembly to external
access*/
ALTER DATABASE AdventureWorks SET trustworthy ON
--Import the assembly into SQL
CREATE ASSEMBLY outputxml
from 'C:outputxml.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS
-- Create the proc from the imported dll
CREATE PROCEDURE output
@xmldata XML,
@filename nvarchar(1024)
AS
EXTERNAL NAME outputxml.[outputxml.outputxml]
.outputxml
-- Test managed stored procedure
DECLARE @output xml
SET @output = (SELECT ProductID, Name, ListPrice
FROM Production.Product Product
FOR XML AUTO, ROOT('Catalog'), TYPE)
EXEC dbo.outputxml @output, 'c:Output.xml'
GO
I have several tasks that I don't want to use conditional failure on. I have a ON ERROR send mail task right now that works. I want to have that send mail task include query results from a separate query. How do I do that?
Peter Cwik
I'm very new to SQL but I have figured out how to do my first query. Now I would like to automate it to send the query as an attachment to users automatically (scheduler/cron??). How would you go about this, I need step by step hints. I'm using SQL Query Analyzer ver 8.00.2039 to generate the query from CDR records. Is this even possible?
Thank you for any guidance.
What is the best way to send emails with query results as attachment?
There is a stored proc sp_send_dbmail but the formatting is not very good.
I have a set of results based on a query that displays a learner, their workplace assessor and both their email addresses.
I want to send a standard request email (using db_mail) to each learner's email address which specifies who their individual assessor is and their current employer. One per learner.
I then want to email each assessor and in this email detail their learners (and their employer details) that I have emailed the request too.
Could anyone walk me through how to do the following:
Schedule a query to run at a designated time
Embed the resulting table in a HTML email
Email the HTML formated results someone automatically
Can this be done with SQL Server only or is there a third party app?
I know its alot but I'm having a hard time finding resourses.
Is there a way to export query results to an excel fie and add that file as an attachment in the email? All this has to be done using SQL query and it needs to be automated. My coworker tried using Openrowset and BCP, but it is not working.
View 3 Replies View RelatedHi all, can i send an email through SQL? i don't want to use third party software. Also, i can't configure the customer's db server. It is possible to send an email without much configuration. If configuration needed, is it possible to configure through SQL script? thx
View 8 Replies View RelatedHi,
I need to select a table from my database and send the table as an email message to a person every 10am because the table changes every day. How to it?
Hi all,
actually i m working on a store procedure in which end of the procedure when task complete it's send email to specific person, but i m having a problem using this function.
i am using
EXEC master..xp_sendmail @subject = @cmd, @recipients = @recipients, @message = @@servername
it's work fine when the outlook is configure on the server, but is there any way to send email rather then configure outlook on the server just chk internet is working send email on behalf of server,
i have more then 2 servers and outlook is configure only one server and i don't want to configure outlook on other server due to work load on server.
Thanks and looking forward.
-MALIK
How can i send email through sqlserver on Micrsoft Exchange?
View 2 Replies View RelatedHow can I send an email from sql server.
I tried the following code which gives me no errors but doesn't send the email.
Code:
CREATE Procedure sp_SMTPMail@SenderName varchar(100),@SenderAddress varchar(100),@RecipientName varchar(100),@RecipientAddress varchar(100),@Subject varchar(200),@Body varchar(8000)ASSET nocount ondeclare @oMail int --Object referencedeclare @resultcode intEXEC @resultcode = sp_OACreate 'CDONTS.NewMail', @oMail OUTif @resultcode = 0BEGINEXEC @resultcode = sp_OASetProperty @oMail, 'From', @SenderAddressEXEC @resultcode = sp_OASetProperty @oMail, 'To', @RecipientAddressEXEC @resultcode = sp_OASetProperty @oMail, 'Subject', @SubjectEXEC @resultcode = sp_OASetProperty @oMail, 'Body', @BodyEXEC @resultcode = sp_OAMethod @oMail, 'Send', NULLEXEC sp_OADestroy @oMailENDSET nocount off GO
GO
Can anyone recommend a solution to send email from sql server?
This was a P A I N to get working. Maybe someone else here is sending email and could use it.
A UNICODE Send Mail using CDOSYS with ReadReceipt and Importance...
Sub SendMail (sFromAddress, sToAddress, sCcAddress, sBccAddress, sSubject, sBody, boolReadReceipt, intImportance )
'on error resume next
Const cdoDispositionNotificationTo = "urn:schemas:mailheader:disposition-notification-to"
Const cdoReturnReceiptTo = "urn:schemas:mailheader:return-receipt-to"
dim cdoMessage, cdoConfiguration
Set cdoConfiguration = Server.CreateObject ("CDO.Configuration")
' Outgoing SMTP server
With cdoConfiguration
.Fields("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "localhost"
.Fields("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
.Fields("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
.Fields("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = 60
.Fields.Update
End With
Set cdoMessage = CreateObject("CDO.Message")
With cdoMessage
' Update the CDOSYS Configuration
SET .Configuration = cdoConfiguration
.BodyPart.charset = "unicode-1-1-utf-8"
IF boolReadReceipt Then
.Fields(cdoDispositionNotificationTo)= sFromAddress
.Fields(cdoReturnReceiptTo)= sFromAddress
End If
' Set the Importance: 0:Low, 1:Normal, 2:High
.Fields("urn:schemas:httpmail:importance").Value= intImportance
.Fields.Update
.From= sFromAddress
.ReplyTo= sFromAddress
.To= sToAddress
.Cc= sCcAddress
.Bcc= sBccAddress
.Subject= sSubject
.Textbody= sBody
.Send
End With
Set cdoMessage = Nothing
Set cdoConfiguration = Nothing
End Sub
I'm trying to implement DTS and FTP. So that when a company sends or FTP infomation to our server, it runs a script and checks to see if the infomation is correct and sends and email saying successful or unsuccessful.
View 1 Replies View Related1. I'm trying to get the notifications working on the scheduled jobs.I want to email me when the job fails. I created an operator(dmalhotr) and given me as the email address.Here is the error what I'm getting when I'm pressing the test buttonto email myself in properties dmalhotr operator (hope that makessense).Error 22022: sqlserveragent error: the sqlserveragent mail session isnot running; check the mail profile and/or the sqlserveragent servicestartup account in the sqlserveragent properties dialog.I cancel out of it. Then I go to support services --> sql mail -->right-click properties (WE HAVE LOTUS NOT EXCHANGE HERE- dont knowwhether that makes a difference).It says profile name Outlook and I click test. It says Successfullystarted (and stopped) a MAPI session with this profile.I go back to the operator and then click test to send email and I getthe same error. I started and stopped sql server agent and still getthe same error.Not sure how this works, please send me an email as to what I'm doingwrongThanks:DHRUV
View 1 Replies View RelatedHow can I have my email download automatically instead of clicking send/receive all the time?
View 1 Replies View RelatedHi Everybody
can somebody please help in finding how to mail the sql server reports via email.
Thanks alot
Nee123
It seems that using the Send Mail Task, there is no option for me to format my email as HTML. I have all my HTML code in a string and need to format my email to send the string as an HTML email. What would be the best way to approach this?
View 6 Replies View RelatedI am generating the text file on run time using flat file destination. The text file is generated on the location C: SSIS or D:SSIS based on the location specified in the configuration file.
The text file contains the non matching rows during lookup transform task.
I want to send this dynamically generated text file using the send email task.
But while doing this, I receive an error during package validation:
Package validation error:
Error at send email task [ send email task ] : either the file C:SSISErroroutput.txt does not exists or you do not have permission to access the file.
Error at send email task ; There were error during task validation.
Please suggest as I need to generate the text file only in case of lookup failure.
Hi guys
Is there anyway to fire an email from within sp - of course base on conditon.
say I write:
declare @emailaddr varchar(4000)
set @emailaddr = 'whatever@me.com'
if not exists(
select a, b from C
group by a, b
having count(*)>1 )
begin ********** -- fire the email to @emailaddress
Is there an sp or a way to send email
Thanks
I thought I could just copy over some asp.net code like: System.Web.Mail.MailMessage mailMessage = new System.Web.Mail.MailMessage();
But VS2005 doesn't seem to want me touching System.Web.Mail.
Any ideas?
Thanks,
Allen
Does anyone know if this can be done? I've looked everywhere. This is how my info is appearing in an email...
These are the files I requested and this is how it's being displayed. Any suggestions? Requestor
Requested Shipped
Due
------------------------------------------------------------------------------
-----------------------------------------------------------------------
-----------------------------------------------------------------------
-----------------------------------
-----------------------------------------------------------------------
---- ------------------------------ ------------------------------
------------------------------
Approaching the Corporate Heart
klm@yahoo.com
02/07/2008 02/07/2008
02/06/2008
PASSION FOR CUSTOMERS 3 copies
Nina.Childress@ssa.gov
Hi all
I have heared that there is way to use SQL SMO (SQL Management Objects), or other third party tools running on a management server to send out an email or some kind of notifications if a database is locked.
Anyone knows how to do that in SQL Managment 2005.
Thanks
-Sarah
When a new record is inserted into the database.
I want this to trigger an email that will be sent to all users who requested one when a new record was added.
I can't figure the best way to set this up.
All help gratefully received.
Thanks
JB
Hi,
I am trying to write a stored procedure in Sql Server that should send an email containing a query result everyday at 2:00 PM. How can I do this?? Im trying to use xp_sendmail but this gives me the following error:
Could not find stored procedure 'xp_sendmail'.
Plz let me know if there is any easy way to handle this.
Thanks a lot