MS SQL Error

[starace]

Using MS SQL 2005 Express, PHP 5, W2k3 Server

can anyone help and explain why the following might be happening

I try to edit a call and save and get the following

SQL Error : Incorrect syntax near 'mu'.SQL Error : Incorrect syntax near 'mc'.

The log states the following

02/09/07 15:25:02,082 [3348] FATAL SugarCRM - Incorrect syntax near 'mc'.: UPDATE calls_contacts mc SET mc.accept_status = 'none' WHERE mc.deleted = 0 AND mc.call_id = '9c11c23b-572a-9eeb-e279-45cb5efd694c' AND mc.contact_id = 'e4ddcb2d-6f2c-a057-6b05-45cb3e3c8149'
02/09/07 15:25:02,084 [3348] FATAL SugarCRM - SQL Server error: Incorrect syntax near 'mc'.
02/09/07 15:25:02,086 [3348] FATAL SugarCRM - SQL Server error: Incorrect syntax near 'mc'.


I also get sql errors when using the pagination of the calls dashlet

SQL Error : Incorrect syntax near the keyword 'DISTINCT'.

Log states
SELECT TOP 11 * FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY calls.time_start ASC) AS row_number, DISTINCT calls.id , calls.status , calls.name , calls.duration_hours , calls.duration_minutes , calls.date_start , calls.time_start , calls.assigned_user_id FROM calls INNER JOIN calls_users ON calls.id = calls_users.call_id where ((calls.date_start + ' ' + time_start)>='2007-02-09 05:00:00' AND (calls.date_start + ' ' + time_start)<='2007-02-10 04:59:59'
AND calls.status IN ('Planned')
) AND calls.deleted=0 AND calls_users.deleted = 0 AND (calls.assigned_user_id = '96452c36-a39c-0493-da01-44c78bc2cf94' OR calls_users.user_id = '96452c36-a39c-0493-da01-44c78bc2cf94')
) AS a
WHERE row_number > 10
02/09/07 15:29:59,393 [3348] FATAL SugarCRM - SQL Server error: Incorrect syntax near the keyword 'DISTINCT'.


Sugar not happy with MS SQL


Starace

[andydreisch]

Hi starace, let's get crackin' on this right away. Can you please enter a bug. I'll attempt to repro this in house as well.

Please log a bug, and report the bug # back here for future reference.

Thanks,

Andy

[starace]

Dashlet bug was entered: 11520

Can not edit and save call bug entered: 11521

[franklin_sugar]

These bugs are also reproducible on 4.5.1 build 1197. I raised their priorities. We will fix them in the coming release. Thanks for catching the bugs.

Franklin

[mrobertson]

This error can be fixed in mssqlmanager by changing the line

$hasDistinct = strpos($matches[0], "distinct");
to
$hasDistinct = strpos($matches[0], "DISTINCT");

[Deuce]

Or,
$hasDistinct = strpos(strtolower($matches[0]), "distinct");
which would allow for both upper and lowercase queries.

[andydreisch]

In any event, we appreciate the fix and I've informed the Dev team of this.

Andy

[mch423]

Thanks for the fix! Unfortunately it just missed the 4.5.1e train which we will be releasing in early July. We will incorporate the fix in 4.5.1f which is due out in August.

- Max