MSSqlServer TSQL save varbinary to file

mssqlserver-tsql-save-varbinary-to-file.sql

DECLARE @local_target_path varchar(50) = 'c:\must\be\local\path'
	, @adodb_file_stream int
	, @create_instance_error int
	, @error int
	, @i bigint
	, @data varbinary(max)
	, @error_count int
	, @error_description varchar(255)
	, @error_source varchar(255)
	, @file_path varchar(max)
	, @open_error int
	, @save_hresult int
	, @write_error int
	;

Use MyDb;
SET @error_count = 0;
declare docs_cursor cursor local forward_only read_only for 
	select doc_key from mydb.schema.my_documents_table;
open docs_cursor;
fetch next from docs_cursor into @i;
while @@FETCH_STATUS = 0 AND @error_count < 50 begin

		SELECT
			 @data = [Document],
			 @file_path = @local_target_path + '\' + replace(replace(replace(replace(rtrim([Doc_FileName]), ' ', '_'), '/', '-'), '\', '-'), ':', '')
		FROM mydbo.schema.my_documents_table WHERE [Doc_Num] = @i;
		print 'About to store document ' + cast(@i as varchar(max)) + ' to file ' + @file_path + '.';

		-- create a stream instance. 
		-- see: https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-oacreate-transact-sql?view=sql-server-ver16#examples
		EXEC @create_instance_error = sp_OACreate 'ADODB.Stream', @adodb_file_stream OUTPUT;

		if @create_instance_error = 0 begin

			-- tell the stream to expect binary data. The default is 2, text.
			-- see: https://learn.microsoft.com/en-us/sql/ado/reference/ado-api/type-property-ado-stream?view=sql-server-ver16
			EXEC sp_OASetProperty @adodb_file_stream, 'Type', 1; 

			-- open the stream with read-write connection mode. The default mode is 0: unknown.
			-- see: https://learn.microsoft.com/en-us/sql/ado/reference/ado-api/open-method-ado-stream?view=sql-server-ver16
			-- we could open with username and pwd.
			EXEC @open_error = sp_OAMethod @adodb_file_stream, 'Open', NULL --, 2, -1;
			if @open_error <> 0 begin
				SET @error_count += 1;
				EXEC @error = sp_OAGetErrorInfo @adodb_file_stream, @error_source OUT, @error_description OUT;
				if @error = 0 begin
					print 'Error ' + cast(@open_error as varchar(max)) + ' in ' + @error_source + ' while opening a file for record ' + cast(@i as varchar(max)) + ' at path ' + @file_path + ': ' + @error_description;
				end else begin
					print 'Unknown error ' + cast(@open_error as varchar(max)) + ' encountered while opening a file for record ' + cast(@i as varchar(max)) + ' at path ' + @file_path + '.';
				end;

			end else begin

				-- write data to stream
				-- see: https://learn.microsoft.com/en-us/sql/ado/reference/ado-api/write-method?view=sql-server-ver16
				EXEC @write_error = sp_OAMethod @adodb_file_stream, 'Write', NULL, @data;
				if @write_error <> 0 begin
					SET @error_count += 1;
					EXEC @error = sp_OAGetErrorInfo @adodb_file_stream, @error_source OUT, @error_description OUT;
					if @error = 0 begin
						print 'Error ' + cast(@write_error as varchar(max)) + ' in ' + @error_source + ' while writing to file for record ' + cast(@i as varchar(max)) + ' at path ' + @file_path + ': ' + @error_description;
					end else begin
						print 'Unknown error ' + cast(@write_error as varchar(max)) + ' encountered while writing to file for record ' + cast(@i as varchar(max)) + ' at path ' + @file_path + '.';
					end;

				end else begin

					-- save stream to file with save option create/overwrite.
					-- https://learn.microsoft.com/en-us/sql/ado/reference/ado-api/savetofile-method?view=sql-server-ver16
					EXEC @save_hresult = sp_OAMethod @adodb_file_stream, 'SaveToFile', NULL, @file_path, 2;
					if @save_hresult = 0 begin
						print 'Document ' + cast(@i as varchar(max)) + ' Generated at - '+  @file_path;
					end else begin
						SET @error_count += 1;
						EXEC @error = sp_OAGetErrorInfo @adodb_file_stream, @error_source OUT, @error_description OUT;
						if @error = 0 begin
							print 'Error ' + cast(@save_hresult as varchar(max)) + ' in ' + @error_source + ' while saving document ' + cast(@i as varchar(max)) + ' to file ' + @file_path + ': ' + @error_description;
						end else begin
							print 'Unknown error ' + cast(@save_hresult as varchar(max)) + ' encountered while saving document ' + cast(@i as varchar(max)) + ' to file ' + @file_path + '.';
						end;

					end;
				end;
				-- close the stream
				EXEC sp_OAMethod @adodb_file_stream, 'Close';
			end;

			-- remove the stream instance from memory
			EXEC sp_OADestroy @adodb_file_stream;

		end else begin
			SET @error_count += 1;
			EXEC @error = sp_OAGetErrorInfo @adodb_file_stream, @error_source OUT, @error_description OUT;
			if @error = 0 begin
				print 'Error ' + cast(@create_instance_error as varchar(max)) + ' in ' + @error_source + ' while creating an ADODB.STREAM instance to store document ' + cast(@i as varchar(max)) + ' to file ' + @file_path + ': ' + @error_description;
			end else begin
				print 'Unknown error encountered while attempting to create an ADODB.STREAM instance to store document ' + cast(@i as varchar(max)) + ' to file ' + @file_path + '.';
			end;
		end;

		--Reset the variables for next use
		SELECT @data = NULL 
			, @adodb_file_stream = NULL
			, @file_path = NULL
			, @error = 0;

	fetch next from docs_cursor into @i;
END;
close docs_cursor;
deallocate docs_cursor;

if @error_count >= 50 begin
	print cast(@error_count as varchar(max)) + ' Errors encountered. Execution automatically stopped.';
end else begin
	print cast(@error_count as varchar(max)) + ' Errors encountered. Execution will stop automatically after 50 errors.'
end;

Update: now using the documents table's actual row key instead of a random counter that might not exist as that key.