Automate SQL query to send excel spreadsheet via email
-
I have this SQL query that runs perfect. I now would like to automate it as a stored procedure that emails the results in an Excel spreadsheet. Any pointer? Thanks for any help you can provide.
Here is the query:
select SiteID,
case
when SiteID = '001' then 'Omaha Terminal'
else 'Geneva Terminal'
end as 'Site Name'
,rtrim([TransNum])+replicate(' ',5-len(TransNum)) [Transaction Num]
,rtrim([Date])+replicate(' ',20-len(Date)) [Transaction Date]
,rtrim([Time])+replicate(' ',20-len(Time)) [Time]
,rtrim([Card1])+replicate(' ',20-len(Card1)) [Card]
,rtrim(c.[Name])+replicate(' ',20-len(c.Name)) [Name]
,rtrim(t.[UserEntry2])+replicate(' ',10-len(t.UserEntry2)) [Tractor]
,rtrim(t.[UserEntry3])+replicate(' ',10-len(t.UserEntry3)) [Odometer]
,rtrim([Qty])+replicate(' ',5-len(Qty)) [Quantity]
from Transactions t
left join Card c
on t.Card1 = c.CardNumber
where t.Date between '07-19-2021' and '07-20-2021'
order by [Transaction Date] desc -
you can always use SSIS, but you'll need to install the ODBC drive (Microsoft.ACE.OLEDB)
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/ -
Does it have to be an Excel file - or does it just need to be able to be opened in Excel? If the latter - you can use sp_send_dbmail and create a CSV file. Note: there is a trick to doing that and having it be able to be opened in Outlook.
I can provide a template if that is something you are interested in doing.
Or - as stated above you can use SSIS, but I would recommend using SSRS instead as it is a much easier process to be done than doing that in SSIS.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
It just has to be opened in Excel, so CSV file would work also. Thank you so much for your help!
-
Here is a basic template:
Set Nocount On;
Declare @query nvarchar(max) = ''
, @recipients varchar(max) = 'user1@domain.com'
, @cc_recipients varchar(max) = 'user2@domain.com';
Set @query = '
Set Nocount On;
Select t.Column1 As [Sep=,' + char(13) + char(10) + 'Column1]
, t.Column2
, t.Column3
, t.Column4
, t.Column5
, t.Column6
From dbo.SomeTable;'
Execute msdb.dbo.sp_send_dbmail
@profile_name = 'PublicOrPrivateProfile'
, @query = @query
, @subject = 'Subject'
, @body = 'Body Message Here'
, @recipients = @recipients
, @copy_recipients = @cc_recipients
, @execute_query_database = 'UserDatabase'
, @attach_query_result_as_file = 1
, @query_result_width = 8000
, @query_attachment_filename = 'Filename.csv'
, @query_result_header = 1
, @query_result_separator = ','
, @query_result_no_padding = 1;The first column needs to be defined with the [Sep=, - followed by a CR/LF - then the name of the column followed by the closing bracket.
You should also be aware that any numeric style columns that have leading zeroes will be converted by Excel to actual numeric values and drop the leading zero. To force the column to be interpreted as a text column - concatenate a leading single-quote to the column. For example: concat(char(39), column3)
For date/time column you also want to strip out the sub-seconds or Excel will display those as time only (but the full data does exist - just not formatted correctly). You can also convert the datetime to a smalldatetime and Excel will display that correctly.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
Thank you so much for your help.
Is there a way to add the date to the end of the filename? For example, Filename072221.csv
-
I did get this query to run successfully, however, no email ever was received. I checked Database Mail Log and it didnt show any activity at the time I ran query. Any ideas what I might be missing? Thanks!
USE [PhoenixSQL]
GO
/****** Object: StoredProcedure [dbo].[wynne_fuel_report] Script Date: 7/22/2021 7:37:40 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[wynne_fuel_report]
as
begin
DECLARE @FileName varchar(35)
SELECT @FileName = 'Wynne-' + convert(nvarchar(MAX), dateadd(day,-7, getdate()), 10) + ' thru ' + convert(nvarchar(MAX), dateadd(day,-1, getdate()), 10) + '.csv'
EXEC msdb..sp_send_dbmail
@profile_name = 'Admin',
@recipients = 'thunter@xxxx.com',
@subject = 'Wynne Transport: Freight Invoicing Spreadsheet',
@body = 'Test CSV output',
@query = 'EXEC [dbo].[Wynne_Fuel_Import]',
@execute_query_database = 'PhoenixSQL',
@attach_query_result_as_file = 1,
@query_result_separator ='',
@query_result_no_padding=1,
@query_result_header =0,
@query_result_width=32767,
@query_attachment_filename = @FileName
end; -
When you run that code - there should be a message returned. What is the message that is being returned?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
Here is the message:
Commands completed successfully.
Completion time: 2021-07-22T07:52:15.4415132-05:00
-
It also seems like my first row is actually showing the headers, can you help with a fix for that please. See screenshot.
-
You are adding the header row with the UNION - remove that and convert the actual select statement to include the separator designation. You are then removing that header row in the send_db_mail portion - which is required in order for that data to be included in the file so that Excel can read it.
The return value should be a notification that the email was queued. Since you are not getting that notification - no mail message has been queued.
I would not separate the query and the sending of the mail into separate procedures.
Last item: the template shows how to dynamically build the query that is used by sp_send_dbmail. It needs to be dynamic so the CR/LF is embedded in the header - as you have it now it isn't embedding the CR/LF, rather it is just placing the strings.
Oh - the code you are using to pad the columns out to a specific length isn't doing anything and isn't needed. If you need to make sure a column doesn't exceed a certain length - then just cast that column to the specific length - cast([SiteID] As varchar(5)) - this will 'truncate' the column to no more than 5 characters.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
Thank you for all your assistance, I really appreciate it and have learned a lot!
One last question - This report is going to run every 2-3 days so I want it to pull all the results for those days, however, my current script only pulls from one day, how do I get the range of dates.
Here is what I am using now, and it only shows the 5th day back instead of all days since 5 days ago. Thanks!
where t.Date = cast(dateadd(dd,-5,getdate()) as date)
-
Change it to greater than or equal:
Where t.Date >= cast(dateadd(day, -5, getdate()) As date)
If the data type of t.Date is a datetime - then you would want to eliminate the implicit conversion:
Where t.Date >= dateadd(day, datediff(day, 0, getdate()) - 5, 0)
And if you want to exclude today since that would be dependent on the time of day it is run, add:
And t.Date < cast(getdate() As date)
Or for datetime:
And t.Date < dateadd(day, datediff(day, 0, getdate()), 0)
A better approach may be to run every weekday for the previous day - and on Monday run for Friday through Sunday. That can be done by creating a variable - default to 1 and if today is Monday set to 3.
Declare @daysBack int = iif(datename(weekday, getdate()) = 'Monday', 3, 1);
Where t.Date >= dateadd(day, datediff(day, 0, getdate()) - @daysBack, 0)
And t.Date < dateadd(day, datediff(day, 0, getdate()), 0)Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply