write dbcc command result to excel

Question

write dbcc command result to excel

CraigIW

SSCrazy

Points: 2301
More actions
April 20, 2012 at 4:56 am

#267743

I want to write a SQL job that generates an excel file containing the result of DBCC SQLPERF(LOGSPACE).
All the examples I have seen don't seem to work with the output of DBCC commands.
What approaches can I use for this? I am using SQL 2008.
Thanks.

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

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

SSIS is probably the way to go here. Otherwise, create a table with the same columns and data types as the DBCC output, and do INSERT INTO MyTable ('EXEC DBCC SQLPERF(LOGSPACE)'). You can then bcp out to Excel (or set it up your spreadhseet as a linked server) in the normal way.

John

CraigIW SSCrazy Points: 2301 More actions · Answer 2

I'm going to completely turn this around now and say, I don't want to capture the result and email a file, I want to show the result in the body of an email.

How can I do that in SSIS?

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

Does this help, or is there something in particular you don't understand about sp_send_dbmail?

John

CraigIW SSCrazy Points: 2301 More actions · Answer 4

Hi John,

I just didn't know about that system proc. Thanks!

CraigIW SSCrazy Points: 2301 More actions · Answer 5

I was sick of creating SSIS packages, especially for something that should be quite simple, and wanted to try

the sp_send_dbmail proc that John pointed out.

The proc worked straight away but the results were pretty ugly, so I made it all HTML. This is what I came up with:

DECLARE @HTML NVARCHAR(MAX);

SET @HTML = '<table><tr><td>Database</td><td>Log Size</td><td>Percent</td><tr>';

DECLARE @SQLPERFRESULT TABLE

(

DBName varchar(32),

LogSize real,

LogSpaceUsed real,

Status int

);

DECLARE @SQLPERFCOMMAND varchar(500);

SELECT @SQLPERFCOMMAND = 'DBCC SQLPERF(LOGSPACE)';

INSERT INTO @SQLPERFRESULT EXEC (@SQLPERFCOMMAND);

SELECT @HTML = @HTML + '<tr><td>' + DBName + '</td><td>' + CONVERT(VARCHAR,LogSize) + '</td><td>' + CONVERT(VARCHAR,LogSpaceUsed) + '</td></tr>'

FROM @SQLPERFRESULT;

SET @HTML = @HTML + '</table>';

EXEC msdb.dbo.sp_send_dbmail

@recipients = 'admins@company.com',

@body = @HTML,

@body_format = 'HTML',

@subject = 'SQL Log Space';

Craig