T-sql variable to file ?

• brgadishwaran

  SSC Journeyman

  Points: 92

  More actions

  May 14, 2008 at 5:46 am

  #380510

  Hi ,

  I would like to know if there is any way to export the value stored in a t-sql variable inside a stored procedure into a text file .

  I have a requirement where i want to create dos batch files, html files dynamically / on the fly.

  thanks,

  Braga

• Hasan Mansur

  SSC Eights!

  Points: 876

  More actions

  May 14, 2008 at 7:27 am

  #815159

  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

  EXEC uspWriteToFile @FilePath= 'C:\\hasan.txt',@DataToWrite='Hasan mansur'

  IF you use sql server 2005 you have to "Enable OLE Automation" from sql server 2005 surface area configuration...

• Johan Bijnens

  SSC Guru

  Points: 135369

  More actions

  May 14, 2008 at 7:38 am

  #815177

  If I'm correct, this is one of the features SQLCMD has.

  You can use DOS-variables with it!

  Check SQLCMD in BOL !

  Johan
  
  Learn to play, play to learn !

  Dont drive faster than your guardian angel can fly ...
  but keeping both feet on the ground wont get you anywhere :w00t:

  - How to post Performance Problems
  - How to post data/code to get the best help[/url]

  - How to prevent a sore throat after hours of presenting ppt

  press F1 for solution, press shift+F1 for urgent solution 😀

  Need a bit of Powershell? How about this

  Who am I ? Sometimes this is me but most of the time this is me