Write to File

Question

Write to File

LadyRuna

SSCrazy

Points: 2311
More actions
May 12, 2016 at 3:23 pm

#310631

Comments posted to this topic are about the item Write to File

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

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

LadyRuna SSCrazy Points: 2311 More actions · Answer 1

You can also modify the script to loop through rows in a temp table (selected as a single long concatenated string into the @Text variable) and repeatedly call the "writeLine" command ( [font="Courier New"]EXECUTE sp_OAMethod @FileID,'WriteLine',NULL,@Text[/font] ) to generate a formatted file that matches any output file requirements you need.

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 2

Nice Script but sp_OAMethod scripts are old com+ object types. Do you have any other latest scripting way to do the same?

Thanks.

mryan1001 SSC Enthusiast Points: 100 More actions · Answer 3

Here is a procedure that I wrote to encapsulate the error handling.

The test example is embedded in the comments

As with all software, It is a work in progress.

CREATE PROCEDURE [Tools].[TextFile] (

@Action SYSNAME = NULL, -- Open, Write, Close

@FileHandle XML = NULL OUTPUT,

@Buffer VARCHAR(MAX) = NULL

) AS

SET NOCOUNT ON;

/*

======================================================================================================================================

Author: MRyan

Created: 05/07/2016

Modified: 05/07/2016

Description: Write a file to the OS

Testing:

DECLARE @Filename VARCHAR(64) = 'C:\SQL\Scripts\Test',

@XFile XML = NULL;

EXECUTE Tools.TextFile [Open], @XFile OUTPUT, @Filename;

---

EXECUTE Tools.TextFile [Write], @XFile , '1One';

EXECUTE Tools.TextFile [Write], @XFile , '2Two';

EXECUTE Tools.TextFile [Write], @XFile , '3Three';

---

EXECUTE Tools.TextFile [Close], @XFile ;

--------------------------------------------------------------------------------------------------------------------------------------

EXECUTE ('EXEC sp_configure ''show advanced options'', 1; RECONFIGURE WITH OVERRIDE;');

EXECUTE ('EXEC sp_configure ''Ole Automation Procedures'',1; RECONFIGURE WITH OVERRIDE;');

EXECUTE ('EXEC sp_configure ''show advanced options'', 0; RECONFIGURE WITH OVERRIDE;');

======================================================================================================================================

*/

DECLARE

@FSO INT,

@RetVal INT,

@FID INT,

@Path SYSNAME = NULL,

@Name VARCHAR(255) = NULL;

----------------------------------------------------------------------

IF @Action = 'Open' BEGIN

SELECT @Name = LEFT(@Buffer,255);

----------------------------------------------------------------------

SELECT @Path = LEFT(@Name, LEN(@Name) - CHARINDEX('\',REVERSE(@Name)));

EXECUTE @RetVal = xp_create_subdir @Path;

IF @RetVal <> 0 RAISERROR('xp_create_subdir: Error Creating Path [%s]', 18, 1, @Path) WITH NOWAIT;

----------------------------------------------------------------------

-- create fso object

EXECUTE @RetVal = sp_OACreate 'Scripting.FileSystemObject', @FSO OUT;

IF @RetVal <> 0 RAISERROR('Scripting.FileSystemObject: Error Creating Object', 18, 1) WITH NOWAIT;

----------------------------------------------------------------------

-- Open a file

EXECUTE @RetVal = sp_OAMethod @FSO, 'OpenTextFile', @FID OUT, @Name, 2, 1; -- 2 write, 8 append

IF @RetVal <> 0 RAISERROR('Scripting.FileSystemObject: Error Opening File [s%]', 18, 1, @Name) WITH NOWAIT;

----------------------------------------------------------------------

SELECT @FileHandle = (SELECT @Name AS [@Name], @FSO AS [@FSO], @FID AS [@FID] FOR XML PATH('File'));

----

END ELSE BEGIN

SELECT

@Name = X.C.value('@Name', 'VARCHAR(255)'),

@FSO = X.C.value('@FSO', 'INT'),

@FID = X.C.value('@FID', 'INT')

FROM @FileHandle.nodes('/File') AS X(C);

END;

--======================================================================================================

IF @Action = 'Write' BEGIN

IF @Buffer IS NOT NULL BEGIN

----------------------------------------------------------------------

-- Write

EXECUTE @RetVal = sp_OAMethod @FID, 'WriteLine', Null, @Buffer;

IF @RetVal <> 0 RAISERROR('Scripting.FileSystemObject: Error Writing to File [s%]', 18, 1, @Buffer) WITH NOWAIT;

END;

END ELSE IF @Action = 'Close' BEGIN

----------------------------------------------------------------------

-- close file

EXECUTE @RetVal = sp_OAMethod @FID, 'Close';

IF @RetVal <> 0 RAISERROR('Scripting.FileSystemObject: Error Closing to File [s%]->[%i]', 18, 1, @Name, @FID) WITH NOWAIT;

----------------------------------------------------------------------

EXECUTE @RetVal = sp_OADestroy @FID;

IF @RetVal <> 0 RAISERROR('Scripting.FileSystemObject: Error Destroying File Handle [%i]', 18, 1, @FID) WITH NOWAIT;

--

EXECUTE @RetVal = sp_OADestroy @FSO;http://www.sqlservercentral.com/Forums/Skins/Classic/Images/RichTextBoxTable/tbl_bottomleft.gif

IF @RetVal <> 0 RAISERROR('Scripting.FileSystemObject: Error Destroying Object [%i]', 18, 1, @FSO) WITH NOWAIT;

END;

--------------------------------------------------------------------------------------------------------------------------------------

RETURN(0);