Can I stream a file to a SQL Server stored procedure?

Question

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.

I'm basically looking for a streamed version of this:

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

1. Can I have a filestream or something equivalent as a stored procedure input parameter?
2. Have a CLR stored procedure handle the stream?
3. Perhaps one procedure could open the stream and return the @ObjectToken, another could WriteText a chunk from the stream, and a third procedure could SaveToFile and Close it, but this seems like !a.GoodIdea and I'd have to ensure closure and destruction through some sort of timeout.
4. Other options?

It's being sourced from a C# console application and I'd prefer to stream these large files instead of something like File.ReadAllText(), loading a multi-gigabyte string into a variable, and then calling a stored procedure with the entire contents of the file.

Answer 1

I made a few attempts to get this working with OLE Automation (sp_OACreate, sp_OAMethod, etc.) and ran into a number of issues and made a CLR-based solution.

I have a C# console application, C# CLR Stored Procedures and a sample SQL Server database posted on my github account in repository named StreamFile.

The basic idea is that I call a procedure to initiate the transfer which logs a row in the database and creates the file, send n chunks to a different stored procedure that appends to the file, and then call a third stored procedure to denote the successful completion of streaming.

CREATE PROCEDURE [dbo].[StreamFile_AddBytes]
    (@fileID int, @chunk varbinary(max))
AS
BEGIN
BEGIN TRY
    DECLARE @tmppath nvarchar(max)
    SELECT @tmppath = FilePath FROM StreamedFiles WHERE StreamedFileID = @fileID

    IF (@tmppath is null)
        RAISERROR (N'Invalid StreamedFileID Provided: %d', 11, 1, @fileID);

    --C# CLR Based Stored Procedure
    EXEC dbo.AppendBytes @tmppath, @chunk

    UPDATE StreamedFiles SET Chunks = Chunks + 1, FileSize = FileSize + LEN(@chunk) WHERE StreamedFileID = @fileID

    SELECT * FROM StreamedFiles sf WHERE sf.StreamedFileID = @fileID
END TRY
BEGIN CATCH
    DECLARE @ErrorMessage NVARCHAR(4000), @ErrorSeverity INT, @ErrorState INT;
    SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE();
    RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
END CATCH

Answer 2

This is a possible solution that I will be testing:

CREATE PROCEDURE [dbo].[StreamOpen]
AS
BEGIN
    DECLARE @ObjectToken INT
    EXEC sp_OACreate 'ADODB.Stream', @ObjectToken OUTPUT
    EXEC sp_OASetProperty @ObjectToken, 'Type', 1
    EXEC sp_OAMethod @ObjectToken, 'Open'
    SELECT @ObjectToken
END

CREATE PROCEDURE [dbo].[StreamAddChunk] (@token int, @chunk nvarchar(max))
AS
BEGIN
    EXEC sp_OAMethod @token, 'WriteText', NULL, @contents
END

CREATE PROCEDURE [dbo].[StreamClose] (@token int, @filename nvarchar(260))
AS
BEGIN
    EXEC sp_OAMethod @token, 'WriteText', NULL, @contents
    EXEC sp_OAMethod @token, 'SaveToFile', NULL, 'C:\\Destination\\' + @filename, 2
    EXEC sp_OAMethod @token, 'Close'
    EXEC sp_OADestroy @token
END

I will update this answer once it is tested, and add the C# calling code.