Closed mkane848 closed 8 years ago
@mkane848 Thank you for reporting again.
The error you receive is due to incorrect implementation of the SQL standard by Microsoft. You are not allowed to put multiple null values for columns under a unique constraint in MS SQL Server. See the following page for more details: https://connect.microsoft.com/SQLServer/Feedback/Details/299229
We can use filtered unique indexes instead of unique constraints as a workaround like @tax does in the pull request #43 . I've made similar changes to the pull request from him in my local repository to test this technique and confirmed that I can resolve the error.
But I've found that we can't use MS SQL Server as a backend of django CMS for another reason. django-treebeard, a dependency of django CMS, has an implementation to generate an SQL statement without using Django's database functions, and it generates an SQL statement inside django CMS that is invalid for MS SQL Server. You can find the actual code for this in the following page: https://github.com/tabo/django-treebeard/blob/4.0/treebeard/mp_tree.py#L250
django-treebeard generates an SQL statement like the following one when you create a new page on django CMS:
UPDATE [cms_page] SET path=?||SUBSTR(path, ?), depth=LENGTH(?||SUBSTR(path, ?))/? WHERE path LIKE ?
And you will receive an error like the following even if you resolve the first error:
[42000] [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Incorrect syntax near '|'. (102) (SQLExecDirectW)
For MS SQL Server the SQL statement should be like:
UPDATE [cms_page] SET path=?+SUBSTRING(path, 0, ?), depth=LEN(?+SUBSTRING(path, 0, ?))/? WHERE path LIKE ?
Unfortunately we are unable to employ MS SQL Server for django CMS backend unless the error above is resolved. If you really need to use MS SQL Server for django CMS backend, why don't you ask django-treebeard guys to support MS SQL Server in addition to other database engines or to use Django's database functions instead of hard coding SQL fragments in their codes to generate SQL statements?
Thank you for the update! It was incredibly informative, and now I've actually got a clue as to how to go about resolving this :)
Thanks again for the info and help
Sorry, back again! Still same setup as last time: Django on a Windows Server 2012, MS SQL Server. Working with djangoCMS and having the above issue when creating new pages. Full traceback:
I tried to flush the database from the command line to see if it was something I did, but I also get this error when running
python manage.py flush
:Thank you for the fix last time, and anything you can do to help with this!