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