October 31, 2006 at 7:53 am
Hi All,
I have what I thought would be a fairly simple request to handle, but it's turned into a nightmare. I've created a web report from a query using sp_makewebtask and it came out nice. Now the customer would rather have the report emailed. No problem I thought, so I figured I'd use osql to generate the output. Ugly output for sure. So is there a way to format the output to make it look more presentable?
SQLCMD is not an option unfortunately.
Here's the output (note that it wraps because of the width):
Name |InactiveJobs|TotalJobs |ModificationTime
--------------------------------------------------------------------------------------------------------------------------------|------------|-----------|-----------------------
server22 | 14| 21|2006-10-30 15:57:33.000
server32 | 14| 21|2006-10-30 15:56:30.000
And there's the osql command I'm using:
osql -E -otest.txt -imm.sql -n -w 512 -s "|"
Thanks,
Dale
October 31, 2006 at 10:15 pm
Why not just email the HTML that sp_MakeWebTask creates? When they double click on it as an attachment, it'll come up just fine.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 1, 2006 at 8:23 am
Actually, the HTML does not come up fine, which is why I'm going down this path. Gotta love Outlook.
November 1, 2006 at 2:26 pm
Use xp_sendmail, with a query designed to look better? That will also accomplish the emailing part of the task:
exec master..xp_sendmail
@recipients = 'me@company.com;',
@subject = 'Server Drive Free Space',
@message = 'Server Drive Free Space',
@dbuse = 'Utility',
@width = 255,
@query = '
CREATE TABLE #FixedDrives
(DriveVARCHAR(10),
MB_FreeDEC(20,2))
INSERTINTO #FixedDrives EXEC Master..XP_FixedDrives
SELECTleft(@@SERVERNAME,20) AS Server,
Drive,
MB_Free
FROM #FixedDrives
DROP TABLE #FixedDrives
'
So long, and thanks for all the fish,
Russell Shilling, MCDBA, MCSA 2K3, MCSE 2K3
November 1, 2006 at 2:35 pm
I work in a very restrictive environment and xp_sendmail is not an option, because the customer won't allow me to install Outlook on the SQL machine. Other suggestions?
Thanks
Viewing 5 posts - 1 through 5 (of 5 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