OceanBird
Level 2

Exporting Attachments

Hello all,

I am trying to export all attachments from Flexera for import into another platform.

I am currently using Microsoft SQL Server Management Studio to query the Document table so export where ContractID IN NOT NULL among other tables. I'm running into an issue where the hexadecimal values in the DocumentFile field are getting truncated, or the limit is causing the attachments to become corrupted upon import into the other platform. 

Does anybody know of a viable way to export attachment data from Flex in a way that will preserve the attachment if it is large?

Does the data get chunked out into manageable pieces over multiple records? I have heard of that happening on other platforms, and if it does, does anyone know how that chunking works or where to find the data?

Has anybody done this in a way that worked but maybe with another tool?

Please let me know asap.

If this question belongs in another forum please let me know as well. Not a Flexera expert by any means.

Many thanks

2 Replies
ChrisG
Community Manager Community Manager
Community Manager

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 INT

OPEN CURSOR_Documents

FETCH NEXT FROM CURSOR_Documents INTO @DocumentID
WHILE (@@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 @DocumentID
END

CLOSE CURSOR_Documents
DEALLOCATE CURSOR_Documents

 

(Did my reply solve the question? Click "ACCEPT AS SOLUTION" to help others find answers faster. Liked something? Click "KUDO". Anything expressed here is my own view and not necessarily that of my employer, Flexera.)

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-oper....

- Bill