Exporting Attachments

ChrisG (Flexera Software)

6 years ago

The following page describes some ways to get binary blob data out of a SQL Server database and may be helpful: SAVING AND EXTRACTING BLOB DATA – BASIC EXAMPLES

If you're familiar with PowerShell, writing a PowerShell script to extract and save the data you want (like the last option described on the above page) would be the most flexible approach of the options listed. I can't find a good example of a PowerShell script to do that for the document data in a FlexNet Manager Suite database though, so that remains an exercise for the reader!

However I did find a script that somebody has written using the "OLE object creation via SSMS" approach described in this article. This approach will only be helpful if you have full admin access to the SQL Server instance and the server it is running on, but if that limitation is not a blocker in your situation then this may be a viable option. Here is the script:

DECLARE CURSOR_Documents CURSOR FOR (SELECT DocumentID FROM dbo.Document_MT WHERE [DocumentTypeID] = 1 /* uploaded document */)DECLARE @DocumentID INTOPEN CURSOR_DocumentsFETCH NEXT FROM CURSOR_Documents INTO @DocumentIDWHILE (@@FETCH_STATUS <> -1)BEGIN  DECLARE @ImageData varbinary(max)  SELECT @ImageData = (SELECT convert(varbinary(max), [DocumentFile], 1) FROM dbo.Document_MT WHERE [DocumentID] = @DocumentID)  DECLARE @Path nvarchar(1024)  SELECT @Path = 'C:\Temp'  DECLARE @Filename NVARCHAR(1024)  SELECT @Filename = (SELECT [DocumentName] FROM dbo.Document_MT WHERE [DocumentID] = @DocumentID)  DECLARE @FullPathToOutputFile NVARCHAR(2048)  SELECT @FullPathToOutputFile = @Path + '\[' + CONVERT(varchar(5), @DocumentID) + ']' + @Filename    PRINT 'Document: ' + @FullPathToOutputFile    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, 'Write', NULL, @ImageData  EXEC sp_OAMethod @ObjectToken, 'SaveToFile', NULL, @FullPathToOutputFile, 2  EXEC sp_OAMethod @ObjectToken, 'Close'  EXEC sp_OADestroy @ObjectToken  FETCH NEXT FROM CURSOR_Documents INTO @DocumentIDENDCLOSE CURSOR_DocumentsDEALLOCATE CURSOR_Documents

Expand Post

ChrisG (Flexera Software)
6 years ago
The following page describes some ways to get binary blob data out of a SQL Server database and may be helpful: SAVING AND EXTRACTING BLOB DATA – BASIC EXAMPLES
If you're familiar with PowerShell, writing a PowerShell script to extract and save the data you want (like the last option described on the above page) would be the most flexible approach of the options listed. I can't find a good example of a PowerShell script to do that for the document data in a FlexNet Manager Suite database though, so that remains an exercise for the reader!
However I did find a script that somebody has written using the "OLE object creation via SSMS" approach described in this article. This approach will only be helpful if you have full admin access to the SQL Server instance and the server it is running on, but if that limitation is not a blocker in your situation then this may be a viable option. Here is the script:
DECLARE CURSOR_Documents CURSOR FOR (SELECT DocumentID FROM dbo.Document_MT WHERE [DocumentTypeID] = 1 /* uploaded document */)DECLARE @DocumentID INTOPEN CURSOR_DocumentsFETCH NEXT FROM CURSOR_Documents INTO @DocumentIDWHILE (@@FETCH_STATUS <> -1)BEGIN DECLARE @ImageData varbinary(max) SELECT @ImageData = (SELECT convert(varbinary(max), [DocumentFile], 1) FROM dbo.Document_MT WHERE [DocumentID] = @DocumentID) DECLARE @Path nvarchar(1024) SELECT @Path = 'C:\Temp' DECLARE @Filename NVARCHAR(1024) SELECT @Filename = (SELECT [DocumentName] FROM dbo.Document_MT WHERE [DocumentID] = @DocumentID) DECLARE @FullPathToOutputFile NVARCHAR(2048) SELECT @FullPathToOutputFile = @Path + '\[' + CONVERT(varchar(5), @DocumentID) + ']' + @Filename PRINT 'Document: ' + @FullPathToOutputFile 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, 'Write', NULL, @ImageData EXEC sp_OAMethod @ObjectToken, 'SaveToFile', NULL, @FullPathToOutputFile, 2 EXEC sp_OAMethod @ObjectToken, 'Close' EXEC sp_OADestroy @ObjectToken FETCH NEXT FROM CURSOR_Documents INTO @DocumentIDENDCLOSE CURSOR_DocumentsDEALLOCATE CURSOR_Documents

Expand Post
- bheadley (Flexera Software)
  6 years ago
  One other way that I've used is LINQPad: https://www.linqpad.net/ . You can use a simple C script to iterate through a table writing out each binary set of data to a file. Here is an example of doing this: https://richardborges.net/download-binary-data-to-file/ .
  Here are a couple of references for LINQ: https://docs.microsoft.com/en-us/dotnet/csharp/programming-guide/concepts/linq/ and https://docs.microsoft.com/en-us/dotnet/csharp/programming-guide/concepts/linq/basic-linq-query-operations .
  - Bill
  Expand Post

Related Product Forums