Create a text file - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Create a text file

Author

Topic

vmurali
Yak Posting Veteran

88 Posts

Posted - 2008-10-16 : 09:22:46

Hi in my project I need to write a result into a text file. I just wanted to know is it possible to create a text file from SQl and write the result from a query to the created text file.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-10-16 : 09:30:16

interactively (in management studio) you can specifiy "Results to file"
by menu: Query | Results to
or by right-clicking the query window.

Be One with the Optimizer
TG

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2008-10-16 : 09:42:32

or by using bcp with xp_cmdshell

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2008-10-16 : 11:00:16

From Phil Factor's website:

CREATE PROCEDURE spWriteStringToFile
(
@String Varchar(max), --8000 in SQL Server 2000
@Path VARCHAR(255),
@Filename VARCHAR(100)

--
)
AS
DECLARE @objFileSystem int
,@objTextStream int,
@objErrorObject int,
@strErrorMessage Varchar(1000),
@Command varchar(1000),
@hr int,
@fileAndPath varchar(80)

set nocount on

select @strErrorMessage='opening the File System Object'
EXECUTE @hr = sp_OACreate 'Scripting.FileSystemObject' , @objFileSystem OUT

Select @FileAndPath=@path+'\'+@filename
if @HR=0 Select @objErrorObject=@objFileSystem , @strErrorMessage='Creating file "'+@FileAndPath+'"'
if @HR=0 execute @hr = sp_OAMethod @objFileSystem , 'CreateTextFile'
, @objTextStream OUT, @FileAndPath,2,True

if @HR=0 Select @objErrorObject=@objTextStream,
@strErrorMessage='writing to the file "'+@FileAndPath+'"'
if @HR=0 execute @hr = sp_OAMethod @objTextStream, 'Write', Null, @String

if @HR=0 Select @objErrorObject=@objTextStream, @strErrorMessage='closing the file "'+@FileAndPath+'"'
if @HR=0 execute @hr = sp_OAMethod @objTextStream, 'Close'

if @hr<>0
begin
Declare
@Source varchar(255),
@Description Varchar(255),
@Helpfile Varchar(255),
@HelpID int

EXECUTE sp_OAGetErrorInfo @objErrorObject,
@source output,@Description output,@Helpfile output,@HelpID output
Select @strErrorMessage='Error whilst '
+coalesce(@strErrorMessage,'doing something')
+', '+coalesce(@Description,'')
raiserror (@strErrorMessage,16,1)
end
EXECUTE sp_OADestroy @objTextStream
EXECUTE sp_OADestroy @objTextStream

---------------------------------------------
--To Set Result of a query into file:
---------------------------------------------

declare @value varchar(max)
set @value = (select Colour from (select top 1 Colour from dbo.B)t)

execute spWriteStringToFile
@value , 'C:\Temp','MyFileName1.txt'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources