Write to File

• LadyRuna

  SSCrazy

  Points: 2421

  More actions

  May 12, 2016 at 3:23 pm

  #310631

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

• LadyRuna

  SSCrazy

  Points: 2421

  More actions

  May 26, 2016 at 8:29 am

  #1881573

  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

  May 26, 2016 at 4:55 pm

  #1881728

  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

  June 14, 2016 at 6:11 am

  #1884856

  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);