How to run results of query to a text file?

[hblabonte]

New to SQL 2005 and I'm trying to get a stored procedure that will extract the query info into a csv file to the server. I know how to do it in 2000, but it didn't work in 2005. I'd surely appreciate some help on this.

Here's the code:


USE [msdb]
GO
/****** Object: StoredProcedure [dbo].[CRH_TestExtract] Script Date: 02/09/2009 16:01:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[CRH_TestExtract]
AS

DECLARE @FILENAME VARCHAR(50)

SET @FILENAME = 'D:\test.csv'

SET NOCOUNT ON

DECLARE
@blah varchar (255)


DECLARE @File int
DECLARE @Return int
DECLARE @Retain int
DECLARE @Status int
DECLARE @Fuse int
DECLARE @Next varchar(8000)
DECLARE @Save varchar(8000)
SET @Status = 0

SET NOCOUNT ON

DECLARE CRFIXER CURSOR FOR

--some sample query
SELECT filegroup_name
FROM dbo.backupfile

OPEN CRFIXER

FETCH CRFIXER INTO

@blah
IF @@FETCH_STATUS <> -1 --RECORDS FOUND
BEGIN
IF @Status = 0
BEGIN
EXECUTE @Return = sp_OACreate 'Scripting.FileSystemObject', @Fuse OUTPUT
END
SET @Retain = @@ERROR
IF @Status = 0 SET @Status = @Retain
IF @Status = 0 SET @Status = @Return

IF @Status = 0
BEGIN
EXECUTE @Return = sp_OAMethod @Fuse, 'CreateTextFile', @File OUTPUT, @FILENAME, -1

END
SET @Retain = @@ERROR
IF @Status = 0 SET @Status = @Retain
IF @Status = 0 SET @Status = @Return

IF ISNULL(@File,0) = 0
BEGIN
PRINT @Next
END
ELSE
BEGIN
SET @Save = @Next + CHAR(13) + CHAR(10)
IF @Status = 0 EXECUTE @Return = sp_OAMethod @File, 'Write', NULL, @Save
IF @Status = 0 SET @Status = @Return
END
END

WHILE @@FETCH_STATUS <> -1
BEGIN

SET @NEXT = ISNULL(@blah,'')

IF ISNULL(@File,0) = 0
BEGIN
PRINT @Next
END
ELSE
BEGIN
SET @Save = @Next + CHAR(13) + CHAR(10)
IF @Status = 0 EXECUTE @Return = sp_OAMethod @File, 'Write', NULL, @Save
IF @Status = 0 SET @Status = @Return
END

FETCH CRFIXER INTO
@blah
END

CLOSE CRFIXER
DEALLOCATE CRFIXER

ABORT:

IF @Status = 1 OR @Status NOT BETWEEN 0 AND 50000 RAISERROR ('Windows error [%d]',16,1,@Status)

 

[hblabonte]

I should add, I can get this to work using SQLCMD, but my manager wants this as a scheduled SQL job (like DTS). It works, but hey, I have to ask!

 

[BIExperts]

In SQL Server 2005 or higher, you can write a CLR stored procedure and save the data using .NET framework classes. You can also use Integration Services (replacement of DTS) to export the data into .csv file. Start Import/Export Wizard from Object Explorer in the Management Studio and follow it's instructions. In thge end, save export definition as a package and schedule a job to execute the package regurarly.