Skip to content

Instantly share code, notes, and snippets.

@aev-mambro2
Last active April 19, 2024 14:19
Show Gist options
  • Save aev-mambro2/ca8fb742b3544c1cef33bfbb8f70fbb3 to your computer and use it in GitHub Desktop.
Save aev-mambro2/ca8fb742b3544c1cef33bfbb8f70fbb3 to your computer and use it in GitHub Desktop.
MSSqlServer TSQL save varbinary to file
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;
@aev-mambro2
Copy link
Author

One of the applications we use is built by a third party. We have access to the data store, but are not supposed to change its structure or processes. One of its many functions is the storage of documents along with some meta data. We needed to export those documents for viewing elsewhere. The above T-SQL procedure allows that. It works on Microsoft SQL Server with a data base version 12 (2014).

The main issue is writing to mapped drives: the procedure runner is a local service account or a network service account on the SQL Server computer, and that means that it does see the same mapped drives as you do, with your logged-in account. Thus the drive to which you have the procedure export the documents must be attached to the computer. They must show in the computer's disk management (inside the computer management panel). Otherwise you have to find a way to map the drive for the procedure runner ahead of time.

Other issues:

  • Since we are calling OLE objects, it is very difficult to gain visibility into processing and errors. This leads to paranoid error handling and a cap on the amount of errors that may occur before calling it quits. Without the cap we ran out of memory and forcing SQL Management Studio to halt and reboot.
  • Retrieving the error information should be done carefully, by capturing it into variables and then displaying those. If, instead, we let the error retrieval write to console / screen immediately, we ran out of memory.
  • Despite the print statements, printing doesn't happen until the procedure is done executing. This causes a build-up of memory use on the server but also delays visibility on any errors that might be occurring. Stopping the loop at a threshold of error count increases that visibility, but it would be better to have immediate output that doesn't cause memory issues.

Make software better!

@aev-mambro2
Copy link
Author

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment