Demo 1 - Email HTML | Demo
2 - Email PDF | Demo 3 - SQL to PDF | Demo
4 - Embedded Queries
Demo 5 - Prescheduled Queue | Demo
6 - Dynamic Queue | Demo 7 - Zip &
Password-Protect
Demo 8 - Mail Merge | Demo 9 -
Content Path | Demo 10 -
Excel Workbook | Demo 11 -
Word/RTF
Demo 10 - Sending multiple resultsets in Excel format
| Description |
If you need to email results of multiple queries but do not want to overwhelm
your users with attachments, Excel may be the right way to go. SqlAnswersMail
allows you to output Excel workbooks that have multiple worksheets, one
worksheet for each query result.
In this example, we use SqlAnswersMail to email a single Excel workbook
containing all the orders for the fictitious Northwind employees. To run the
sample, please provide an email address to send the results to and click on the Run
Demo 10 button. |
| Email |
|
| SQL Code |
Note, @SQL parameter in the following call was pre-generated.
Click here to see how @SQL was generated.
/* Declare a cursor that loops through the Employees table */DECLARE Employees CURSOR READ_ONLY FOR select EmployeeID, FirstName, LastName from Northwind..Employees DECLARE @EmployeeID int, @FirstName varchar(20), @LastName varchar(40), @Name varchar(100) OPEN Employees /* The purpose of this cursor is to generate SQL that can then be passed to sp_sendSAM. */DECLARE @SQL varchar(8000) SET @SQL = ' 'FETCH NEXT FROM Employees INTO @EmployeeID, @FirstName, @LastName WHILE (@@fetch_status <> -1) BEGIN IF (@@fetch_status <> -2) BEGIN /* For each employee, generate a nicely-formatted name field. Escape single apostrophes. */ | SET @Name = | replace(@FirstName + ' ' + @LastName,char(39),char(39)+char(39)) |
/* For each employee, append to @SQL a query that selects his/her orders from Orders. */ | SET @SQL = | @SQL + 'select OrderID,ShippedDate, Freight,ShipName from northwind..orders where EmployeeID=' + cast(@EmployeeID as varchar(10)) + '/ *<title>' + @Name + '</title><comments>Orders for ' + @Name + '</comments>*/;' |
END FETCH NEXT FROM Employees INTO @EmployeeID, @FirstName, @LastName END CLOSE Employees DEALLOCATE Employees
exec sp_SendSAM @Recipients= provided in form, @Subject= 'Demo email from SqlAnswersMail - Excel workbook', @Message= 'This message contains multiple query resultsets in a single Excel workbook. For more information, visit www.sqlanswers.com.', @Query=@SQL, @Attach_Results= 'true', @Attachment_Format = 'XLS' |