Print/Format OSQL Output

Question

Print/Format OSQL Output

DZJC

SSC-Addicted

Points: 415
More actions
October 31, 2006 at 7:53 am

#90746

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

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 1

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

DZJC SSC-Addicted Points: 415 More actions · Answer 2

Actually, the HTML does not come up fine, which is why I'm going down this path. Gotta love Outlook.

Russell Shilling SSCarpal Tunnel Points: 4399 More actions · Answer 3

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

DZJC SSC-Addicted Points: 415 More actions · Answer 4

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