query output to text files for each person

• Vijay-251247

  Say Hey Kid

  Points: 684

  More actions

  September 5, 2006 at 8:03 am

  #114409

  hi guys

  I've another question. How do i create individual text files for each person who has his notes field larger than 1000 chars?

  DECLARE @cmd VARCHAR(5000)

   

  SET @cmd = 'OSQL -S support_svr1 -d NCLREMOTE_SP6D '

      + ' -U sa -P seaward'

      + ' -Q "select notes from person where len(notes) > 1000"'

      + ' -o c:\Notes.txt'

   

  EXEC master..xp_cmdshell @cmd, NO_OUTPUT

  Thanks

• Sameer Raval

  Hall of Fame

  Points: 3214

  More actions

  September 5, 2006 at 9:04 am

  #658568

  You can probably use Cursor, but again that will be very expensive

  on performance part  or else try using XML output.

   

  Kindest Regards,

  Sameer Raval [Sql Server DBA]
  Geico Insurance
  RavalSameer@hotmail.com

• john-132685

  SSCertifiable

  Points: 6134

  More actions

  September 5, 2006 at 3:43 pm

  #658657

  Not quite a cursor and somewhat serial a solution (but still some of the glory of a set based solution):

  Run a query to create a separate OSQL (or BCP) command line for each DOS file you want created:

  select 'osql -S.... -Q"select textfield from sourcetable where id = '

         + convert(varchar(10), id )

         + '" ... -ofilename_' + username + '.txt'

    INTO ##tempbatch

    FROM sourcetable

   WHERE datalenghth( columnname ) > 1000 

  Then export that as a batch file using BCP or osql (with xp_cmdshell):

  exec xp_cmdshell 'BCP tempdb..##tempbatch OUT c:\temp\tempbatch.bat -S...'  

  Then run it as dos batch:

  exec xp_cmdshell 'call c:\temp\tempbatch.bat '  

  ...and that can all be done with one stored proc call.