SQL 2012 :: Send Emails With Query Results As Attachment?
Jul 16, 2014What 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.
ADVERTISEMENT
SQL Server 2008 :: Send Emails To Individuals In Query Results And Extra?
Jul 15, 2015I 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.
SQL 2012 :: Reporting Services Would Not Send Subscription Emails
May 5, 2014I am running RS 2012 installation and everything worked until few days ago. Now the subscriptions would not even run. When I configure new ones, after the time passes, there is not error message, not even a sign it ran.
View 2 Replies View RelatedSQL 2012 :: Data Driven Subscriptions - Any Way To Send All Emails At Once?
Mar 18, 2015We have a rather large query that takes time to run between each data-driven instance. Is there a way to only send out the first email after the last subscription has finished processing? Any advanced input on data-driven subscriptions .
View 0 Replies View RelatedSQL 2012 :: Using Database Mail To Send Emails Based On Certain Actions
Jun 12, 2014I have been using database mail for quite a few years now. I started using it with SQL Server 2005 and then it worked fine. Then we went over to SQL 2012 and the problems started. At first I could not get any e-mails out and read a post by Microsoft that a patch needs to come out but not available yet. That was in 2012. Presumably the patch came out with windows updates and my DB Mail started working. Now, however, I have the problem that some e-mails do get sent but the majority failed.
I use this so that whenever a user captures certain data to update a person's status on the database then an automatic e-mail will be sent to that person to inform them of their status at this company.
SQL 2012 :: Possible To Send Pivot Query Results As Automated Database Email?
Nov 26, 2014possible to send Pivot query results as automated database email ?
View 3 Replies View RelatedSQL 2012 :: Send Binary File Stored In Server As Email Attachment?
Apr 26, 2014Is there a way to send binary file stored in SQL Server as email attachment without downloading it to the file system?
View 1 Replies View RelatedHow To Send Query Result As Attachment With EXCEL Format
Feb 17, 2012i am using sp_send_dbmail to send Emails.how to send query result as an attachment with EXCEL Format.
View 1 Replies View RelatedDB Engine :: Send Query Result As Excel Attachment With Database Mail?
Aug 12, 2015I have below table in the snapshot, My task is to send the same output as a attachment in Excel to email. i used the below procedure
EXEC [msdb]..sp_send_dbmail
@profile_name='TSSSendMail',
@recipients='mymailID@gmail.com.com',
@subject='DB Mail',
@body='HI Team',
@execute_query_database = 'DbName',
@query = 'EXEC J16ReimbursementFortnightly1TO15 1',
@attach_query_result_as_file=1,
@query_attachment_filename = 'SummaryReimbursement.xls'
But I am not getting Proper Output, is seems like CSV Format, but i want it proper tabular format in Excel.
How To Send A Dynamic Files Attachment By Send Mail Task?
Jan 19, 2007Hello All,
Hopefully someone out there will have an idea as this is driving me nuts.
I want to send a dynamic files in attachment files ny send mail task that file name has change follow datetime.
I try to use the expression but I can't use it.
please tell me for this problem.
Any suggestions appreciated,
Thanks.
Export Query Results To Excel File And Add As Attachment In Email
Jan 2, 2014Is 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 RelatedSQL Server 2008 :: 2 Emails Being Send From Job When Only 1 Should Send?
Apr 18, 2012I have a job that emails out shipment notifications at the end of the day to our customers. The problem I have is I don't understand why the same email is sending out twice within a minute of each other when the job is only scheduled to run once. If I take the code out of the step and run it in management studio it only emails once. I attached the code for one customer for reference. We are running SQL 2008 on a VM sending to an exchange 2010 server.
DECLARE @tableHTML NVARCHAR(MAX) ;
SET @tableHTML =N'<H1>XYZ Company ASN For ' + CONVERT(VARCHAR(10), GETDATE(), 101) + ' </H1>' +
N'<table border="1">' +
N'<tr><th>Vendor</th><th>Delivery Date</th>' +
N'<th>Purchase Order Number</th><th>Item Number</th><th>Item Description</th>' +
N'<th>Quantity Shipped</th><th>UOM</th><th>BOL Number</th>'
[code]....
Can't Send Attachment With CDO
Sep 24, 2001<CODE>
<FONT face="Verdana, Arial, Helvetica" color=midnightblue size=2>For some reason
this proc doesn't send attachment. Please advise.
<P></P>
<P><PRE id=code><FONT id=code face=courier size=2></pre>
<P></P><P>CREATE PROCEDURE [dbo].[sp_SendCDONTSMail]<BR>@Help [BIT] = 0,<BR>@From [VARCHAR](8000) = NULL,<BR>@To [VARCHAR](8000) = NULL,<BR>@Cc [VARCHAR](8000) = NULL,<BR>@Bcc [VARCHAR](8000) = NULL,<BR>@Subject [VARCHAR](8000) = NULL,<BR>@Body [VARCHAR](8000) = NULL,<BR> @Filename [VARCHAR](8000) = NULL,<BR> @Importance int = 0,<BR>@MailFormat [BIT] = 0,<BR>@BodyFormat [BIT] = 0<BR>AS<BR>DECLARE<BR>@Error [VARCHAR](150),<BR>@object [INT],<BR>@hr [INT]</P><P>IF @Help = 1<BR>BEGIN<BR>PRINT '<BR>Purpose:<BR>This porcedure will send an email using CDONTS.dll.<BR>Use as a replacement to xp_sendmail. It will allow you<BR>to send HTML emails from SQL<BR>'<BR>RETURN 1<BR>END</P><P>IF ((@From IS NULL OR @From = '') OR (@To IS NULL OR @To = '') OR (@Subject IS NULL OR @Subject = '') OR (@Body IS NULL OR @Body = '')) AND @Help = 0<BR>BEGIN<BR>SET @Error = 'sp_SendCDONTSMail requires parameters @From, @To, @Subject, and @Body.' + CHAR(13) + 'Please execute ''sp_SendCDONTSMail 1'' for syntax assistance.'<BR>RAISERROR(@Error, 16, 1)<BR>RETURN -1<BR>END</P><P>EXEC @hr = sp_OACreate 'CDONTS.NewMail', @object OUT<BR>EXEC @hr = sp_OASetProperty @object, 'From', @From<BR>EXEC @hr = sp_OASetProperty @object, 'To', @To<BR>EXEC @hr = sp_OASetProperty @object, 'CC', @Cc<BR>EXEC @hr = sp_OASetProperty @object, 'Bcc', @Bcc<BR>EXEC @hr = sp_OASetProperty @object, 'Subject', @Subject<BR>EXEC @hr = sp_OASetProperty @object, 'Body', @Body<BR>EXEC @hr = sp_OASetProperty @object, 'MailFormat', @MailFormat<BR>EXEC @hr = sp_OASetProperty @object, 'BodyFormat', @BodyFormat<BR>exec @hr = sp_OASetProperty @object, 'Importance', @Importance<BR>EXEC @hr = sp_OAMethod @object, 'AttachFile', @FileName <BR>EXEC @hr = sp_OAMethod @object, 'Send'<BR>EXEC @hr = sp_OADestroy @object</P></FONT></FONT>
</CODE>
Send Query Results Using SQl Mail
Sep 3, 2007Hi,
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
Send Query Results Via Email
Sep 27, 2007is 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
Send Mail Variable Attachment
Jul 16, 2007I am able to use a variable filename in my send mail task as the message source when I specify variable as the message source type. But I would rather use the variable in the attachment e.g. @[User::filename].
When I do that, I get the message that the file doen't exist. How is it possible to specify a variable as the attachment?
Den
How To Send Query Results To A Flat File?
Jul 26, 2001SQL 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?
MS SQL 6.5 Procedure To Send Query Results Via Email
Jul 20, 2005Hi,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 RelatedTransact SQL :: Send Email Only If Query Contains Results
Aug 8, 2015I’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] ....
Send Results Of SELECT FOR XML Query To A File
Feb 9, 2008
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
How Do I Attach Query Results To Send Mail
Jun 21, 2006I 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
Passing Variables In SP - Send Out Emails Based On Certain Criteria
Feb 11, 2015I was tasked with witing a stored procedure to send out emails based on certain criteria. One of our developers decided to take care of the criteria in another process and pass variables for me to use. I am not quite sure how to go about this, but below is what I started with. I declared his variables, and then set up a couple of my own for the email subject line and text.
create PROCEDURE [dbo].[SendWelcomeEmails]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
[Code] ....
Do I need to use a cursor or can I just specify using if, then else? Also not sure where to SET the individual subject lines.
SQl Express 2005 Suppoerts For Send Emails Using Stored Porcedures
Jun 3, 2008Hello to all,Can anybody please tell is SQl express 2005 suppoerts for send emails using stored porcedures?
SSIS SEND MAIL TASK EXCEL ATTACHMENT TIME OUT
Mar 3, 2008HI !
I need help to fix SSIS Send Mail Task Error.
I have a
Data Flow
Read from Sql Data base and then put the query result into excel file
(OLE DB Source --> Excel Destination)
Send Mail Task
Email excel file from the data flow
Control Flow :
Data Flow --> Send Mail Task
The package throws an error below. I tried to disable my antivirus because i thought it's throwing an error because of the antivirus.
But, it's still throwing the same error. If i tried to send email without attachment, it works just fine.
Is there anybody know how to fix this?
Progress: The SendMail task is initiated. - 0 percent complete
[Send Mail Task] Error: An error occurred with the following error message: "The operation has timed out.".
Progress: The SendMail task is completed. - 100 percent complete
Task Send Mail Task failed
Write Logg To A Textfile And Send That File As Attachment - Problem
Jul 5, 2006I would like to use logging to be able to view information about the package execution afterwards, especially to be able to find out which task that failed and why it failed.
Something similar to this:
-----------------------------------------------------------------------------
The execution of the following DTS Package succeeded:
Package Name: XXX
Package Description: YYY
Package ID: {5ADDA98B-1F27-404B-8EC4-3568FA4523F6}
Package Version: {0DAA5592-D123-4936-94FC-717DDC581866}
Package Execution Lineage: {4D353C5F-444E-4870-8A4F-B35B635F3646}
Executed On: ServerName
Executed By: XXX
Execution Started: 2005-06-22 07:14:27
Execution Completed: 2005-06-22 07:40:17
Total Execution Time: 1550,422 seconds
Package Steps execution information:
Step 'DTSStep_DTSDataPumpTask_1' succeeded
Step Execution Started: 2005-06-22 07:18:30
Step Execution Completed: 2005-06-22 07:18:31
Total Step Execution Time: 0,031 seconds
Progress count in Step: 37
Step 'DTSStep_DTSActiveScriptTask_2' was not executed
Step 'DTSStep_DTSExecuteSQLTask_33' failed
Step Error Source: Microsoft OLE DB Provider for SQL Server
Step Error Description:The statement has been terminated. (Microsoft OLE DB Provider for SQL Server (80040e2f): Cannot insert the value NULL into column 'ID_adress', table 'VPKBA.dbo.aktAdress'; column does not allow nulls. INSERT fails.)
Step Error code: 80040E2F
Step Error Help File:
Step Error Help Context ID:0
Step Execution Started: 2005-06-22 07:40:14
Step Execution Completed: 2005-06-22 07:40:17
Total Step Execution Time: 3,672 seconds
Progress count in Step: 0
-------------------------------------------------------------------------------------
I have tried different events to be logged on the Details-tab on Configure SSIS Logs, but I don't get the information that I want.
I also want to send this log-file as an attachment in a couple of Send Mail task in the same package. But then I get this error:
Error at Mail Error [Send Mail Task]: Either the file "filename.txt" does not exist or you do not have permissions to access the file.
The file does exist and there is no permission problem because I don't get this error when I remove logging.
Isn't it possible to do this in SSIS-packages?
It works in DTS-packages.
Regards,
Sara
Best Way To Send Email From A Stored Procedure (dynamically Changing Paramenters And Attachment)
Jul 23, 2005Hello everyone,I need advice of how to accomplish the following:Loop though records in a table and send an email per record. Emailrecipient, message text and attachment file name - that's all changesrecord by record.Is it doable from a stored procedure (easily I mean, or am I better offwriting a VB app)? There are so many options of sending mail from SQLserver - CDONTS, SQL MAIL TASK, xp_sendmail. What's easier to implementand set up?Thanks a lot!!!(links and fragments of sample code would be greatlyappreciated)Larisa
View 2 Replies View RelatedTransact SQL :: Convert Output Into Excel And Schedule Automate Job That Runs Every Friday And Send Email With Attachment
Nov 3, 2015Here below is the perfect query i made which is working fine and giving me the sql output but just only need is how to convert to excel and automate the job scheduling so that it run on everyday and send the mail with attachment .
SELECT DN, cn, displayName, mail, objectClass, sAMAccountName, Company, givenName, sn
FROM
(
SELECT DN, cn, displayName, mail, objectClass, sAMAccountName, Company, givenName, sn, 1 [ordering] FROM alpha.dbo.DCADFeed
where sAMAccountName collate SQL_Latin1_General_CP1_CI_AS in
[Code] ....
SQL 2012 :: Pasting Query Results Into XL From SSMS
Mar 13, 2014Previous to 2012, grid results from SSMS queries pasted into Excel beautifully and were easy to format.in 2008, The results from this query pasted into a spreadsheet with the query contained in a single Excel cell:
(sample - not the whole script)
select top 300
Avg_CPU_Time
,Total_Physical_Reads
,convert(datetime,Last_execution_time) as Timestamp
,Stored_Procedure
,Query_text
from dbadmin.dbo.History_CPU_IO_ByQueryAndSP
This was wonderful. I could deliver to developers wonderfully tidy reports on query resource usage with timestamps along with which stored procedure the queries came from.
Can't do that in SSMS 2012. Try it. It's a disaster. The Query_text, when pasted, spreads across multiple Excel columns, including the ones designated for other data. The result is totally unreadable.how to make the query_text stay in it's own cell? I've tried converting query_text to varchar and ntext. Same results.
SQL 2012 :: Group By In Sub Query To Get Desired Results
Jun 15, 2015I have to use a Group By in Sub query that will give me my desired results.
SELECT count(hc), CS, S_W, (SELECT COUNT(Reg_Temp)
FROM CS_S
WHERE Reg_Temp = "Reg") as [RegOnly]
FROM CS_S
GROUP BY S_W, CS
ORDER By CS, S_W
The query I have above counts ALL the Reg, I'd want it to give only the count that I'd get by using Group By clause i.e. S_W and CS.
SQL Server 2012 :: Tab Character In Query Results
Jul 9, 2015I am getting a tab character at the end of my query fields, been trying various things to fix, such as using the replace function below but still i get the tabs!
select CAST(REPLACE(NAMEALIAS,CHAR(9),'')AS CHAR(40)) + ',' as PRODNAME,
CAST(REPLACE(ISNULL(GLOBALTRADEITEMNUMBER,0),CHAR(9),'')AS CHAR(18))as EANNO,LTRIM(cast(ISNULL(GLOBALTRADEITEMNUMBER,0) as char(18))) as KONSEAN
,LTRIM(CAST(I.ITEMID AS CHAR(8))) AS PRODCODE,'00' from INVENTTABLE I
LEFT JOIN INVENTITEMGROUPITEM IG ON I.ITEMID = IG.ITEMID
[Code] ....
Results:
Gießanzeiger groß ,04260376730475 04260376730475 730475 00
Gießanzeiger klein , 04260376730352 04260376730352 730468 00
Gießanzeiger klein , 04260376730468 04260376730468 730468 00
SQL 2012 :: Query To Match Multiple Results And Average
Mar 3, 2014I work for a school district and new requirement we were just given for scoring some student scores.
Everything will currently be in one table to keep track of students test scores for various things. This table will contain students information and a student will be shown more than once in the table. The Student ID is what we will key off of to find the multiple instances of the student. The table contains the following columns: studentName, StudentId, teacherName, focus1, controllingIdeas1, reading1, development1, organization1, conventions1, and contentUnderstanding1. All of the columns with a 1 at the end will be numeric values with possible decimal values.
What we need to be able to do is some how perform a search for these multiple entries of each student and when found, average the 2 scores for each 7 test categories. The result needs to be a single line for each student that gives the student name, student id, and the 7 test category averages exported to an csv file.
SQL 2012 :: Limiting Query Results With 3 Criteria For Each Record
Mar 20, 2015I am trying to write a query that gives me the personal records from speed skaters on e.g. the 500 mtrs. I do this with the query:
SELECT cdsDistance AS Distance
, prsFirstName
, prsLastName
, min(crtFinalTime) AS MinTime
FROM tb....... INNER JOIN etc..
GROUP BY cdsDistance, prsFirstName, prsLastName
ORDER BY min(crtFinalTime)
In itself this works fine. However, there are complicating factors. Sometimes a speed skater has multiple PRs, meaning the he/she has the same fastest time more than once.
If these times are achieved on multple days, the 1st date is the official PR. (meaning: "Min of racedate")
If they are raced on the same day the 1st race is the PR (meaning: "Min of distancenumber")
Changing the code to:
SELECT cdsDistance AS Distance
, prsFirstName
, prsLastName
, MIN(crtFinalTime) AS MinTime
, MIN(cdsStartDate) AS RaceDate
, MIN(cdsDistanceNumber) AS DistanceNumber
FROM tb.......
GROUP BY cdsDistance, prsFirstName, prsLastName
ORDER BY min(crtFinalTime)
This gives me the wrong outcome because it gives me the "MIN" of every field, and they are not necessarily on the same row.
An option would be to calculate min(crtFinalTime), if for a person there is more than 1 result, calculate min of date, and then (if there is still more than 1 row) min of distancenumber.
Seems complicated, and I have the feeling there must be a better way (apart from: how to get this code)
Stacking subqueries in the FROM statement seems like a option be costly (time wise). There are more than 10 million rows (and growing) to run through.
As an example a few times:
DistanceFirst nameLast name Time Date Distance nr.
500 Yuya Oikawa 34.49 201311155
500 Yuya Oikawa 34.49 201311153
500 Yuya Oikawa 34.49 201311172
Yuya has 3 best times (34.49), 15-11-2013 is the 1st date, then distance nr 3 is the 1st distance raced. Therefore the 2nd row is the only row I would like to get in my endresult.
SQL Search :: 2012 / How To Update The Results Into Select Query Table
Oct 28, 2015I have created a table(T1) from select query result, that Select query is parameterised. Now I need to update the select query table(T1) based on the result every time.
Below is my Query:
ALTER PROCEDURE [dbo].[RPT_Cost_copy]
SELECT MEII.*, SIMM.U_SP2DC, UPPER(SIMM.U_C3C2) AS GRP3,sb.cost, PREV.Z1, PREV.Z3, SB.Z2, SB.Z4,SIMM.U_C3DC1 AS FAM
INTO T1
FROM
(SELECT a.meu, a.mep2, SUM(a.mest) as excst
FROM mei as A WHERE a.myar=@yr and a.mprd=@mth AND LTRIM(A.MCU) <> '' AND LTRIM(A.MRP2) <> ''
[code]....