[sims] SIMS SQL Databse Tables

[Grazza]

Hi all,

 

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?

 

Regards

 

Graham

[Rawns]

Hi all,

 

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?

 

Regards

 

Graham

 

I doubt anyone apart from Capita has as there are hundreds and hundreds of them!

 

Connect to a SIMS database via Management Studio and you can see all the tables, their names may give hints as to what they are for.

[vikpaw]

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.

[jinnantonnixx]

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?

Edited December 11, 2013 by jinnantonnixx

[vikpaw]

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.

[ICT_Pro]

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.

[LosOjos]

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:

 

http://www.edugeek.net/forums/mis-systems/66215-sims-net-reporting.html

http://www.edugeek.net/forums/mis-systems/83623-executing-commandreporter-scripts.html

http://www.edugeek.net/forums/mis-systems/119999-command-reporter-filtering-reports.html

http://www.edugeek.net/forums/mis-systems/86701-clr-params-string.html

 

(I really need to start keeping an index of the best CLR threads!)

[Grazza]

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

[matt40k]

What does it need to end up in?

[LosOjos]

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!

 

Did you try the script I posted in that thread? If you just change the file tpye on the SaveAs line to TSV, that should do it. It's an extra step you could do without I know, but it's worked well for me when I've had problems with the format of CLR's output

[Grazza]

TAB delimited it's because the text I need contains commas.

[Grazza]

Did you try the script I posted in that thread? If you just change the file tpye on the SaveAs line to TSV, that should do it. It's an extra step you could do without I know, but it's worked well for me when I've had problems with the format of CLR's output

 

Yes I did thank you, it was very easy and stripped out the "

 

I will alter the script and see what I can do, thanks

[Grazza]

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.

 

Thanks

Graham