Automate SQL query to send excel spreadsheet via email

%3CLINGO-SUB%20id%3D%22lingo-sub-2572107%22%20slang%3D%22en-US%22%3EAutomate%20SQL%20query%20to%20send%20excel%20spreadsheet%20via%20email%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2572107%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20this%20SQL%20query%20that%20runs%20perfect.%20I%20now%20would%20like%20to%20automate%20it%20as%20a%20stored%20procedure%20that%20emails%20the%20results%20in%20an%20Excel%20spreadsheet.%20Any%20pointer%3F%20Thanks%20for%20any%20help%20you%20can%20provide.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20is%20the%20query%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-cpp%22%3E%3CCODE%3Eselect%20SiteID%2C%0Acase%20%0A%20when%20SiteID%20%3D%20'001'%20then%20'Omaha%20Terminal'%0A%20else%20'Geneva%20Terminal'%0A%20end%20as%20'Site%20Name'%0A%20%20%20%2Crtrim(%5BTransNum%5D)%2Breplicate('%20'%2C5-len(TransNum))%20%5BTransaction%20Num%5D%0A%20%20%20%2Crtrim(%5BDate%5D)%2Breplicate('%20'%2C20-len(Date))%20%5BTransaction%20Date%5D%0A%20%20%20%2Crtrim(%5BTime%5D)%2Breplicate('%20'%2C20-len(Time))%20%5BTime%5D%0A%20%20%20%2Crtrim(%5BCard1%5D)%2Breplicate('%20'%2C20-len(Card1))%20%5BCard%5D%0A%20%20%20%2Crtrim(c.%5BName%5D)%2Breplicate('%20'%2C20-len(c.Name))%20%5BName%5D%0A%20%20%20%2Crtrim(t.%5BUserEntry2%5D)%2Breplicate('%20'%2C10-len(t.UserEntry2))%20%5BTractor%5D%0A%20%20%20%2Crtrim(t.%5BUserEntry3%5D)%2Breplicate('%20'%2C10-len(t.UserEntry3))%20%5BOdometer%5D%0A%20%20%20%2Crtrim(%5BQty%5D)%2Breplicate('%20'%2C5-len(Qty))%20%5BQuantity%5D%0Afrom%20Transactions%20t%0Aleft%20join%20Card%20c%0Aon%20t.Card1%20%3D%20c.CardNumber%0Awhere%20t.Date%20%20between%20'07-19-2021'%20and%20'07-20-2021'%0Aorder%20by%20%5BTransaction%20Date%5D%20desc%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2572107%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3Eautomation%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EEmail%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EStored%20Procedure%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2573545%22%20slang%3D%22en-US%22%3ERe%3A%20Automate%20SQL%20query%20to%20send%20excel%20spreadsheet%20via%20email%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2573545%22%20slang%3D%22en-US%22%3E%3CA%20href%3D%22https%3A%2F%2Fwww.sqlservercentral.com%2Fscripts%2Fcreate-excel-xls-from-t-sql%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.sqlservercentral.com%2Fscripts%2Fcreate-excel-xls-from-t-sql%3C%2FA%3E%3CBR%20%2F%3E%3CBR%20%2F%3EYou%20will%20need%20replace%20in%20this%20scrip%20%22Microsoft.Jet.OLEDB.4.0%22%20to%20%22Microsoft.ACE.OLEDB.12.0%22%3CBR%20%2F%3Eand%20install%20%22Microsoft%20Access%20Database%20Engine%202010%20Redistributable%20%22%20x32%20or%20x64%20-%20depending%20what%20version%20of%20Office%20you%20have%20installed.%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E
Super Contributor

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
1 Reply

https://www.sqlservercentral.com/scripts/create-excel-xls-from-t-sql

It creates excel file, you can send it as attachment.

The script is very old,  you will need replace in this script "Microsoft.Jet.OLEDB.4.0" to "Microsoft.ACE.OLEDB.12.0"
and install "Microsoft Access Database Engine 2010 Redistributable " x32 or x64 - depending what version of Office you have installed.