Updated to 3.3.4, and this issue is still occurring.

This comment was created with the J!Tracker Application at http://issues.joomla.org/.

Updated to 3.3.5, and this issue is still occurring.

This comment was created with the J!Tracker Application at http://issues.joomla.org/.

Unfortunately a patch hasn't been submitted to resolve this issue. Until then, the issue will continue to occur.

Indeed. Could it be that the issue is not being found by other developers? I'm seeing that the categories on this issue item are not reflected in the search filters. Is it possible to edit the tags to be updated to "3.3.5" and also ensure the categories ("IIS", "MS SQL") are active in the list of issues? Currently, when selecting these categories in the Search Tools, this issue does not appear in those filtered lists. Thank you.

This comment was created with the J!Tracker Application at http://issues.joomla.org/.

I changed build to a generic 3.x and added the categories.

Part of the issue is that we have a lack of developers who work with the SQL Server environment. I did until my Windows system crashed last year and I just never got around to setting up a new VM or full desktop system. It's the same with PostgreSQL users unfortunately; anything that isn't MySQL just doesn't get as much attention since most of our contributors don't have environments supporting those databases set up.

I hear you. Makes sense -- MSSQL isn't my environment of choice either. It sounds like my best option currently is to delve into the software myself and come up with a patch, and then propose it here. I don't consider myself to be that knowledgeable of programming but I will make an attempt. This error basically renders Joomla 3.x as unusable in a production environment with MSSQL. I've already determined that Joomla isn't including the "path" column for queries of this nature, but I don't know how to trace back to the file where this should be happening. Thanks for helping with this Issue item, though!

This comment was created with the J!Tracker Application at http://issues.joomla.org/.

run the below commands on your MSSQL database, replacing # with whatever your database prefix is.
This will add default values to the path and img columns in the menu table.
Alternatively you can do it via the SQL Server Manager studio using the Design interface

ALTER TABLE dbo.#menu ADD CONSTRAINT DF_#menu_path DEFAULT '' FOR path
ALTER TABLE dbo.#menu ADD CONSTRAINT DF_#menu_img DEFAULT '' FOR img

e.g.
ALTER TABLE dbo.joomla_menu ADD CONSTRAINT DF_joomla_menu_path DEFAULT '' FOR path
ALTER TABLE dbo.joomla_menu ADD CONSTRAINT DF_joomla_menu_img DEFAULT '' FOR img

This comment was created with the J!Tracker Application at issues.joomla.org/joomla-cms/4207.

Awesome -- this seems to resolve the issue. Thank you! I have tested and as far as I'm concerned, it can be added to future Joomla! updates.

Could you guys submit a pull request on github? Here's how:
http://docs.joomla.org/Git_for_Coders

For simple changes, you can use the github UI:
http://docs.joomla.org/Using_the_Github_UI_to_Make_Pull_Requests

I would love to do that -- I am confused as to where to create a pull request for database updates. Wouldn't there be a "3.3.7" sql file somewhere?

This comment was created with the J!Tracker Application at issues.joomla.org/joomla-cms/4207.

I am confused as to where to create a pull request for database updates. Wouldn't there be a "3.3.7" sql file somewhere?

You just create one yourself: /administrator/components/com_admin/sql/updates/mysql/3.3.7-2014-10-20.sql, and similar ones for postgres and mssql.

Hi ¿This fix is already in joomla update? How can i resolve It, i have been all day triying to add/edit menu items with no result, in a VPS Windows. Help please

Thanks adrianfso, I create the file but no changes were made, still getting Internal Server Error. I am pretty lost with this issue, all works well excepto for the menu.

