Documents in Facility Admin

[Watty08]

Hello All,

 

Sorry if this has been covered, I have had a quick search around and can't seem to find a thread on this.

We have moved from Facility to SIMS but I have migrated Facility into a dark cupboard for historical data.

All seems fine after the migration apart from the documents. I have installed the document server add on. Moved the documents folder to the new server and pointed to settings to the new folders. I have checked the shares and windows permissions and all seems ok.

When looking at the documents they are not picking their document names up and when you try and open a file it says: That Document item no longer exists.

Any help on this would massively appreciated as I've had to resort to "just for men" to hide the grey.

[themightymrp]

We use Facility here but I have just spotted this old comment which may be useful??

 

http://www.edugeek.net/forums/mis-systems/12833-sims-document-server-no-documents-found.html#post129702

[6Foot2]

We have seen this problem in Facility from time-to-time: Particularly so after an upgrade. I have asked the Helpdesk in the past and they have logged in and applied our licences which kicked things into shape without a server restart.

 

Happy days!

[michael2k6]

Just to clear this up; the location of the documents is stored in the table, so if you move the document, they will no longer be found; if you update the settings any new documents will be stored in the new location. In a live environment, it would be normal for the support team to assist you in running a script to update the old location to the new location.

Whilst this may not be the easiest and immediate solution you're looking for, hopefully i've pointed you in the right direction.

Do check your license key; if you can see the document settings in facility, you've got it licensed. Check the key in eportal too if it's only an issue with eportal.

 

Mic

[themightymrp]

I think from the OP he has moved AWAY from Facility/ePortal and gone to SIMS. @Watty08 - have you tried the info on the link I posted?

[Watty08]

Thanks for the help everyone. I started looking Mighty but its not documents in sims that needs repairing, it is in facility. I've took Facility off of our MIS server to free up resources for SIMS (we're a Dell managed school and are limited to what we can install). Our exams officer and some admin staff often pop on to get old data for references and suck like.

Luckily I took a vmware backup before I migrated Facility. The migrated instance still works ok but I don't want to stand that up for staff to use. I'm wondering whether to take a backup and restore it on the migrated Facility install.

[themightymrp]

Ahhhhh, I thought you'd said you'd migrated from Facility to SIMS so I assumed you were trying to get the old Documents from Facility to work in SIMS!

 

If I remember correctly, don't you have to change the user account that one of the Facility services uses for startup in order for the Documents add-on to work?

[Watty08]

Ohhh I'm not sure, I didn't see that in the migration document. I'll have a quick look though thanks.

[Watty08]

Just changed the "SQL Server Integration Service" and "SQL Full text filter" with a domain account for its login rather than built in service.

Still no joy.

[themightymrp]

Is it within Facility_SPM or the ePortal application that you can't get to the Documents? (or both). Just checked ours out and it is the Facility Data Server service which logs on using a domain account.

 

In Facility, what do your paths look like for the document storage? I assume you set it under the Data menu -> document storage -> settings ?

[Watty08]

Just in Facility Mighty. Not installing eportal as off site access isn't needed any more. Yeah under database/document storage/ settings.

The path for the folders are set to: \\servername\facility$\docstore\doc (and temp for the temporary one).

I wondered if it was because of an hidden share but it worked ok on the last server.

Thanks for the help on this, Its gratefully appreciated.

[themightymrp]

No worries mate.

 

Its nothing to do with a hidden share. Our settings are set to

 

\\facility\DOCTSTORE$\DATASTORE

\\facility\DOCSTORE$\TEMP

 

Out of interest, could the folder path name be case sensitive? Our has the capitals where shown above.... I've seen stranger glitches. If you browse to the paths in your system I assume you do see the documents?

[SkywOrca]

I think you'll find that @michael2k6 is correct on this one. The document storage in Facility stores the full virtual path of the file inside the database, so when you move the location of the document storage around the system can lose track of where it thinks those files should now be. If you take a look in the DOCCONTENT table you might see that the FileRef field is still referencing an old location for the documents from before your migration. If this does not match up with the current location of the document storage folders then this is likely the cause of the issue.

 

I guess there are two possible solutions, neither of which are all that great. You could either relocate the document storage folders back to their original location on the network so that Facility understands where those file references point to. Alternatively, if you need to keep the document storage on your archive server, you would likely need to update the file refs to point to their new location. This in itself could be fairly involved and potentially dangerous as you'd be editing the SQL tables directly. If you need to do this there are some pointers I could give that might help you, but you'd be in an "at your own risk" situation, so please bear that in mind.

[themightymrp]

I'm trying to remember how we did it once, we migrated our facility install to a new server about 3 years ago and obviously had to change all the paths then. I don't remember having any difficulties or scripts to run though??

[SkywOrca]

Hmm, perhaps I'm assuming the worst in terms of how intelligent the system is with rebuilding the documents from the paths stored in that table then? In truth, I do think it's somewhat ridiculous that the system even bothers to store the full virtual paths in the table since it should only need the file name and the config settings telling it where the temp storage is to fully recreate the original file from the repository. Perhaps it is able to pick out just the filename section and use that with the settings, but the OP's issue would certainly indicate this may not be the case?

[themightymrp]

OK, the official answer from Advanced Learning (I just asked them). Follow points 2.4 and 2.5 of the attached Document Storage manual Edited May 22, 2014 by elsiegee40
Doc removed as only avaioable to vendor's support portal customers

[michael2k6]

Sorry guys; again, to clear this up:

It's not to do with the server settings, or the eportal login permissions.

