+ Post New Thread
Results 1 to 15 of 15
MIS Systems Thread, sims SQL access in Technical; random sims thoughts that might solve some issues #2 since the sims data is in its own ms-sql database, it ...
  1. #1
    browolf's Avatar
    Join Date
    Jun 2005
    Location
    Mars
    Posts
    1,540
    Thank Post
    112
    Thanked 89 Times in 75 Posts
    Blog Entries
    47
    Rep Power
    41

    sims SQL access

    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?

  2. #2

    Join Date
    Feb 2006
    Location
    Dorset/Hants
    Posts
    87
    Thank Post
    2
    Thanked 13 Times in 10 Posts
    Rep Power
    21
    Quote Originally Posted by browolf View Post
    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.)

  3. #3

    LosOjos's Avatar
    Join Date
    Dec 2009
    Location
    West Midlands
    Posts
    5,771
    Thank Post
    1,511
    Thanked 1,294 Times in 884 Posts
    Rep Power
    813
    Quote Originally Posted by TimH View Post
    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

  4. #4
    Mcshammer_dj's Avatar
    Join Date
    Feb 2007
    Location
    Portsmouth
    Posts
    991
    Thank Post
    39
    Thanked 180 Times in 145 Posts
    Rep Power
    98
    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

  5. #5

    dhicks's Avatar
    Join Date
    Aug 2005
    Location
    Knightsbridge
    Posts
    5,772
    Thank Post
    1,308
    Thanked 804 Times in 698 Posts
    Rep Power
    246
    Quote Originally Posted by TimH View Post
    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

  6. #6
    rh91uk's Avatar
    Join Date
    Sep 2008
    Location
    UK
    Posts
    877
    Thank Post
    137
    Thanked 132 Times in 114 Posts
    Rep Power
    36
    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!

  7. #7
    AIT
    AIT is offline
    AIT's Avatar
    Join Date
    Dec 2009
    Location
    Nottingham
    Posts
    369
    Thank Post
    46
    Thanked 32 Times in 30 Posts
    Rep Power
    20
    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.

  8. #8
    Mcshammer_dj's Avatar
    Join Date
    Feb 2007
    Location
    Portsmouth
    Posts
    991
    Thank Post
    39
    Thanked 180 Times in 145 Posts
    Rep Power
    98
    Quote Originally Posted by rh91uk View Post
    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?

  9. #9
    AIT
    AIT is offline
    AIT's Avatar
    Join Date
    Dec 2009
    Location
    Nottingham
    Posts
    369
    Thank Post
    46
    Thanked 32 Times in 30 Posts
    Rep Power
    20
    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.

  10. #10

    LosOjos's Avatar
    Join Date
    Dec 2009
    Location
    West Midlands
    Posts
    5,771
    Thank Post
    1,511
    Thanked 1,294 Times in 884 Posts
    Rep Power
    813
    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)

  11. #11

    localzuk's Avatar
    Join Date
    Dec 2006
    Location
    Minehead
    Posts
    18,529
    Thank Post
    527
    Thanked 2,648 Times in 2,049 Posts
    Blog Entries
    24
    Rep Power
    925
    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...

  12. #12

    vikpaw's Avatar
    Join Date
    Sep 2006
    Location
    Saudi Arabia
    Posts
    5,956
    Thank Post
    775
    Thanked 1,487 Times in 1,234 Posts
    Rep Power
    367
    Quote Originally Posted by rh91uk View Post
    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.

    Quote Originally Posted by localzuk View Post
    ...
    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.

  13. #13
    rh91uk's Avatar
    Join Date
    Sep 2008
    Location
    UK
    Posts
    877
    Thank Post
    137
    Thanked 132 Times in 114 Posts
    Rep Power
    36
    Quote Originally Posted by vikpaw View Post



    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

  14. #14
    browolf's Avatar
    Join Date
    Jun 2005
    Location
    Mars
    Posts
    1,540
    Thank Post
    112
    Thanked 89 Times in 75 Posts
    Blog Entries
    47
    Rep Power
    41
    Quote Originally Posted by rh91uk View Post
    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.

  15. #15

    vikpaw's Avatar
    Join Date
    Sep 2006
    Location
    Saudi Arabia
    Posts
    5,956
    Thank Post
    775
    Thanked 1,487 Times in 1,234 Posts
    Rep Power
    367
    Quote Originally Posted by rh91uk View Post
    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!

    Quote Originally Posted by browolf View Post
    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.



SHARE:
+ Post New Thread

Similar Threads

  1. No access to any database on SQL server
    By button_ripple in forum MIS Systems
    Replies: 2
    Last Post: 1st May 2009, 09:17 AM
  2. SIMS SQL access and capita problems
    By the_travisty in forum MIS Systems
    Replies: 6
    Last Post: 29th August 2008, 12:53 PM
  3. moving form access to sql server
    By RabbieBurns in forum Office Software
    Replies: 3
    Last Post: 27th August 2008, 04:20 PM
  4. Web based questionnaire to Access/SQL express dB
    By joe90bass in forum How do you do....it?
    Replies: 4
    Last Post: 23rd June 2008, 03:46 PM
  5. Refreshing MS Access linked tables to CMIS MS SQL
    By dickyfinn02 in forum MIS Systems
    Replies: 0
    Last Post: 23rd April 2007, 05:33 PM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •