title: Can I stream a file to a SQL Server stored procedure?
body: <p>I have a file on a remote machine that I want to 'copy' to the SQL Server host machine to a specific area. I'm wondering what my options are for doing this through a SQL Server stored procedure, and more specifically I want to stream the file contents, not reading the entire contents into memory first.</p>
  
  <p>I'm basically looking for a streamed version of this:</p>
  
  <pre><code>CREATE PROCEDURE [dbo].[SaveFile]
      (@filename nvarchar(255), @contents nvarchar(max))
  AS
  BEGIN
      DECLARE @ObjectToken INT
      EXEC sp_OACreate 'ADODB.Stream', @ObjectToken OUTPUT
      EXEC sp_OASetProperty @ObjectToken, 'Type', 1
      EXEC sp_OAMethod @ObjectToken, 'Open'
      EXEC sp_OAMethod @ObjectToken, 'WriteText', NULL, @contents
      EXEC sp_OAMethod @ObjectToken, 'SaveToFile', NULL, 'C:\\Destination\\' + @filename, 2
      EXEC sp_OAMethod @ObjectToken, 'Close'
      EXEC sp_OADestroy @ObjectToken
  END
  </code></pre>
  
  <ol>
  <li>Can I have a <code>filestream</code> or something equivalent as a stored procedure input parameter?</li>
  <li>Have a CLR stored procedure handle the stream?</li>
  <li>Perhaps one procedure could open the stream and return the <code>@ObjectToken</code>, another could <code>WriteText</code> a chunk from the stream, and a third procedure could <code>SaveToFile</code> and <code>Close</code> it, but this seems like <code>!a.GoodIdea</code> and I'd have to ensure closure and destruction through some sort of timeout.</li>
  <li>Other options?</li>
  </ol>
  
  <p>It's being sourced from a C# console application and I'd prefer to stream these large files instead of something like <code>File.ReadAllText()</code>, loading a multi-gigabyte string into a variable, and then calling a stored procedure with the entire contents of the file.</p>