Hi. maluc21. Well I think you would have to actually take those two statements contained in the file and open a program such as Microsoft SQL Server Management Studio, connect to your database, and execute those two lines of code (but first you have to replace #_ with your database's prefix). This is a complicated series of steps if you're not already able to do it. So you might research how SQL works if you aren't familiar. Hope this helps!

after doing above sql changes for path and img now it occurs for 'params' also.

[Microsoft][SQL Server Native Client 11.0][SQL Server]Cannot insert the value NULL into column 'params', table 'joomla.dbo.joomla_menu'; column does not allow nulls.

Look at this one also.

[Microsoft][SQL Server Native Client 11.0][SQL Server]Cannot insert the value NULL into column 'custom_data', table 'joomla.dbo.joomla_extensions'; column does not allow nulls.

We need to do same for the above :

ALTER TABLE [dbo].[joomla_extensions] ADD CONSTRAINT [joomla_extensions_custom_data] DEFAULT '' FOR custom_data;

unfortunately here we are again with another NULL or NOT NULL issue
something like #4687 @bakual @wilsonge do you remember

so again a temporary fix
@adrianfso could you add to #5368 the same fix for the params,custom_data fields as suggested by @rbsl-mohit , in addition i think we need to change the installation\sql\sqlazure\joomla.sql file accordingly

the fix
we need to think seriously about this "amletic" issue

I have made a fix that made the menu items work again, I don´t know if this is not really a good solution but now the problem has gone away. In Plesk I Select Value "none" in the option open_basedir. The menus now work but sometimes I got internal server error pages when I make changes, hit back and the changes are made. I hope future uptades fix this problem the right way, thanks

unfortunately here we are again with another NULL or NOT NULL issue
something like #4687 @bakual @wilsonge do you remember

That's why I don't like the approach from @wilsonge. It would be very simple to allow NULL for optional data. If we leave it at NOT NULL, the code needs to be adjusted in several places to account for it.
Or we set it to NOT NULL DEFAULT '' for PostgreSQL and MSSQL (for MySQL it's there implicite anyway).

Have you looked at the data here? Path should never ever be null with the exception of the root node (and the root node is inserted during install and should never be changed after that) - as it should be the computer alias to your item!

Now the image column on the other hand #5368 I completely agree with removing as that always is an empty string (we should do that across all DB's I guess)

@wilsonge path is varchar. That's not an issue. We could define a default there if we wanted/needed.
The issue mentioned by @alikon was about the params field and the custom_data field in the @__extensions table. Those are TEXT fields where we have the problem that MySQL doesn't allow a default value (but assumes an empty string) and other databases would require it defined if we don't pass a value.

Well it is according to #5368 :P Either way. custom_data should definitely be allowed to be null (in all databases).

I'm not sure about params. I think that should be a empty json string actually. I'm not sure if it can have any other use cases?

Since MySQL doesn't allow to specify an default for TEXT fields but always will use an empty string, I think we should either allow NULL or specify a DEFAULT '' in the other databases to be consistent. If we define an empty json string for the other databases, it would in the end be inconsistent.
Also from an architectural view it's probably not very wise to have an implementation logic (JSON string for storing params) on the database level. We may change that in future to another format and would have to change the database structure because of that.

You don't hardcode that into the database. You code it into the table class. Again that is what the check method exists for....

Again, it's where I disagree

Reading through these comments, I'm not sure how to proceed. If someone could summarize what changes should be made to the file, I'll be happy to make the changes (if nobody else can or will). Thanks!

This is still a problem in mssql which basically renders mssql unusable for use in combination with joomla. I suggest a practical solution for the 3.x series and a makeover of the database schema - as suggested at another place by @alikon - for 4.x. Otherwise this deadlock situation will continue forever.

Did this get any further? Only today came across a need to use SQL Server and hit the NOT NULL issue. I like @waader suggestion - practical solution for J3.X, and a rethink for 4.X.

Looks like someone ran the same issue while installing Gantry5 to MSSQL. MySQL is all good.

I'm keeping the issue in Gantry5 open for now; I'd rather to fix it in here, but as most of us, I'm also lacking a proper environment (and knowledge) to test it out.

Which way we should go for: adding the default values in JTable or fixing the database fields?

It depends on the logical thing. For example if the data is a json field it makes sense to set a default of an empty json object in the table class. If it's an optional field that isn't always populated you fix the database fields to be not null and you take it on a case by case basis

Hello @adrianfso

Thank you for your contribution.

The last comment here was on 5th June. Can you create a pull request for this issue?

If no reply is received within 4 weeks we will close this issue.

Thanks for understanding!

@adrianfso I see you already created a pull request. Thanks, I will close this issue.