sims SQL access

[browolf]

random sims thoughts that might solve some issues #2

 

since the sims data is in its own ms-sql database, it is possible to query the data outside the sims applications. for instance a dos/wsh script running on the sims server?

[TimH]

random sims thoughts that might solve some issues #2

 

since the sims data is in its own ms-sql database, it is possible to query the data outside the sims applications. for instance a dos/wsh script running on the sims server?

 

Technically possible once you've worked out the table structure but the man from SIMS, he say no !

 

I had an issue that I couldn't solve via reports/commandreporter/scripting but could have solved from direct database access, checked with SIMS and it is not "allowed".

 

I can understand them worrying about people writing data back to the database but allowing a read only user would be so handy - maybe even include a special database user in the standard install for such purposes (most people would never be aware of it but it would be there for those who need it).

 

As far as programmatic input, as they certainly don't want you/us trying that, what about an extended command reporter with import features ?

 

(We only started with SIMS this year and had to do a crazy amount of manual data entry for stuff that wasn't/couldn't be in a ctf.)

[LosOjos]

what about an extended command reporter with import features ?

 

I still don't understand why this functionality isn't available! It would make life SO much easier for those with the technical knowledge to use it, and as it's command line, those without the knowledge would be scared to look at it, let alone use it!

 

I'm sure it wouldn't be difficult to do, Capita, pull your socks up! Please

[Mcshammer_dj]

I would have thought that a query the looks at the SIMS data is not banned at all. Last time I spoke to Capita they confirmed that lookingh at the data is not banned but adding to or making changes is. The problem is that if you don't go via the buisness objects then you will have to identify the table structure first, and Capita do have a tendancy to change the structure

[dhicks]

As far as programmatic input, as they certainly don't want you/us trying that, what about an extended command reporter with import features?

 

Cheat: stick an old Windows XP workstations in a quiet corner somewhere, or as a VM, and use AutoIT to enter data into SIMS.

 

--

David Hicks

[rh91uk]

We have been told (and I think this is ridic .. personally) is that our support contract with our LEA (very minimal!) would be void if we even touched the SIMS sql db.

 

What I want to do is run a script which adds new users from the SIMS db. So, what I plan to do is on another ms-sql box of ours is mount the database on another server at whatever AM in the morning, run the script I want to run then, and dismount it. After the backup of the SIMS server has taken place, without a doubt!

[AIT]

yes totally possible. night mare to work out the table structure. we have built an application that generates graphs from sims data. but took a very long time to work out the very complicated table structure.

[Mcshammer_dj]

We have been told (and I think this is ridic .. personally) is that our support contract with our LEA (very minimal!) would be void if we even touched the SIMS sql db.

 

What I want to do is run a script which adds new users from the SIMS db. So, what I plan to do is on another ms-sql box of ours is mount the database on another server at whatever AM in the morning, run the script I want to run then, and dismount it. After the backup of the SIMS server has taken place, without a doubt!

 

when you say Add new users from SIMS db do you mean that you want to extre=act a current list of children and then use AD tools to import them and create logins?

[AIT]

personaly i would add a new user on the sql level rather than through sims. your support wouldnt be void long as you are only reading data. it gets very complicated once you try and write data back to sims. if you wished to do this you would have to purchase the sims developer license. (in doing this you also get the table structure) however its very expensive.

[LosOjos]

Hadn't there ought to be a sticky on this subject? It seems to crop up every week (in fact, it was how I found this site in the first place)

[localzuk]

The issue has been answered by Capita on here IIRC. It stands like this - the data structure is likely to change at different updates, so querying directly is ill advised. Also, if you are doing any direct access stuff, and something goes wrong, Capita can turn round to you and hand you a bill for the repairs of the database.

 

So, it is a pain but the options to get data from SIMS are:

 

Command Reporter - limited

Business Objects - You have to sign an NDA which is *very* restrictive.

Get a developer license - I have no information about this...

[vikpaw]

We have been told (and I think this is ridic .. personally) is that our support contract with our LEA (very minimal!) would be void if we even touched the SIMS sql db.

 

What I want to do is run a script which adds new users from the SIMS db. So, what I plan to do is on another ms-sql box of ours is mount the database on another server at whatever AM in the morning, run the script I want to run then, and dismount it. After the backup of the SIMS server has taken place, without a doubt!

 

If you want to add new users into AD from the SIMS db i.e. when new students/staff etc join you can just use AD Provisioning. Now it is available stand-alone i believe. It's from Capita, so effectively future proof and it's supported. I'm sure the cost is high, but what isn't.

 

Alternatively, there are many other companies, any offering VLE with Capita link, that have their own versions of these tools. They have the deals with Capita and can take the risk.

 

Saves you doing your own thing and taking risks with your db.

 

...

So, it is a pain but the options to get data from SIMS are:

 

Command Reporter - limited

Business Objects - You have to sign an NDA which is *very* restrictive.

Get a developer license - I have no information about this...

 

Or, option #4 (The Wild Card).

- Set up virtual machine with dummy SIMS server. Attach live SIMS db backup to dummy machine and do whatever you like to it with SQL or other scripts.

 

That's what i do, to see what's around, it's hard work finding the right tables etc.

I've now got a few updates that i'd like to have done to the database so i'm going to submit sql scripts to support for approval to run on my system. Presumably they will check, test and approve and return as a site specific patch.

[rh91uk]

 

 

 

Or, option #4 (The Wild Card).

- Set up virtual machine with dummy SIMS server. Attach live SIMS db backup to dummy machine and do whatever you like to it with SQL or other scripts.

 

That's what i do, to see what's around, it's hard work finding the right tables etc.

I've now got a few updates that i'd like to have done to the database so i'm going to submit sql scripts to support for approval to run on my system. Presumably they will check, test and approve and return as a site specific patch.

 

 

That's what I aim to do - then surely it is off the live SIMS setup - hence if it goes wrong there's nothing to worry about

[browolf]

That's what I aim to do - then surely it is off the live SIMS setup - hence if it goes wrong there's nothing to worry about

 

I suggested something similar some years ago to our sims managment. Since sql can be clustered it ought to be possible to have a 2nd sims database with one way data transfer. ie it can receive updates but can't send changes back to the original box (not that I wanted to make any). Needless to say our sims maintenance company didn't like the idea and our own management couldn't see any immediate benefit.

[vikpaw]

That's what I aim to do - then surely it is off the live SIMS setup - hence if it goes wrong there's nothing to worry about

My point exactly!

 

I suggested something similar some years ago to our sims managment. Since sql can be clustered it ought to be possible to have a 2nd sims database with one way data transfer. ie it can receive updates but can't send changes back to the original box (not that I wanted to make any). Needless to say our sims maintenance company didn't like the idea and our own management couldn't see any immediate benefit.

I didn't ask for approval, nor have anyway near that kind of complicated setup. I just ran a vm on my workstation and restored a backup as needed. Now i'm moving it to some spare server space on a blade which is so much quicker.