Is it possible to link the SLM "documents" attached to each contract OR license to the corresponding license ID or Contract ID?

6 years ago

That is a nice project!!!!

The exact steps will depend on 1) if your files are nicely stored or in a mess (will impact the complexity) and 2) whether your DB has been fed with documents uploaded to the DB or just links pointing to a folder structure. We use links to save some DB space ... yeah, we have tons of licenses 😅

Option 1: The files are in folders on a server and the DB contains links. It will include some code:
- First, use PowerShell to retrieve the list of files and their links from SQL, then move each individual file in a folder named after the LicenseID. All the data you need is in the folder structure now. Some more code or Excel will be needed to get all the paths and LicenseIDs back into an importable spreadsheet. Note that it will instantaneously break all your links so you will need to either work on a mirror copy of all your files or act quickly over a weekend maybe.
Option 2: The files have been uploaded directly to the DB
- You have the option of exporting the files manually (hope there is no timeout ...) and then play with some SQL/PS to rename the file (or a folder to store them in)
- Alternatively (we have played with that option a bit with no final implememtation), you can work around a possible export timeout if you recreate the files based on the data which is stored in plain inside the corresponding DB table (get the data, open a stream, dump the data in it, save your stream as a file with the correct extension and name ... of course include the LicenseID somehow in the naming).

My recommendation would be to do like we do here: having a folder for each License named after the LicenseID ... it is a constraint and takes some time but it pays off in situations like yours😎 .

If you do that I would recommend that you have a routine in place to clean those files/folders (delete any folder belonging to a LicenseID that is no more in Snow) ... We still have to work on that last bit here 😁

You have nice evenings/weekends ahead of you! Let us know how that goes

Expand Post

SamuelBaughan
6 years ago
That is a nice project!!!!
The exact steps will depend on 1) if your files are nicely stored or in a mess (will impact the complexity) and 2) whether your DB has been fed with documents uploaded to the DB or just links pointing to a folder structure. We use links to save some DB space ... yeah, we have tons of licenses 😅
Option 1: The files are in folders on a server and the DB contains links. It will include some code:
First, use PowerShell to retrieve the list of files and their links from SQL, then move each individual file in a folder named after the LicenseID. All the data you need is in the folder structure now. Some more code or Excel will be needed to get all the paths and LicenseIDs back into an importable spreadsheet. Note that it will instantaneously break all your links so you will need to either work on a mirror copy of all your files or act quickly over a weekend maybe.
Option 2: The files have been uploaded directly to the DB
You have the option of exporting the files manually (hope there is no timeout ...) and then play with some SQL/PS to rename the file (or a folder to store them in)
Alternatively (we have played with that option a bit with no final implememtation), you can work around a possible export timeout if you recreate the files based on the data which is stored in plain inside the corresponding DB table (get the data, open a stream, dump the data in it, save your stream as a file with the correct extension and name ... of course include the LicenseID somehow in the naming).

My recommendation would be to do like we do here: having a folder for each License named after the LicenseID ... it is a constraint and takes some time but it pays off in situations like yours😎 .
If you do that I would recommend that you have a routine in place to clean those files/folders (delete any folder belonging to a LicenseID that is no more in Snow) ... We still have to work on that last bit here 😁
You have nice evenings/weekends ahead of you! Let us know how that goes
Expand Post
Oliver Berger (Flexera Software)
6 years ago
Hi @Gonçalo Morgado , i and some of my colleagues did those migrations before. Used SQL / TSQL using this approach http://www.jitendrazaa.com/blog/sql/sqlserver/export-documents-saved-as-blob-binary-from-sql-server/ . @Samuel Baughan pointed out some of the points. Tricky part is to have some kind of identifier to map the old licenseids and the new one, like a customer field or the externalID field (if not used already) what need to be filled during the license import. Than u can map the files correctly in the next steps.

Cheers Oli
Expand Post

Related Product Forums