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