<div>
Hi,<br>I hope these links help:<br><a href="https://sqlskull.com/2020/03/07/how-to-send-a-sql-server-query-results-in-e-mail-message/" rel="ugc">How to send a SQL Server query results in e-mail message - SqlSkull</a>&nbsp;<br><a href="https://www.brentozar.com/archive/2014/10/send-query-results-sql-server-agent-job/" rel="ugc">Email Query Results Using a SQL Server Agent Job - Brent Ozar Unlimited®</a>&nbsp;
</div>


Senior Database Architecture 

marrowyung

<div>
tks,<br><br><blockquote><a href="https://www.brentozar.com/archive/2014/10/send-query-results-sql-server-agent-job/" rel="ugc">https://www.brentozar.com/archive/2014/10/send-query-results-sql-server-agent-job/</a></blockquote><br>but this one seems do not care about the format. anyway to force it to send out in excel format?<br><br>
</div>


<div>
You can change it from .txt to .csv format so you will be able to open it in Excel.
</div>


<div>
Also, to ensure that your date range is rock solid <a href="https://www.experts-exchange.com/articles/11210/Beware-of-Between.html" rel="ugc">Beware of Between</a><br><pre><code class="code-1 nolinks" id="code-20-43322987-1"><span>SELECT *</span>
<span>FROM QueryProcessHistory</span>
<span>WHERE collection_time &gt;= '20210728' AND collection_time &lt; '20210729' </span>
<span>ORDER BY collection_time desc</span>
</code></pre>Otherwise your query would not include a row with collection_time = '2021-07-28 23:59:59.555'
</div>


<div>
<a href="https://www.experts-exchange.com/members/AmirAzhdari.html" rel="ugc">Amir</a>,<blockquote>You can change it from .txt to .csv format</blockquote>tks. but what about column format in excel, anyway to control it by this process ?<br><br><a href="https://www.experts-exchange.com/members/jimhorn.html" rel="ugc">Jim</a> ,<br><br>but this condition works for a long time:<br>collection_time BETWEEN '2021-07-28 00:00:00.000' AND '2021-07-28 23:59:00.000' order by collection_time desc ; <br>why we change them ? <br><br>just for this process?<br><br>how about we need the data range 1 day ago? how to program it?<br><br>
</div>


<div>
&gt;but this condition works for a long time:<br>Unless this column only contains dates and not time values then it worked for the entire day other then the last minute, so using 23:59:00 would not have captured anything between 23:59:00 and 00:00:00 the next day.<br><br>&gt;how about we need the data range 1 day ago? how to program it?<br>If it's always the previous day, then use GETDATE() to get the current datetime, CAST(as date) to remove the time component, and a little math..<br><pre><code class="code-1 nolinks" id="code-20-43323361-1"><span>SELECT * </span>
<span>FROM QueryProcessHistory </span>
<span>WHERE collection_time &gt;= DATEADD(day, -1, CAST(GETDATE() as date)) </span>
<span>   AND collection_time &lt; CAST(GETDATE() as date))</span>
<span>ORDER BY collection_time desc</span>
</code></pre><br>
</div>


<div>
<a href="https://www.experts-exchange.com/members/lcohan.html" rel="ugc">lcohan</a> ,<br><br><blockquote>NOTE that you may need to limit the output of your query OR change the mail settings as the default attachment size as per link below is only 1MB</blockquote><br>result set send out can only be at most 1MB ? and more than 1MB the link is going to show us how to change the limit?<br><br><a href="https://www.experts-exchange.com/members/jimhorn.html" rel="ugc">Jim Horn</a> .<br><br><blockquote>SELECT * FROM QueryProcessHistory WHERE collection_time &gt;= DATEADD(day, -1, CAST(GETDATE() as date)) &nbsp; &nbsp;AND collection_time &lt; CAST(GETDATE() as date)) ORDER BY collection_time desc</blockquote><br>then just plug this one to:<br><br><pre><code class="code-1 nolinks" id="code-20-43323705-1"><span>SET @sql = N'</span>
</code></pre><blockquote>SELECT * FROM QueryProcessHistory WHERE collection_time &gt;= DATEADD(day, -1, CAST(GETDATE() as date)) &nbsp; &nbsp;AND collection_time &lt; CAST(GETDATE() as date)) ORDER BY collection_time desc</blockquote><pre><code class="code-10 nolinks" id="code-20-43323705-2"><span>'</span>
<span>PRINT @sql;</span>
<span></span>
<span>EXEC msdb.dbo.sp_send_dbmail</span>
<span>&nbsp; &nbsp; @profile_name = 'SQL Server Mail Profile Name Here',</span>
<span>&nbsp; &nbsp; @recipients = 'lcohan@mail.com',&nbsp;&nbsp; &nbsp; @query = &nbsp;@sql,</span>
<span>&nbsp; &nbsp; @attach_query_result_as_file= 1,</span>
<span>&nbsp; &nbsp; @query_attachment_filename='attachment_name_here.csv',</span>
<span>&nbsp; &nbsp; @query_result_header = 1,</span>
<span>&nbsp; &nbsp; @query_result_no_padding = 1,</span>
<span>&nbsp; &nbsp; @query_result_separator=' &nbsp; ',</span>
<span>&nbsp; &nbsp; @subject = @subjectstr;&nbsp;</span>
<span></span>
<span></span>
</code></pre>?&nbsp;
</div>


<div>
oh, this can run as a SQL job by SQL agent.
</div>


<div>
<a href="https://www.experts-exchange.com/members/fomand.html" rel="ugc">Andrei</a> ,<br><br>where in your script should I put my query &nbsp;? &nbsp;I can't find it .
</div>


<div>
Sorry for late reply on this " and more than 1MB the link is going to show us how to change the limit? " - here's how you can adjust that limit in MSSQL&nbsp;<a href="https://www.mssqltips.com/sqlservertip/4599/sql-server-database-mail-attachment-error/" rel="ugc">https://www.mssqltips.com/sqlservertip/4599/sql-server-database-mail-attachment-error/</a>
</div>


<div>
<div class="content wysiwyg-content fr-view">
hi,<br><br>we tried to email the result of the query to an email address.<br><br>what method we can do , any example for reference.<br><br>Query:<br><br><pre><code class="code-1 nolinks" id="code-10-29221722-1"><span>SELECT * FROM [QueryProcessHistory] WHERE collection_time BETWEEN '2021-07-28 00:00:00.000' AND '2021-07-28 23:59:00.000' order by collection_time desc ;</span>
</code></pre><br>we want to set the task run daily at 3am and the data should include 00:00:00 to 23:59:59 of previous day<br><br>
</div>
</div>



hi,
we tried to email the result of the query to an email address.
what method we can do , any example for reference.
Query:
SELECT * FROM [QueryProcessHistory] WHERE collection_time BETWEEN '2021-07-28 00:00:00.000' AND '2021-07-28 23:59:00.000' order by collection_time desc ;
we want to set the task run daily at 3am and the data should include 00:00:00 to 23:59:59 of previous day

email the result the a SQL query out

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.