Retrieve 'Text file' from a table

The following BCP utility worked for the issue I posted earlier. The only issue pending is getting the timestamp from BillDt column. For formatting purpose we're inserting @@@ on all header records but what we noticed is that the BillDt (timestamp) is not displayed properly (see below the results from bcp utility). So, How do I get the BillDt in the correct format?

BCP Utility:

C:>bcp "SELECT TOP 5 '@@@', [BillDt], [FileName], [FileExt], [BillImage] FROM [Eclips].[dbo].[tblBill] WHERE BillType in ('ITBP2007', 'CURRENT_MONTH_DEMAND' )" queryout C:TempeClipseClipsdata.dat -N -S SQLSERVER01TEST -T

Results from BCP utility: @ @ @ ?ž H 9 9 9 9 - 9 9 9 9 - 9 9 _ 2 0 1 0 - 1 2 - 0 1 _ I T B P 2 0 0 7 . h t m H T M öu
@ @ @ !ž H 9 9 9 9 - 9 9 9 9 - 9 9 _ 2 0 1 0 - 1 1 - 0 1 _ I T B P 2 0 0 7 . t x t T X T u
@ @ @ å J 0 0 3 0 - 0 6 7 3 - 0 1 _ C U R R E N T _ M O N T H _ D E M A N D . T X T T X T :[

If I understand you right, you have a table in which there is a column that is either TEXT, NTEXT, VARCHAR(MAX) or NVARCHAR(MAX). You want to save the value in every row to a separate file.

The obvious way of doing this is to use a PowerShell script, especially if you wish to save on the 'client' rather than the Server.

If you need to extract the text as files on the server, as part of an archiving perhaps, or to do a file-based process, there are a variety of different ways. I'd recommend using PowerShell, again, but it depends on the circumstances, and the systems that you are most familiar with.

If you need to do it in TSQL via BCP, then that's possible. If each row represents a file, then I think you'll have to get each 'text file' value from the table and then execute this procedure to save it to disk, supplying the file path for each row. Here is a general procedure to write out a VARCHAR(MAX) to disk as a file. (taken from my article The TSQL of text files . I suspect that there is a much cleaner way of doing this in your case since the text file is already a row in a permanent table, and my routine is for a variable; but I haven't the time to write it (and test it!).

----------------------------------------------------------------- IF OBJECT_ID (N'dbo.spSaveTextToFile') IS NOT NULL DROP PROCEDURE dbo.spSaveTextToFile GO CREATE PROCEDURE spSaveTextToFile @TheString VARCHAR(MAX), @Filename VARCHAR(255), @Unicode INT=0 AS SET NOCOUNT ON DECLARE @MySpecialTempTable VARCHAR(255) DECLARE @Command NVARCHAR(4000) DECLARE @RESULT INT

--firstly we create a global temp table with a unique name
  SELECT  @MySpecialTempTable = '##temp'
   + CONVERT(VARCHAR(12), CONVERT(INT, RAND() * 1000000))
--then we create it using dynamic SQL, & insert a single row
--in it with the MAX Varchar stocked with the string we want
  SELECT  @Command = 'create table ['
   + @MySpecialTempTable
   + '] (MyID int identity(1,1), Bulkcol varchar(MAX))
insert into ['
   + @MySpecialTempTable
   + '](BulkCol) select @TheString'
  EXECUTE sp_ExecuteSQL @command, N'@TheString varchar(MAX)',
       @TheString

    --then we execute the BCP to save the file
  SELECT  @Command = 'bcp "select BulkCol from ['
      + @MySpecialTempTable + ']'
      + '" queryout '
      + @Filename + ' '
     + CASE WHEN @Unicode=0 THEN '-c' ELSE '-w' END
      + ' -T -S' + @@servername
  EXECUTE @RESULT= MASTER..xp_cmdshell @command, NO_OUTPUT
  EXECUTE ( 'Drop table ' + @MySpecialTempTable )
  RETURN @result
GO