We have invoice, Mail communications and PO are attached to snow license manager, I want to download all attached documents for all licenses, let me know where the files are storing?
All documents uploaded are stored in a table called tblDocuments, and they are not stored in clear text.
Unfortunately there is no easy way to download all attachments for all licenses that I am aware of.
Thank you Martin
the following script will download all License documents to c:\temp and Name them <LicenseID>_<Title in Snow Licnese Manager>_>Original filename>
declare @parentid int
declare @init int
declare @title varchar(100)
declare @FileNameD varchar(255)
declare @FileName varchar(max)
declare @DocumentID int
declare @data varbinary(max)
declare @filepath varchar(max)
declare licdocs cursor for
select FileName,ParentID,DocumentID,Title,Content from tblDocuments where DocumentType=1-- DocumentType=1 = only License documents
Fetch next from licdocs into @FileNameD,@parentid,@DocumentID,@title,@data
set @FileName=convert(varchar(max), @parentid) + '_' + @title + '_' + @FileNameD
set @filepath = N'c:\temp\' + @FileName
EXEC sp_OACreate 'ADODB.Stream', @init OUTPUT; -- ADODB instace created
EXEC sp_OASetProperty @init, 'Type', 1;
EXEC sp_OAMethod @init, 'Open'; -- Open ADODB stream
EXEC sp_OAMethod @init, 'Write', NULL, @data; -- inster binary data of document to ADODB stream
EXEC sp_OAMethod @init, 'SaveToFile', NULL, @filepath, 2; -- saving content of ADODB stream to file
EXEC sp_OAMethod @init, 'Close'; -- Close ADODB stream
EXEC sp_OADestroy @init; -- Deallocate ADODB stream
If you face the error message:
Msg 15281, Level 16, State 1, Procedure sp_OACreate, Line 1 [Batch Start Line 2]
SQL Server blocked access to procedure 'sys.sp_OACreate' of component 'Ole Automation Procedures' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ole Automation Procedures' by using sp_configure. For more information about enabling 'Ole Automation Procedures', search for 'Ole Automation Procedures' in SQL Server Books Online.
Your DB Administrator can enable the funtion by running
EXEC master.dbo.sp_configure 'Ole Automation Procedures', 1;
Hi Michael! This is really useful for when migrating from e.g. an SPE edition to on-premise or vice versa. Extending the question: is there a way of exporting all fields for each license?
you can export all fields of a License very easily with the Report "All licenses" in SLM. Open the Report and add all columns with th "Column selector" including the License ID. Export this Report then to XLS. Add a new cutom field for licenses in the on-prem and Name it "SPE migrated License ID". Import all fields to on-prem, map the exported License ID to the new custom field "SPE migrated License ID". This will help you later whith uploading the migrated documents.
That report does not export documents for licenses. I did an analysis of what could/couldn't be exported back in SLM 7 (2016-06-14), and some following could not be exported through the All licenses report, respectively All agreements (sorry it's in swedish):
Valbart efter utgång
Övervakning aktiverad, varningsnivå, kritisk nivå
Information: skapad, skapad av
Inköp.Inköpspriser per licens.
Inköp.Rätt till andra plattformar: ja/nej
Information.Stock-keeping unit (SKU)
Information.Uppdaterad av, uppdaterad datum
I'm guessing this could be modified to work on an SPE Platform as well by just specifying a CID in the select statement?
select FileName,ParentID,DocumentID,Title,Content from tblDocuments where CID=1 DocumentType=1
Yes, this assumption is correct. Replace the "1" after "CID=" in the select Statement with the Id of the customer in SPE.
Another thing you can do which I did not see mentioned was the following:
You can repeat this process for licenses as well.
Retrieving data ...