Long shot I know, but would any of you have a list of the Tables in the SIMS SQL database and what they are used for?
It doesn't always make sense. I've knocked together a few select statements and joined tables together, but it's not easy.
Depends what you want to do. I'm sure their technical partners have a good idea for their particular area.
Don't use tables directly. We've seen recently how they can be reorganised and change with upgrades. Tables come and go, and change: If you base queries on tables expect to have the rug pulled from under your feet at any upgrade,
Views, however, are a safer bet. A view is a kind of virtual table, which appers like a table but can be made up of one table, or many tables joined together. These tend to change infrequently, so are a much more stable way of looking at data. As an added bonus, SIMS have named the views sensibly (by and large) so you can get the gist of what they represent from their names.
Be aware that there's a lot of obsolete junk in there too. Core Integrator tables, anyone?
Last edited by jinnantonnixx; 11th December 2013 at 09:53 AM.
The problem I've had with views is that you can't modify data. Obviously not an issue with SIMS cos you wouldn't dare touch it.
Capita don't allow to touch the tables and it is very hard naming structure. Every update make changes as well. I think you need developer licence to work on SIMS database directly someone mentioned in past not sure.
Depending on what it is you're trying to do, you may find that CommandReporter is enough. It's not live data I know, but it is at least a Capita approved extraction method that won't land your school a huge consultancy bill should things go wrong. I have seen you're already usin g it, but maybe some of these threads will help you get what you want from it:
executing CommandReporter from scripts
Command Reporter and Filtering Reports
CLR /Params String
(I really need to start keeping an index of the best CLR threads!)
I've had an issue with Command Line Report.
I have a report that is TAB delimited, which worked fine in SIMS itself.
When I use CommandLine Reporter it extracts it as a CSV which is useless to me!
I've sent an email to the Capita Business Manager to try and get this sorted.
All I need to do is read Students, Staff, Classes they are in and The students SEN info.
I've already managed to load the SIMS Database into Visual Studio, but the Tables are endless ! Was much easier in dBase :-)
What does it need to end up in?
Grazza (11th December 2013)
TAB delimited it's because the text I need contains commas.
Sorted, got the script saving as Tab now, so I'm going to run CommandLine Reporter and then your Script for now, until I can get some table information.
There are currently 1 users browsing this thread. (0 members and 1 guests)