The full file name and path is stored in the database, and the file is no longer in that location.

You'll need to run TSQL against the table containing the fileref (Skyworca has alluded to), with an update statement. If you use a like statement correctly, you can replace just the start of the row so that the file name etc remains in place. Obviously this is at your own risk, as Skyworca suggested.

This is something which is done as part of the migration consultancy offering where by an establishment would purchase a consultancy service for us to move all of the software, documents and database.

 

I'm not an amateur on this one folks; I'm in year 9 of Facility / ePortal experience now having been through the service desk, consultancy, training, etc. A good couple of years of that was spent responsible for installations, migrations and hardware specifications.

You won't find a way to make it work without updating the fileref in that table, or moving the documents back.

Or just as a wildcard, if you set up a DFS path to point to the old path and redirect to the new one? I've not used DFS in years so I'm not sure if that's even possible, but it's a thought.

 

I hope this helps.

Mic

[themightymrp]

Looks like I made an oopsy above.

 

Michael is most likely right. I asked Advance Learning just yesterday on how I would go about moving the Doc Store to a different server and their answer was to point me to the (now removed) Doc Store manual. All this says is to create your share with the appropriate permissions and point Facility to it under the Data --> Documents --> Settings page.

 

I guess it's more complex!

[Watty08]

Very strange, migrated the documents back to the old server and it still didn't work. Which probably points to an installation error on my behalf. Cant seem to find a fresh install guide anywhere. I have got upgrade guides and a stand alone installation guide but no fresh install guide.

[themightymrp]

Did you install the Document Storage component into the same folder where Facility was installed?

[themightymrp]

Do you still have a login for Advanced Learning's website? You can grab the guides from there.

[SkywOrca]

Very strange, migrated the documents back to the old server and it still didn't work. Which probably points to an installation error on my behalf. Cant seem to find a fresh install guide anywhere. I have got upgrade guides and a stand alone installation guide but no fresh install guide.

 

You need to check the FileRef values in the DOCCONTENT table and make sure that the path you've restore the document storage files to match those. You will also then need to reset the configuration settings for document storage in Admin (and ePortal, if you're still using that) to also point at this new location. That should fix the problem, but as always there may be some individual wobbles with the way your setup works. Note also that if you had previously moved your document storage prior to moving systems (server upgrade, for example) then you might have documents listed against multiple locations in your DOCCONTENT anyway, which you would need to script to correct.

[SkywOrca]

If you need to change your FileRefs to a new location, the following may help. Please note that this information is given as-is and it is your responsibility to ensure you have a full backup of your data first. I would also recommend testing this on a dummy database first to ensure it doesn't do anything destructive on your live data.

 

First, double check if you have any crazy long FileRefs, if you do, DO NOT PROCEED, bad things could happen:

SELECT * FROM DOCCONTENT WHERE RefLine <> 1

If you get any results from this statement you'll need some more in depth code to fix things.

 

Assuming you have on-liner FileRefs run the following statement to ensure that the references look like a normal file ref. There are some entries in the DOCCONTENT table that don't really point to a physical file, but we need to ensure that all the others follow a specific format:

SELECT * FROM DOCCONTENT WHERE FileRef NOT LIKE '%\Temp\%'

If you ONLY see FileRefs here that relate to a .FacRep file, then this is all good and we can proceed again, otherwise again you will need a more in depth script.

 

OK, so we should now be in a position to assume that your references are all playing ball, now test some code to create some new FileRefs that point to your new document storage location:

SELECT 'C:\DocumentStorage' + SUBSTRING(FileRef, CHARINDEX('\Temp\', FileRef), 250) NewFileRef, FileRef FROM DOCCONTENT WHERE FileRef LIKE '%\Temp\%'

You should see the NewFileRef creates a new file path that looks like where your Temp folder is in the new document storage location and the rest of the file location is correct for your new document storage. Obviously, you'll need to amend the 'C:\DocumentStorage' bit to the relevant folder path.

 

Now, before we write this back to your data, lets make sure we're not creating new crazy long FileRefs that will fail to work:

SELECT 'C:\DocumentStorage' + SUBSTRING(FileRef, CHARINDEX('\Temp\', FileRef), 250) NewFileRef, LEN('C:\DocumentStorage' + SUBSTRING(FileRef, CHARINDEX('\Temp\', FileRef), 250)) Length FROM DOCCONTENT WHERE FileRef LIKE '%\Temp\%' ORDER BY LEN('C:\DocumentStorage' + SUBSTRING(FileRef, CHARINDEX('\Temp\', FileRef), 250)) DESC

If you see any Lengths greater than 249 STOP, this won't work with your data.

 

So, finally happy that your document storage can safely move to your new file location, we can go ahead and swap in the new folder structure:

UPDATE DOCCONTENT SET FileRef = 'C:\DocumentStorage' + SUBSTRING(FileRef, CHARINDEX('\Temp\', FileRef), 250) WHERE FileRef LIKE '%\Temp\%'

Refresh your connections (stop and restart ePortal) and you should now have access to your documents again. Ensure all the configurations for document storage are also pointing to your new location also.

 

As I said at the start, this information is given as-is, you need to ensure that you fully test this before deplying in a live environment. Always, ALWAYS, ensure you have full SQL backups before you run potentially destructive update statement in SQL, and be prepared to have to restore that back in if you find that there are issues when you test the data afterwards.

[themightymrp]

WOW! Is all I'll say

[SkywOrca]

I have a paranoid approach to mucking about in SQL.. I once very nearly got burnt by a bad script, so I learnt my lesson well!