title: T-SQL writing to a file txt or csv
body: <p>I've spent all day scouring the net on answers. Apparently tsql doesn't have its own nifty write to file commands. Here is my dilemma</p>
  
  <p>I have a load file that I am creating where a single line can reach 10K+ in length. On SQL Server varchar(MAX) limit is 8000 (so I believe) so I broke those lines into several variables. I tried to do PRINT but the window pane has allows 4000. The workaround is to print those broken lines one variable at a time but that can get tedious for manual labor so I opted to look into writing it into a txt file one variable at a time.</p>
  
  <p>I looked into BCP via xpcommandshell and it looked promising. Issue was that I could get this line to work on the command prompt yet that exact same line doesn't work on TSQL query:</p>
  
  <pre><code>declare @cmd varchar(8000)
  select @cmd = 'bcp Client_DB "Select name from dbo.t_TagBuild_set" queryout "Desktop\LAMB\dummy.txt" -c -t, -T'
  exec master..xp_cmdshell @cmd
  </code></pre>
  
  <p><code>bcp Client_DB "Select name from dbo.t_TagBuild_set" queryout "Desktop\LAMB\dummy.txt" -c -t, -T</code> works perfectly fine on command prompt</p>
  
  <p>despite this slight progress, my manager didn't want to go that route. So instead I opted for sp_OACreate and sp_OAMethod after enabling sp_configure via executing this line on SQL:
  sp_configure 'Ole Automation Procedures', 1</p>
  
  <p>One of the very first lines on this route is this:</p>
  
  <pre><code>EXECUTE @hr = sp_OACreate  'Scripting.FileSystemObject' , @objFileSystem OUT
  </code></pre>
  
  <p>@hr gives a 0 so that's good but @objFileSystem yields 16711422 and @hr eventually becomes -2146828218 which i believe is permissions.</p>
  
  <p>i really am at a loss on finding something simple to do yet i've made this increasingly difficult on myself to find something concrete just to write to a couple variables in a row before adding a new line and repeat the process.</p>
  
  <p>If anyone can expertly help me figure out BCP or sp_OACreate then I'd be very appreciative cause the net as is barely helps (and this is after I spent a lot of time looking through Microsofts own site for an answer)</p>