April 26, 2021 at 2:43 pm
I was going to ask is there any easy way to port the output to an Xcel file?
If I understand your requirement - you want to run this as an agent job and deliver the results by email. If that is the case - then you cannot output to Excel, but you can create the output as a delimited file with a .csv extension that will open in Excel.
If you attach a CSV file and want to be able to open it in Excel directly from the email - you have to tell Excel that it is a delimited file. If you don't include that and the end user double-clicks on the attachment in email it will give them an error. They would then have to save the file to a folder and then open it.
Here is a template - replace the query with your query, making the necessary changes to the first column being returned as shown:
Declare @query nvarchar(max) = ''
, @recipients varchar(max) = 'your-recipient@somedomain.com'
, @cc_recipients varchar(max) = 'cc-recipient@somedomain.com';
Set @query = '
Set Nocount On;
Select t.col1 As [Sep=,' + char(13) + char(10) + 'Column1Name]
, t.col2
, t.col3
From dbo.tabl1 t
Where {some criteria};'
Execute msdb.dbo.sp_send_dbmail
@profile_name = '{Your Mail Profile}'
, @query = @query
, @subject = '{Your Subject}'
, @body = '{Your Body message here} - can be formatted as HTML'
, @recipients = @recipients
, @copy_recipients = @cc_recipients
, @execute_query_database = '{User Database Here}'
, @attach_query_result_as_file = 1
, @query_result_width = 8000
, @query_attachment_filename = '{Your Filename}.csv'
, @query_result_header = 1
, @query_result_separator = ','
, @query_result_no_padding = 1;
Another option is to embed the 'table' in the body of the message - which is more often a better choice because the end users don't need an attachment, they just need to see the numbers.
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
April 26, 2021 at 4:42 pm
You can run the query and output the data as a CSV, which Excel can load as a spreadsheet.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
April 26, 2021 at 4:45 pm
could I call SP from powershell and output to csv?
April 26, 2021 at 5:11 pm
could I call SP from powershell and output to csv?
Why are you changing the requirements now? You started with a question about dynamic pivot - that was answered by @JeffModen - you then stated you want to send this in email and Excel format - which was answered. Now...you bring in Powershell?
Personally - I wouldn't create a file. I would embed an HTML table in the body of an email message and send that out to an email group. Schedule that as an agent job and be done - if you really need it as an attachment, then use the template I provided.
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
April 26, 2021 at 5:16 pm
I can wrap the results of the dynamic pivot - into the email?
Thanks
April 26, 2021 at 6:02 pm
I can wrap the results of the dynamic pivot - into the email?
Thanks
Yes - here is a template:
Set Nocount On;
Declare @body nvarchar(max)
, @xmlResults varchar(max)
, @tableHeader varchar(max)
, @recipients varchar(max) = 'youremailrecipient@domain.com'
, @cc_recipients varchar(max) = '';
--==== Create the table header
Set @tableHeader = cast((Select html.hdr1 As th, ''
, html.hdr2 As th, ''
, html.hdr3 As th, ''
From (
Values ('Hdr1', 'Hdr2', 'Total')
) As html(hdr1, hdr2, hdr3)
For xml Path('tr'), elements) As varchar(max));
--==== Get the results as an XML table
Set @xmlResults = cast((Select col1 As td, ''
, col2 As td, ''
, count(*) As td, ''
From yourTable
For xml Path('tr'), elements) As varchar(max));
--==== Send Notification if we have results
If @xmlResults Is Not Null
Begin
--==== Setup the CSS style of the message
Set @body = '<style type=''text/css''>';
Set @body += '{font-family:Tahoma, Arial, Helvetica, sans-serif; font-smoothing:always; width:100%; border-collapse:collapse;}';
Set @body += 'td {font-size:10pt; text-align:center; border:1px DarkCyan solid; padding:2px 2px 2px 2px;}';
Set @body += 'th {font-size:10pt; text-align:center; padding:2px 2px 2px 2px; background-color:DarkCyan; color:White;}';
Set @body += 'name tr {color:Black; background-color:DarkCyan;}';
Set @body += '</style>'
--==== Setup the body of the message
Set @body += '<html><body>Body Paragraph.
';
--==== Setup the table with the list of new document types
Set @body += '<table style="margin-left:30px">' + @tableHeader + @xmlResults + '</table>';
--==== Close the body and html
Set @body += '</body></html>';
--==== Send the HTML formatted email message
Execute msdb.dbo.sp_send_dbmail
@profile_name = 'Your Profile'
, @from_address = 'FromAddress@SomeDomain.com'
, @reply_to = 'ActualReplyTo@somedomain.com'
, @recipients = @recipients
, @copy_recipients = @cc_recipients
, @subject = 'Subject Here'
, @body_format = 'HTML'
, @body = @body;
End
Go
You will need to dynamically create the header - and detail results. In the dynamic pivot - you are building the list of columns, and just need to incorporate that into the header. Modify the dynamic pivot to return the data as @xmlResults - and adjust the CSS style.
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
April 26, 2021 at 11:20 pm
Thanks for the template, I have it working sending the XML - email...
Thanks ALL!!!!
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy