This topic is about getting MS SQL Server to work with Moodle v1.5.x. There is a working patch available for v1.4.3, but it's no help for v1.5. To get Moodle v1.5 working with SQL Server, here's what you need to do:
Modify lib\datalib.php to accomodate SQL Server. For example, SQL Server does not support renaming columns, under some circumstances, but you can still (usually) use the system procedure 'sp_rename' to rename the column.
Modify lib\setup.php to use $CFG->dsn. You did set the DSN string in config.php, didn't you?
Create ado_mssql files from postgres7 files: Make a copy of every postgres7.sql and postgres7.php. Rename the copies to ado_mssql.sql and ado_mssql.php. Edit every ado_mssql file to conform to MS SQL Server's language requirements. Examples of changes you must make:
Replace 'SERIAL' with 'int IDENTITY'
Replace 'int8' with 'bigint'
Replace 'firstname||\' \'||lastname' with 'firstname+'' ''+lastname'
There is a conflict with SQL Server new in Moodle version 1.5. The 'timezone' table uses a column calle 'rule', which is a reserved word in Transact-SQL (SQL Server); i.e. you can't use 'rule' as a column name on SQL Server. If anyone has a nice, clean solution to this conflict, please post it here.
Attached is a Perl script to scan and modify setup.php, so that it will hopefully work in future versions of setup.php.
Thanks! This helped!
Also be sure to change 'SERIAL8' to 'bigint IDENTITY'
I changed 'rule' to '[rule]' and it lets me define the table. I still have to test it though to see if any other changes are required.
Another problem: had to add
id int IDENTITY PRIMARY KEY,
after
CREATE TABLE prefix_log_display (
so that the generic insert_record call in datalib.php doesn't fail trying to select WHERE id='-1'
And I'm getting lots of COLUMN errors I'm going to have to wade through.
Also be sure to change 'SERIAL8' to 'bigint IDENTITY'
I changed 'rule' to '[rule]' and it lets me define the table. I still have to test it though to see if any other changes are required.
Another problem: had to add
id int IDENTITY PRIMARY KEY,
after
CREATE TABLE prefix_log_display (
so that the generic insert_record call in datalib.php doesn't fail trying to select WHERE id='-1'
And I'm getting lots of COLUMN errors I'm going to have to wade through.
I'm working on a perl script to convert all the postgres7 files to ado_mssql files.
I've run into:
# Reset the sequence. doing it this way rather than simply taking out
# id from the preceeding sql to avoid missing fks.
SELECT setval('prefix_survey_id_seq', (select max(id) from prefix_survey));
in /mod/survey/db/postgres7.sql
from looking at mysql.sql and orci8po.sql, I'm thinking I can maybe just leave the line out. Any ideas? Anyone have any opinions on mysql vs. postgres as a source to convert from? I'm thinking mysql may actually have been a better choice.
I've run into:
# Reset the sequence. doing it this way rather than simply taking out
# id from the preceeding sql to avoid missing fks.
SELECT setval('prefix_survey_id_seq', (select max(id) from prefix_survey));
in /mod/survey/db/postgres7.sql
from looking at mysql.sql and orci8po.sql, I'm thinking I can maybe just leave the line out. Any ideas? Anyone have any opinions on mysql vs. postgres as a source to convert from? I'm thinking mysql may actually have been a better choice.
Am I the only person noticing a disparity between 'log_display' and 'prefix_log_display'? the .sql files use prefix_log_display, but the .php files don't prefix with $CFG->prefix ...
I'm new to moodle, is this all 1.5 stuff? I'm beginning to think my problems aren't just mssql issues. The included postgres7 and mysql files have the same disparity.
Digging some more, it seems to be trying to do a bunch of updates that I'm not sure it needs to. I see my $currmodule->version is empty when I'm in 'assignments'. version.php is there. I'll have to try again tomorrow- I'm beyond concentration now.
I'm new to moodle, is this all 1.5 stuff? I'm beginning to think my problems aren't just mssql issues. The included postgres7 and mysql files have the same disparity.
Digging some more, it seems to be trying to do a bunch of updates that I'm not sure it needs to. I see my $currmodule->version is empty when I'm in 'assignments'. version.php is there. I'll have to try again tomorrow- I'm beyond concentration now.
Rich,
I think you are doing well in using the Pg files -- Postgres is closer to a "standard" SQL syntax than MySQL. Make sure you are using a really recent 1.5.2+. Perhaps get an anon CVS checkout of MOODLE_15_STABLE so you can get updates and fixes, and create patchfiles.
You ask about skipping:
SELECT setval('prefix-survey-id-seq', (select max(id) from prefix_survey));
I am not sure how MSSQL deals with sequences for a primary key. Perhaps you don't need it, but if you do... you really do
With regards to
Am I the only person noticing a disparity between 'log-display' and 'prefix-log-display'? the .sql files use prefix-log-display, but the .php files don't prefix with $CFG->prefix ...
The functions that don't take SQL directly (like get-record()) add $CFG->prefix automagically. The functions that take SQL directly (get-record-sql()) aren't that smart...
Edit: replaced all underscores w hyphens. I almost love Markdown.
I agree: Postgres SQL is closest to MS SQL. I have also been working on Perl scripts to convert the Postgresl SQL files to ADO MSSQL. The scripts I have work, but I'm sure I missed the interval conversion described in the previous post.
Thanks for all the help Martin,
with regards to 'setval' and sequences for primary keys, setting IDENTITY in mssql tells it to auto-increment the field for each insert. In fact, you can't specify the value for the field yourself, unless you turn IDENTITY off. In my perl script, I have:
if ($issql) { # if this is a .sql file as opposed to a .php file<br /><br /> # if the command is 'insert into'<br /> # and the 'id' field is specified<br /> if ($_ =~ /insert\s+into/i and $_ =~ /\(\s*id\s*,/) {<br /> @cmdparts = split /\s+/, $_;<br /> $tablename = @cmdparts[2];<br /> $_ = "SET IDENTITY_INSERT ".$tablename." ON;\n".$_."SET IDENTITY_INSERT ".$tablename." OFF;\n"<br /> }
}
which turns identity on/off around inserts which set the 'id' field. I'm making the assumption that ALL 'id' fields in moodle are SERIAL in postgres, and hence IDENTITY in mssql.
I _think_ that's basically what setval was doing? If it's just making sure I have unique keys, I think I'm ok.
with regards to 'setval' and sequences for primary keys, setting IDENTITY in mssql tells it to auto-increment the field for each insert. In fact, you can't specify the value for the field yourself, unless you turn IDENTITY off. In my perl script, I have:
if ($issql) { # if this is a .sql file as opposed to a .php file<br /><br /> # if the command is 'insert into'<br /> # and the 'id' field is specified<br /> if ($_ =~ /insert\s+into/i and $_ =~ /\(\s*id\s*,/) {<br /> @cmdparts = split /\s+/, $_;<br /> $tablename = @cmdparts[2];<br /> $_ = "SET IDENTITY_INSERT ".$tablename." ON;\n".$_."SET IDENTITY_INSERT ".$tablename." OFF;\n"<br /> }
}
which turns identity on/off around inserts which set the 'id' field. I'm making the assumption that ALL 'id' fields in moodle are SERIAL in postgres, and hence IDENTITY in mssql.
I _think_ that's basically what setval was doing? If it's just making sure I have unique keys, I think I'm ok.
I think you're doing the right thing ;)
Postgres sets up a sequence for each primary key, and there are cases where the sequence and the table get out of sync, so you setval the sequence to max(id). Doesn't happen often, and it's usually a sign that we fsck'd up something else, and are trying to fix it
Postgres sets up a sequence for each primary key, and there are cases where the sequence and the table get out of sync, so you setval the sequence to max(id). Doesn't happen often, and it's usually a sign that we fsck'd up something else, and are trying to fix it
Attached is a patch file for datalib.php in Moodle v. 1.5. The biggest problem is the LIMIT clause, which the current code assumes you can tack onto the end of any SQL command. MS SQL just doesn't support this keyword, so the SQL code handling had to be reworked all over the place.
Changes:
Changed every if-then to a switch() when checking for $CFG->dbtype.
- Added a section for ado_mssql wherever database-specific commands are running.
- Added function build_sql() to build up a db-specific SQL command.
- Added function sql_db_ind_paging_limit to handle db-independent "paging" limits; intended to replace function sql_paging_limit.
- Added (defaulted) parameters to count_records_select() to break out LIMIT clause handling, plus code to handle the LIMIT in a db-independent fashion.
Changes:
Changed every if-then to a switch() when checking for $CFG->dbtype.
- Added a section for ado_mssql wherever database-specific commands are running.
- Added function build_sql() to build up a db-specific SQL command.
- Added function sql_db_ind_paging_limit to handle db-independent "paging" limits; intended to replace function sql_paging_limit.
- Added (defaulted) parameters to count_records_select() to break out LIMIT clause handling, plus code to handle the LIMIT in a db-independent fashion.
I seem to have a different version of datalib.php than you. I have moodle-1.5.2 ( $version = 2005060220;), which doesn't seem to have a provision for rollback in datalib.php, so hunk 2
96,98c101,106
< if ($CFG->dbtype === 'postgres7') {
< return execute_sql('ROLLBACK', false);
< }
---
> switch ($CFG->dbtype) {
> case 'postgres7':
> case 'ado_mssql':
> return execute_sql('ROLLBACK', false);
> break;
> }
fails. The last two hunks also fail (50 and 51, original file isn't that long). Is mine older? Looks like maybe I should try CVS...
96,98c101,106
< if ($CFG->dbtype === 'postgres7') {
< return execute_sql('ROLLBACK', false);
< }
---
> switch ($CFG->dbtype) {
> case 'postgres7':
> case 'ado_mssql':
> return execute_sql('ROLLBACK', false);
> break;
> }
fails. The last two hunks also fail (50 and 51, original file isn't that long). Is mine older? Looks like maybe I should try CVS...
> Attached is a patch file for datalib.php in Moodle v. 1.5.
What version exactly? Are you working off an unzipped 1.5.0, 1.5.1, 1.5.2, 1.5.x+ or anon cvs?
> The biggest problem is the LIMIT clause
Yes, it's a problem with MySQL vs MSSQL vs Postgres. Perhaps we should have a limit_sql(from,length) function that returns the SQL for the current RDBMS, so we can say
get_records_sql("select foo from bar " . limit_sql(0,100));
And avoid peppering the code with if/switch statements.
I got the latest build which had a 20080831 copy of datalib.php, and the patch worked. A couple of bugs:
line 293 of the patch file (line 972 of the result) shoul be:
> $sql .= ';';
(missing ; at the eol)
line 421 (3100 of result)
> $having .= 'COUNT(*) > ' . $offset;
(extra ) )
I've attached the same patch file with those two very minor typos changed.
And I've just been taken off the project... so let me attach my postgres->mssql perl script- maybe it will help somone.
line 293 of the patch file (line 972 of the result) shoul be:
> $sql .= ';';
(missing ; at the eol)
line 421 (3100 of result)
> $having .= 'COUNT(*) > ' . $offset;
(extra ) )
I've attached the same patch file with those two very minor typos changed.
And I've just been taken off the project... so let me attach my postgres->mssql perl script- maybe it will help somone.
My perl script for converting postgres7.php and .sql to ado_mssql.php and .sql
I intended to clean this up a lot before posting, but since anything else I do with moodle will be on my own time, I'm going to post it in case there is no more time.
I intended to clean this up a lot before posting, but since anything else I do with moodle will be on my own time, I'm going to post it in case there is no more time.
I'm glad people are working this but it takes an enormous effort each time plus will create difficult maintenance.
The very best way to solve all this would be to migrate all our schema to a single XML format, using ADOdb XML Schema. It's a big change but will give us complete cross-platform support for many databases.
If anyone is interested in funding this let me know. I'd love to hire someone at Moodle HQ to focus just on this for a couple of months and get it done.
The very best way to solve all this would be to migrate all our schema to a single XML format, using ADOdb XML Schema. It's a big change but will give us complete cross-platform support for many databases.
If anyone is interested in funding this let me know. I'd love to hire someone at Moodle HQ to focus just on this for a couple of months and get it done.
I hope this is the right place to ask this question...
I see lots of people are making changes to the database abstraction files to support MSSQL. Has anyone had a look at doing this for Oracle? I was about to wade in to my copy of datalib.php (1.250.2.11) to see what could be done but a) has anyone already hacked this in a later version, or b) should I be looking at your ADOdb XML suggestion now?
(A bit of background: Moodle is one of our "candidate" VLEs for a forthcoming evaluation, and a lot of our student data is held by our MIS department in Oracle. This database is the backend for the student portal, among other things. I'm looking to hook up a VLE to that, and am investigating calling RPCs on the VLE from the portal, or replacing tables in the VLE's database with VIEWS of the source data.)
I see lots of people are making changes to the database abstraction files to support MSSQL. Has anyone had a look at doing this for Oracle? I was about to wade in to my copy of datalib.php (1.250.2.11) to see what could be done but a) has anyone already hacked this in a later version, or b) should I be looking at your ADOdb XML suggestion now?
(A bit of background: Moodle is one of our "candidate" VLEs for a forthcoming evaluation, and a lot of our student data is held by our MIS department in Oracle. This database is the backend for the student portal, among other things. I'm looking to hook up a VLE to that, and am investigating calling RPCs on the VLE from the portal, or replacing tables in the VLE's database with VIEWS of the source data.)
Agreed, but I need a version of Moodle that works with MS SQL Server as soon as possible. I figure I can get everything at least working in MS SQL Server in less than a couple of months (Well, when I get more time to work on it at all, that is). Is it possible that you could open up the CVS repository to some developers outside Moodle HQ and let them write the XML schema version together from their respective HQ's?
"Agreed, but I need a version of Moodle that works with MS SQL Server as soon as possible"
I've been asked to explore the possibility of porting our Moodle install from MySQL to MSSQL since the school uses MSSQL for many other things. I would like to be able to demo quickly on this and as I read the forum began thinking that others might be able to do more testing if there was a simple version to install that worked. Similar to the XAMPP windows complete package - would someone be willing to post a MSSQL package and simply indicate what version of Moodle it is. For example Moodle 1.5.2 for MSSQL - it certainly would make my job easier at demonstrating that Moodle can be setup with MSSQL. The easier it is to get folks involved the more likely that some support for further development might pop up. Peace - Anthony
I've been asked to explore the possibility of porting our Moodle install from MySQL to MSSQL since the school uses MSSQL for many other things. I would like to be able to demo quickly on this and as I read the forum began thinking that others might be able to do more testing if there was a simple version to install that worked. Similar to the XAMPP windows complete package - would someone be willing to post a MSSQL package and simply indicate what version of Moodle it is. For example Moodle 1.5.2 for MSSQL - it certainly would make my job easier at demonstrating that Moodle can be setup with MSSQL. The easier it is to get folks involved the more likely that some support for further development might pop up. Peace - Anthony
No, I have not received any responses about MS SQL. It is not a major priority but it was something put on our wish list. However, being an opensource fan I have not pursued it vigorously and I can understand why others may not have. I would be interested in hearing from anyone who has a successful implementation using MSSQL - it would be worth sharing with the community.
Hi guys im one of those people trying to figure out how to 'moodle' with MS SQL server. im using MSSQL Advance server in IIS 5. I've updated my datalib.php created ado_mssql.php and ado_mssql.sql just like what you said in this forum.
in ado_mssql.sql i did this:
Replace 'SERIAL' with 'int IDENTITY'
Replace 'SERIAL8' with 'bigint IDENTITY'
Replace 'int8' with 'bigint'
Replace 'firstname||\' \'||lastname' with 'firstname+'' ''+lastname'
Replace 'rule' to '[rule]'
insert 'id int IDENTITY PRIMARY KEY,' to 'CREATE TABLE prefix_log_display ('
here is my code for config.php:
<?php /// Moodle Configuration File
unset($CFG);
$CFG->dbtype = 'ado_mssql';
$CFG->dbhost = 'localhost';
$CFG->dbname = 'moodle';
$CFG->dbuser = 'moodleuser';
$CFG->dbpass = 'moodlepass';
$CFG->dsn = 'Driver={SQL Server};Server=localhost;Database=moodle;UID=$CFG->moodleuser;PWD=moodlepass;';
$CFG->prefix = 'mdl_';
$CFG->wwwroot = 'http://localhost/moodle';
$CFG->dirroot = 'E:\\wwwroot\\moodle';
$CFG->dataroot = 'E:\\wwwroot\\moodledata';
$CFG->admin = 'admin';
$CFG->directorypermissions = 00777; // try 02777 on a server in Safe Mode
require_once("$CFG->dirroot/lib/setup.php");
// MAKE SURE WHEN YOU EDIT THIS FILE THAT THERE ARE NO SPACES, BLANK LINES,
// RETURNS, OR ANYTHING ELSE AFTER THE TWO CHARACTERS ON THE NEXT LINE.
?>
i run install.php and i am able create the moodle tables.
Moodle version is Moodle 1.5.3+ (2005060230).
But when im saving changes in variables form:
this error always occur:
could not update lang to en
could not update langmenu to 0 .... and so on... then there's blank screen.
i tried testing adodb using this code to update mdl_config and it works fine:
filename: test.php
<?php
include('E:\wwwroot\moodle\lib\adodb\adodb.inc.php');
$db = &ADONewConnection("ado_mssql");
print "<h1>Connecting DSN-less $db->databaseType...</h1>";
$db->debug=true;
$CFG->dsn = 'Driver={SQL Server};Server=localhost;Database=moodle;UID=$CFG->moodleuser;PWD=moodlepass;';
$db->Connect($myDSN);
$rs = $db->Execute("update mdl_config set value = 'es_es' where name = 'locale'");
$rs = $db->Execute("select * from mdl_config");
$arr = $rs->GetArray();
print_r($arr);
?>
I have attached my datalib.php here. I need some help... 
I have attached a slightly modified /admin/config.php file.
The small changes at around lines 40-41 fixed this problem for me.
I have an operational version of Moodle running ado_mssql on server2003 IIS6.
It took a bit to get it running but seems to be ok now.
I used much of the info from this forum, but still had to make many changes.
As I am new to Moodle I am sure I have made way to many changes
I agree with Moodle management that a more generic solution to the data layor would be great.
Anyway hope this helps you a bit.
here is a version of the postgres7_to_mssql.pl I have also modified it a bit.
Remember to change the path to your moodle path.
I create a simple .bat file to run this so that I can see the error messages )if any)
postgres7_to_mssql.pl
pause
In addition I found that places that use "distinct" in a select where a text field is involved will not work. In some cases I change the text field to a large varchar. In other cases I change the select * to be the specific fields required.(Should be done this way anyway).
I also wrote a small function that wraped any mssql reserved word up in [...].
There is better ways to do this but it got me going.
// An attempt to put [] around all reserved words. Will not work in all cases.
function mssql_get_safe_reserved_words($sql){
$reservedwords = array('ADD', 'EXCEPT', 'PERCENT', 'ALL', 'EXEC', 'PLAN', 'ALTER', 'EXECUTE', 'PRECISION', 'AND', 'EXISTS', 'PRIMARY', 'ANY', 'EXIT', 'PRINT', 'AS', 'FETCH', 'PROC', 'ASC', 'FILE', 'PROCEDURE', 'AUTHORIZATION', 'FILLFACTOR', 'PUBLIC', 'BACKUP', 'FOR', 'RAISERROR', 'BEGIN', 'FOREIGN', 'READ', 'BETWEEN', 'FREETEXT', 'READTEXT', 'BREAK', 'FREETEXTTABLE', 'RECONFIGURE', 'BROWSE', 'FROM', 'REFERENCES', 'BULK', 'FULL', 'REPLICATION', 'BY', 'FUNCTION', 'RESTORE', 'CASCADE', 'GOTO', 'RESTRICT', 'CASE', 'GRANT', 'RETURN', 'CHECK', 'GROUP', 'REVOKE', 'CHECKPOINT', 'HAVING', 'RIGHT', 'CLOSE', 'HOLDLOCK', 'ROLLBACK', 'CLUSTERED', 'IDENTITY', 'ROWCOUNT', 'COALESCE', 'IDENTITY_INSERT', 'ROWGUIDCOL', 'COLLATE', 'IDENTITYCOL', 'RULE', 'COLUMN', 'IF', 'SAVE', 'COMMIT', 'IN', 'SCHEMA', 'COMPUTE', 'INDEX', 'SELECT', 'CONSTRAINT', 'INNER', 'SESSION_USER', 'CONTAINS', 'INSERT', 'SET', 'CONTAINSTABLE', 'INTERSECT', 'SETUSER', 'CONTINUE', 'INTO', 'SHUTDOWN', 'CONVERT', 'IS', 'SOME', 'CREATE', 'JOIN', 'STATISTICS', 'CROSS', 'KEY', 'SYSTEM_USER', 'CURRENT', 'KILL', 'TABLE', 'CURRENT_DATE', 'LEFT', 'TEXTSIZE', 'CURRENT_TIME', 'LIKE', 'THEN', 'CURRENT_TIMESTAMP', 'LINENO', 'TO', 'CURRENT_USER', 'LOAD', 'TOP', 'CURSOR', 'NATIONAL', 'TRAN', 'DATABASE', 'NOCHECK', 'TRANSACTION', 'DBCC', 'NONCLUSTERED', 'TRIGGER', 'DEALLOCATE', 'NOT', 'TRUNCATE', 'DECLARE', 'NULL', 'TSEQUAL', 'DEFAULT', 'NULLIF', 'UNION', 'DELETE', 'OF', 'UNIQUE', 'DENY', 'OFF', 'UPDATE', 'DESC', 'OFFSETS', 'UPDATETEXT', 'DISK', 'ON', 'USE', 'DISTINCT', 'OPEN', 'USER', 'DISTRIBUTED', 'OPENDATASOURCE', 'VALUES', 'DOUBLE', 'OPENQUERY', 'VARYING', 'DROP', 'OPENROWSET', 'VIEW', 'DUMMY', 'OPENXML', 'WAITFOR', 'DUMP', 'OPTION', 'WHEN', 'ELSE', 'OR', 'WHERE', 'END', 'ORDER', 'WHILE', 'ERRLVL', 'OUTER', 'WITH', 'ESCAPE', 'OVER', 'WRITETEXT'); // add as needed
foreach ($reservedwords as $i => $value) {
$look = $reservedwords[$i];
$sql = str_ireplace (".".$look , "[".$look."]", $sql);
$sql = str_ireplace (" ".$look.",", " [".$look."],", $sql);
}
//Also do a replace on empty field seperators. Asume string
$sql = str_ireplace (", ,", ", '',", $sql);
return $sql;
}
see you
Glenn
Thank you very much Glenn. I updated my ado_mssql using your perl patch postgres7_to_mssql.pl. I also updated admin/config.php but theres still an error:
Error: could not add new variable tabselectedtofront !
After that the installation continues on Setting up modules table. workshop tables have been set up correctly. then this error:
assignment module needs upgrading
(ado_mssql): ALTER TABLE assignment ADD format INTEGER DEFAULT '0' NOT NULL AFTER description
170: [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near 'AFTER'.
Error
(ado_mssql): EXEC sp_rename mdl_assignment[resubmit_alter_column_tmp], [resubmit] 'COLUMN'
170: [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near 'resubmit_alter_column_tmp'.
Error
(ado_mssql): CREATE INDEX mdl_assignment_course_idx ON mdl_assignment (course)
1913: [Microsoft][ODBC SQL Server Driver][SQL Server]There is already an index on table 'mdl_assignment' named 'mdl_assignment_course_idx'.
Error
(ado_mssql): CREATE INDEX mdl_assignment_submissions_assignment_idx ON mdl_assignment_submissions (assignment)
1913: [Microsoft][ODBC SQL Server Driver][SQL Server]There is already an index on table 'mdl_assignment_submissions' named 'mdl_assignment_submissions_assignment_idx'.
Error
(ado_mssql): CREATE INDEX mdl_assignment_submissions_userid_idx ON mdl_assignment_submissions (userid)
1913: [Microsoft][ODBC SQL Server Driver][SQL Server]There is already an index on table 'mdl_assignment_submissions' named 'mdl_assignment_submissions_userid_idx'.
Error
(ado_mssql): CREATE INDEX mdl_assignment_submissions_mailed_idx ON mdl_assignment_submissions (mailed)
1913: [Microsoft][ODBC SQL Server Driver][SQL Server]There is already an index on table 'mdl_assignment_submissions' named 'mdl_assignment_submissions_mailed_idx'.
Error
(ado_mssql): CREATE INDEX mdl_assignment_submissions_timemarked_idx ON mdl_assignment_submissions (timemarked)
1913: [Microsoft][ODBC SQL Server Driver][SQL Server]There is already an index on table 'mdl_assignment_submissions' named 'mdl_assignment_submissions_timemarked_idx'.
Error
(ado_mssql): EXEC sp_rename mdl_assignment[emailteachers_alter_column_tmp], [emailteachers] 'COLUMN'
170: [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near 'emailteachers_alter_column_tmp'.
Error
(ado_mssql): EXEC sp_rename mdl_assignment[timeavailable_alter_column_tmp], [timeavailable] 'COLUMN'
170: [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near 'timeavailable_alter_column_tmp'.
Error
(ado_mssql): EXEC sp_rename mdl_assignment[assignmenttype_alter_column_tmp], [assignmenttype] 'COLUMN'
170: [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near 'assignmenttype_alter_column_tmp'.
Error
(ado_mssql): ALTER TABLE mdl_assignment DROP type;
3727: [Microsoft][ODBC SQL Server Driver][SQL Server]Could not drop constraint. See previous errors.
Error
(ado_mssql): EXEC sp_rename mdl_assignment_submissions[data2_alter_column_tmp], [data2] 'COLUMN'
170: [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near 'data2_alter_column_tmp'.
Error
(ado_mssql): COMMIT TRANSACTION
Success
(ado_mssql): ALTER TABLE mdl_assignment_submissions ADD [data1_alter_column_tmp] TEXT DEFAULT '' WITH VALUES not null
Success
(ado_mssql): EXEC sp_rename mdl_assignment_submissions[data1_alter_column_tmp], [data1] 'COLUMN'
170: [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near 'data1_alter_column_tmp'.
Error
(ado_mssql): COMMIT TRANSACTION
Success
(ado_mssql): ALTER TABLE mdl_assignment ADD [var5_alter_column_tmp] INT DEFAULT '0' WITH VALUES null
Success
(ado_mssql): EXEC sp_rename mdl_assignment[var5_alter_column_tmp], [var5] 'COLUMN'
170: [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near 'var5_alter_column_tmp'.
Error
(ado_mssql): COMMIT TRANSACTION
Success
(ado_mssql): ALTER TABLE mdl_assignment ADD [var4_alter_column_tmp] INT DEFAULT '0' WITH VALUES null
Success
(ado_mssql): EXEC sp_rename mdl_assignment[var4_alter_column_tmp], [var4] 'COLUMN'
170: [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near 'var4_alter_column_tmp'.
Error
(ado_mssql): COMMIT TRANSACTION
Success
(ado_mssql): ALTER TABLE mdl_assignment ADD [var3_alter_column_tmp] INT DEFAULT '0' WITH VALUES null
Success
(ado_mssql): EXEC sp_rename mdl_assignment[var3_alter_column_tmp], [var3] 'COLUMN'
170: [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near 'var3_alter_column_tmp'.
Error
(ado_mssql): COMMIT TRANSACTION
Success
(ado_mssql): ALTER TABLE mdl_assignment ADD [var2_alter_column_tmp] INT DEFAULT '0' WITH VALUES null
Success
(ado_mssql): EXEC sp_rename mdl_assignment[var2_alter_column_tmp], [var2] 'COLUMN'
170: [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near 'var2_alter_column_tmp'.
Error
(ado_mssql): COMMIT TRANSACTION
Success
(ado_mssql): ALTER TABLE mdl_assignment ADD [var1_alter_column_tmp] INT DEFAULT '0' WITH VALUES null
Success
(ado_mssql): EXEC sp_rename mdl_assignment[var1_alter_column_tmp], [var1] 'COLUMN'
170: [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near 'var1_alter_column_tmp'.
Error
(ado_mssql): COMMIT TRANSACTION
Success
(ado_mssql): ALTER TABLE mdl_assignment_submissions ADD [format_alter_column_tmp] INT DEFAULT '0' WITH VALUES not null
Success
(ado_mssql): EXEC sp_rename mdl_assignment_submissions[format_alter_column_tmp], [format] 'COLUMN'
170: [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near 'format_alter_column_tmp'.
Error
(ado_mssql): COMMIT TRANSACTION
Success
(ado_mssql): ALTER TABLE mdl_assignment ADD [preventlate_alter_column_tmp] INT DEFAULT '0' WITH VALUES not null
Success
(ado_mssql): EXEC sp_rename mdl_assignment[preventlate_alter_column_tmp], [preventlate] 'COLUMN'
170: [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near 'preventlate_alter_column_tmp'.
Error
(ado_mssql): COMMIT TRANSACTION
Success
(ado_mssql): SELECT * FROM mdl_assignment;
(ado_mssql): ALTER TABLE mdl_assignment ALTER assignmenttype SET NOT NULL
170: [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near 'assignmenttype'.
Error
(ado_mssql): ALTER TABLE mdl_assignment ALTER emailteachers SET NOT NULL
170: [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near 'emailteachers'.
Error
(ado_mssql): ALTER TABLE mdl_assignment ALTER preventlate SET NOT NULL
170: [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near 'preventlate'.
Error
(ado_mssql): ALTER TABLE mdl_assignment ALTER timeavailable SET NOT NULL
170: [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near 'timeavailable'.
Error
(ado_mssql): ALTER TABLE mdl_assignment_submissions ALTER data1 SET NOT NULL
170: [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near 'data1'.
Error
(ado_mssql): ALTER TABLE mdl_assignment_submissions ALTER data2 SET NOT NULL
170: [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near 'data2'.
Error
(ado_mssql): ALTER TABLE mdl_assignment_submissions ALTER format SET NOT NULL
170: [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near 'format'.
Error
| Could not update assignment record in modules table! |
NEED UR HELP AGAIN!
Hi Mike,
Glad I was able to help. How did your install go?
I have attached an updated version of my datalib file.
It has a slightly changed mssql_get_safe_reserved_words function and various other changes.
Also I have added a prototype function to cater for paging (MySql Limit ) called get_mssql_page_sql. It still needs tidying up but is working ok for me so far. It may hinder performance once the data set gets over 100,000s of records or so, but then again it may be an acceptable performance loss. Its ok for my instal at the moment.
As I am replicating the database using merge replication I have created a function that adds a rowguid field to every table. (AddGuids()).I call it from the bottom of the admin page so that when ever a new table is added it will automatically add the rowguid field. If you dont use replication then dont call this function. Next I am creating a way to seed the identity fields and set identity to not for replication so there will be no problems replicating.
This is only an interim step as I feel the ADOdb XML Schema suggestion has much more merit than the way we have done this install.
See you
Glenn
Hi Mike,
Sorry for not replying sooner, Spam filter was trapping mail from Moodle
I am not using that form of quiz creation so have not had a chance to look at it.
However I have just done a simple test and it all seems to work. Could be I have fixed a generic problem that may have fixed your problem as well.
I have attached my latest datalib file.
Glenn
Hello to all!
I am very interested in getting moodle to work with SQL Express. I am very familiar with it and do not want to have to maintain another database.
With all the work that has been done in the previous posts, I was curious if anyone has come up with a "step by step" approach (along with the appropriate files for download) to get moodle working with SQL Express?
I am working on this "project" by myself to demonstrate to my principal that we do not need to spend $250 per student to use ClassServer from Microsoft. I know that we have an SQL Server at school and upsizing from express is quite easy.
So, I don't want to "reinvent the wheel", so if anyone has a solution I would be very greatful in hearing about it.
Michael
Just wait for Moodle 1.7 in September. http://docs.moodle.org/en/Roadmap
Thank you thank you thank you!!!! I will be very (im)patiently awaiting its arrival!!
I heard about Moodle from Technology and Learning magazine about 2 weeks ago. I am impressed with the entire concept and idea! Most importantly it is very clear that it is "people driven" as evidenced by this very forum!! Wether my school takes to it or not, I am already looking into hosting the site myself!
Thank you very much!
Michael