Hi

Does #11396 resolve this issue?

Unfortunately no, this is because the fault lies with the getTableColums method of the JDatabaseDriverSqlsrv that tries to get the column details for a given database table.

The issue here is it uses this query below to get the default values for the table rows say for example '#__user_notes'--which to be honest you don't really have another way of getting that information.

SELECT column_name as Field, data_type as Type, is_nullable as 'Null', column_default as 'Default'
FROM information_schema.columns WHERE table_name = 'nqu7n_user_notes'

[Note 'nqu7n' is the prefix used for my database please change this with the one you are using]

If you run this query in any version of MS SQL Server and look at what it is given you for the default values, you will see straight away that this is going to cause issues. Please see my description above which goes in more depth.

The SQL Issue arises (Cannot convert value as Int or Cannot convert blah as datetime2 etc..) when you are using JTable to create a new row in a database table, and you don't explicitly supply the default values for every column that has a default value.

If you apply my patch, it will filter the default values and return them in a correct format.

Sorry I don't have mssql to test and to be honest with you it looks like
there are very few <100 users of joomla on mssql

Thank you for your reply. I do understand that this is difficult to test if you don’t have MSSQL but the Joomla team clearly have put a lot of effort in making Joomla work on non-MySQL servers, and it would be a shame to see that work go to waste with such a simple fix.

On our side, we have thoroughly tested it as we have been working on a live project
that uses SQL Server so it is important to us that this fix can be applied so we can continue to upgrade Joomla for that project for security patches. The reason I am pushing this change is due to this issue #11280
(#11280 (comment)) I have committed to fixing this issue involving MSSQL. However, I cannot apply the fix for that one because it is being held up by this PR.

I also believe that there is a duty of care issue as Joomla states it is MSSQL compliant. We would be more than happy to fix these issues; we just need someone to test our patches so we will be able to fix issues
like this and the other one mentioned in this PR

Unfortunately the people who did the work to support mssql disappeared as
soon as they committed the code.

Right now my understanding is that the PLT are re-evaluating if Joomla
should continue to support mssql as we don't have the resources to test and
maintain it and the number of users is so small.

Maybe @wilsonge or @rdeutz caan comment

We would be more than happy to support more database types. As far as I know then besides PostgreSQL all others are broken. I don't think it is realistic and we don't have the people and knowledge to support more.

I would merge it when it has 2 successful tests as we usually do

@sovainfo and @roland-d would you be so kind to test this PR. Thanks.

I agree with @rdeutz here. I don't have any MSSQL server available, hopefully others have and can check. Although community support for MSSQL is nearly non-existent as we found out.

Tried to submit a positive test with issues.joomla.org, but that failed.

Used this code to test:

<?php
$datatypes = array("((0))", "((10))", "('1900-01-01T00:00:00.000')", "(getdate())", "('1900-01-01')", "('')", "('1234')", "(N'1234')", "(NULL)");
foreach ($datatypes as $datatype) {
    echo $datatype;echo " : ";
    echo preg_replace("/(^(\(\(|\('|\(N'|\()|(('\)|(?<!\()\)\)|\))$))/i", '', $datatype);
    echo "\n";
}
?>

Output:
((0)) : 0
((10)) : 10
('1900-01-01T00:00:00.000') : 1900-01-01T00:00:00.000
(getdate()) : getdate()
('1900-01-01') : 1900-01-01
('') :
('1234') : 1234
(N'1234') : 1234
(NULL) : NULL

I have tested this item ✅ successfully on 0fba84c

@waader and @lmcculley Hi gents I am trying to get a fix in for those of us that have MSSQL and I am wondering if you could test this PR so that we can get this fix in

@Bakual would you be so kind to test the fix?

I have tested this item ✅ successfully on 0fba84c

Thanks @williamsandy! Just ping me if you have other mssql patches to test. But it may take some days as I am busy right now.

Thanks for testing this, RTC

@joomla-cms-bot please ?

@zero-24 I am changing ! my name

hehe ? Thanks.