Paging issues again

[Gilneas]

Hey guys,

I have the following environment and hope you can help me with some advise:

sugarCE v6.2.4 (no external modules or bigger customizations)
MS SQL05
IIS6 with FastCGI and PHP 5.2.17


My problem with the paging is the following:
Previously I searched for contacts / accounts / whatever and get displayed s.th. like

(1 - 50 of 1857)

on the top of the column headers. But for any reasons some of my searches failed with an SQL error after paging through the search results.

Warning: mssql_query() [function.mssql-query]: message: Incorrect syntax near
the keyword 'select'. (severity 15) occured in
C:\Inetpub\wwwroot\sugar\include\database\MssqlManager.php on line 271
[2011-12-27 16:57:40] display_stack_trace caller, file:
C:\Inetpub\wwwroot\sugar\include\utils.php line#: 2927
[L:](:StackTraceErrorHandler)
C:\Inetpub\wwwroot\sugar\include\database\MssqlManager.php[L:271](:mssql_query)
C:\Inetpub\wwwroot\sugar\include\database\MssqlManager.php[L:586](MssqlManager:query)
C:\Inetpub\wwwroot\sugar\include\ListView\ListViewData.php[L:335](MssqlManager:limitQuery)
C:\Inetpub\wwwroot\sugar\include\ListView\ListViewDisplay.php[L:134](ListViewData:getListViewData)
C:\Inetpub\wwwroot\sugar\include\MVC\View\views\view.list.php[L:172](ListViewDisplay:setup)
C:\Inetpub\wwwroot\sugar\include\MVC\View\views\view.list.php[L:289](ViewList:listViewProcess)
C:\Inetpub\wwwroot\sugar\include\MVC\View\SugarView.php[L:127](ViewList:display)
C:\Inetpub\wwwroot\sugar\include\MVC\Controller\SugarController.php[L:293](SugarView:process)
C:\Inetpub\wwwroot\sugar\include\MVC\Controller\SugarController.php[L:274](SugarController:processView)
C:\Inetpub\wwwroot\sugar\include\MVC\SugarApplication.php[L:85](SugarController:execute)
C:\Inetpub\wwwroot\sugar\index.php[L:51](SugarApplication:execute)
Warning: mssql_query() [function.mssql-query]: Query failed occured in
C:\Inetpub\wwwroot\sugar\include\database\MssqlManager.php on line 271
[2011-12-27 16:57:40] display_stack_trace caller, file:
C:\Inetpub\wwwroot\sugar\include\utils.php line#: 2927

I copied the related SQL statement to a MS SQL console and I found that a sub select statement was wrong or at least incomplete. I messed around, deleted my saved searches, created new ones and changed column layout (still using sugar's native search functions).
Right now the paging works, but it does not show me the sum of results anymore. It just displays s.th. like:

(1 - 50 of 51+)

This is quite annoying. Only way to get the sum of results displayed is to navigate to the last result page.
Therefore selection and aciotns on results to all results only work at the last page.

I tried with IE8, IE9, chrome and FF 3.6 FF9. I also copied the sugar instance to another Webserver what changed nothing to the results.

I would be glad about any idea.

/Edit:
Of course I also tried:
- Quick repair
- Restart of the webserver
- Another user

[eggsurplus]

If there are no other customizations sounds like you may be facing a bug in include/database/MssqlManager.php. You may be able to find how the bad sql that you are finding is getting built in that file.

[Gilneas]

Well the thing is, I had a custom field in use which was not displayed but available in layout options of advanced search. I completely deleted that field, cause I read in another topic that there might be issues with saved search values for fields which were first used and then removed from search.
After deleting the field I do not have these SQL issues, except the counting thing.

There are lots of topics related to paging, also an old one of myself. But last time I completely set up sugar from scratch and this is not my prefered way atm.

/edit: btw. the incorrect SQL command was (from sugar.log):

[..] Query Failed: SELECT TOP 51 * FROM
(
SELECT ROW_NUMBER()
OVER (ORDER BY select name) AS row_number,
contacts.id ,contacts_cstm.skype_c,contacts_cstm.type_c, LTRIM(RTRIM(ISNULL(contacts.first_name,'') + ' ' + ISNULL(contacts.last_name,''))) as name, contacts.first_name , contacts.last_name , contacts.salutation , accounts.name account_name, jtl0.account_id account_id, contacts.title , contacts.phone_work , contacts.phone_mobile , contacts.assigned_user_id FROM contacts LEFT JOIN contacts_cstm ON contacts.id = contacts_cstm.id_c LEFT JOIN accounts_contacts jtl0 ON contacts.id=jtl0.contact_id AND jtl0.deleted=0
LEFT JOIN accounts accounts ON accounts.id=jtl0.account_id AND accounts.deleted=0
AND accounts.deleted=0 where contacts.deleted=0
) AS a
WHERE row_number > 50: SQL Server error: Incorrect syntax near the keyword 'select'.

[Gilneas]

I did not find a solution for this issue, so I submitted a bug:
SugarCRM Bug Tracker | Open Source Business & Social CRM - SugarCRM

[Gilneas]

I did not find a solution for this issue, so I submitted a bug:
SugarCRM Bug Tracker | Open Source Business & Social CRM - SugarCRM

Now I'm really nerved. My bug has been closed with solution "Works as designed", because it's not a bug, but a future feature request. Never heard about a a projected bug...