SIMS to iSAMS: How to run SQL scripts to extract data

[dumontict]

Morning folks,

 

I have a bunch of SQL scripts that I want to run to extract data from SIMS. They are custom made by iSAMS and contain all the necessary tables/fields, etc so we can package all relevant data up prior to migrating everything to iSAMS in a few weeks.

 

My question is how can this be done? The IT guys seem pretty busy and haven't done it for a long time, and as I'm project managing it I thought I ask here instead.

 

Many thanks

[jinnantonnixx]

I've found Powershell excellent for this.

 

The invoke-sqlcmd is the simplest way of populating a Powershell object with data.

 

$results = invoke-sqlcmd -server "$server\Instance1" -database "Database1" -query "SELECT MAX(TimeStamp)[TimeStamp], COUNT(*) AS Count FROM [Database1].[dbo].[Table1];"

 

 

http://sqlmag.com/powershell/4-essential-powershell-provider-commands-sql-server

Edited May 9, 2016 by jinnantonnixx

[bigdannyb79]

if your a non techy I'd just go to SQL management studio and you choose new query against the database and you can then see the output and right click and save as if all went well.

 

Do you mind me asking your school and phase ? - interested to hear how things go with ISAMS, I was impressed with what I have seen of the product.

[Spl1t]

Hi there,

 

Personally speaking I feel you should be reluctant into running a bunch of SQL scripts unless you're sure exactly what they're doing. I am sure they are probably selecting data across multiple tables and therefore not making any changes but couldn't iSAMS technical team do this for you? Secondly if you feel confident enough the SQL scripts are just selecting then do you know how they want data in terms of format?

 

Best of luck with the process of moving from SIMS to iSAMS

[bobsmith]

you can create a SQL account with read only access and execute the scripts with that. Gives you a bit more peace of mind.

[dumontict]

Thank you folks, I have a keen colleague already looking into those methods. We're going to use an offline backup copy in case of calamity

[jwinters]

I have a bunch of SQL scripts that I want to run to extract data from SIMS. They are custom made by iSAMS and contain all the necessary tables/fields, etc so we can package all relevant data up prior to migrating everything to iSAMS in a few weeks.

 

Having just done the same moving from SchoolBase to iSAMS, I'm intrigued to know what iSAMS provided in the way of SQL scripts for a similar SIMS => iSAMS migration. There are something like 150 spreadsheets in the iSAMS Excel data import workbooks, and for a SchoolBase => iSAMS migration they provided sample SQL for about 6 of them. It provided a few useful pointers, but there were lots of bugs and omissions in it.

 

I ended up writing about 9000 lines of code to effect the data extraction from SchoolBase. I'd be very interested to know if their SIMS data extraction material is any more complete.