Convert SQL Stored Procedure into a .txt

Question

Post reply

Convert SQL Stored Procedure into a .txt

gamarcon

SSC Rookie

Points: 40
More actions
April 24, 2008 at 2:30 pm

#75187

Hello,
I had created the following Stored Procedure:
USE Master
GO
CREATE PROCEDURE sql_monitoracao_diskspace
AS
SET NOCOUNT ON
CREATE TABLE #dbspace (
name sysname,
caminho varchar(200),
tamanho varchar(10),
drive Varchar(30))
CREATE TABLE [#espacodisco] (
Drive varchar (10) ,
[Tamanho (MB)] Int,
[Usado (MB)] Int,
[Livre (MB)] Int,
[Livre (%)] Varchar(100),
[Usado (%)] Varchar(100),
[Ocupado SQL (MB)] Int,
[Data] smalldatetime)
Exec SP_MSForEachDB 'Use ? Insert into #dbspace Select Convert(Varchar(25),DB_Name())''Database'',Convert(Varchar(60),FileName),Convert(Varchar(8),Size/128)''Size in MB'',Convert(Varchar(30),Name) from SysFiles'
DECLARE @hr int,@fso int,@mbtotal int,
@TotalSpace int,@MBFree int,
@Percentage int,@SQLDriveSize int,
@size float
DECLARE @drive Varchar(1),@fso_Method varchar(255)
SET @mbTotal = 0
EXEC @hr = master.dbo.sp_OACreate 'Scripting.FilesystemObject', @fso OUTPUT
CREATE TABLE #space (drive char(1), mbfree int)
INSERT INTO #space EXEC master.dbo.xp_fixeddrives
Declare CheckDrives Cursor For Select drive,MBfree From #space
Open CheckDrives
Fetch Next from CheckDrives into @drive,@MBFree
WHILE(@@FETCH_STATUS=0)
BEGIN
SET @fso_Method = 'Drives("' + @drive + ':").TotalSize'
SELECT @SQLDriveSize=sum(Convert(Int,tamanho)) from #dbspace where Substring(caminho,1,1)=@drive
EXEC @hr = sp_OAMethod @fso, @fso_method, @size OUTPUT
SET @mbtotal = @mbtotal + @size / (1024 * 1024)
INSERT INTO #espacodisco VALUES(@Drive+':',@MBTotal,@MBTotal-@MBFree,@MBFree,
Convert(Varchar,100 * round(@MBFree,2) / round(@MBTotal,2))+'%',
Convert(Varchar,100 - 100 * round(@MBFree,2) / round(@MBTotal,2))+'%',@SQLDriveSize, getdate())
FETCH NEXT FROM CheckDrives INTO @drive,@mbFree
END
CLOSE CheckDrives
DEALLOCATE CheckDrives
SELECT * FROM #espacodisco
DROP TABLE #dbspace
DROP TABLE #space
DROP TABLE #espacodisco
GO
And I want to apply that to a job.
The problem is: how can I get the results exported to a .txt file?
Thanks in advance

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

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

jezemine SSCrazy Eights Points: 8059 More actions · Answer 1

you could invoke bcp via xp_cmdshell to export results to a file.

---------------------------------------
elsasoft.org

gamarcon SSC Rookie Points: 40 More actions · Answer 2

Thanks =)

I was wondering if there´s a SQL script for that.

jezemine SSCrazy Eights Points: 8059 More actions · Answer 3

there are a great many examples of how to do this in this article:

http://www.simple-talk.com/sql/database-administration/creating-csv-files-using-bcp-and-stored-procedures%5B/url%5D

---------------------------------------
elsasoft.org