SQL Server Performance Forum – Threads Archive

creating and writing to a txt file using transact-

Hi guys,
Struggling to write some transact-sql code to create then write to a txt file.
can anyone help?
JeanLuc[?]

---

You can use bcp Exec Master..xp_cmdShell ‘bcp "Select * from DBname..yourtable" queryout "D: est.txt" -c’
Madhivanan Failing to plan is Planning to fail

---

Posted – 06/04/2004 : 17:03:51
——————————————————————————– Forget the extended stored procedure, use a COM object from within SQL. This stored proc works, enjoy. Email me if you have any questions. -Joe
CREATE PROCEDURE dbo.uspWriteToFile
@FilePath as VARCHAR(255),
@DataToWrite as TEXT
— @DataToWrite as VARCHAR(8000)
AS
SET NOCOUNT ON
DECLARE @RetCode int , @FileSystem int , @FileHandle int EXECUTE @RetCode = sp_OACreate ‘Scripting.FileSystemObject’ , @FileSystem OUTPUT
IF (@@ERROR|@RetCode > 0 Or @FileSystem < 0)
RAISERROR (‘could not create FileSystemObject’,16,1) EXECUTE @RetCode = sp_OAMethod @FileSystem , ‘OpenTextFile’ , @FileHandle OUTPUT , @FilePath, 2, 1
IF (@@ERROR|@RetCode > 0 Or @FileHandle < 0)
RAISERROR (‘Could not open File.’,16,1) EXECUTE @RetCode = sp_OAMethod @FileHandle , ‘Write’ , NULL , @DataToWrite
IF (@@ERROR|@RetCode > 0)
RAISERROR (‘Could not write to file ‘,16,1) EXECUTE @RetCode = sp_OAMethod @FileHandle , ‘Close’ , NULL
IF (@@ERROR|@RetCode > 0)
RAISERROR (‘Could not close file’,16,1) EXEC sp_OADestroy @FileSystem
RETURN( @FileHandle )
ErrorHandler:
EXEC sp_OADestroy @FileSystem
RAISERROR (‘could not create FileSystemObject’,16,1)
RETURN(-1)
GO

---

]]>