Results 1 to 5 of 5

Thread: Problem with MSSQL and soap get entry list

  1. #1
    dhavaldarji's Avatar
    dhavaldarji is offline Sugar Community Member
    Join Date
    Mar 2012
    Posts
    115

    Default Problem with MSSQL and soap get entry list

    Hi,
    Using sugarcrm 6.1 with mssql server.

    During soap operation (actually riva integration) when it tries to fetch records it get error like "Column 'users.user_name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY"

    Here is some portion of sugar log.

    Code:
    Mon Feb 11 03:57:06 2013 [31100][50ee1355-2e15-1474-366b-50db333bf953][DEBUG] SugarBean.load_relationships, Loading relationship (calls).
    Mon Feb 11 03:57:06 2013 [31100][50ee1355-2e15-1474-366b-50db333bf953][DEBUG] Link Constructor, relationship name: calls_users
    Mon Feb 11 03:57:06 2013 [31100][50ee1355-2e15-1474-366b-50db333bf953][DEBUG] Link Constructor, Table name: 
    Mon Feb 11 03:57:06 2013 [31100][50ee1355-2e15-1474-366b-50db333bf953][DEBUG] Link Constructor, Key name: 
    Mon Feb 11 03:57:06 2013 [31100][50ee1355-2e15-1474-366b-50db333bf953][DEBUG] Link Constructor, _bean_table_name: users
    Mon Feb 11 03:57:06 2013 [31100][50ee1355-2e15-1474-366b-50db333bf953][DEBUG] Link Constructor, _bean_key_name: id
    Mon Feb 11 03:57:06 2013 [31100][50ee1355-2e15-1474-366b-50db333bf953][DEBUG] Link Constructor, relationship record found.
    Mon Feb 11 03:57:06 2013 [31100][50ee1355-2e15-1474-366b-50db333bf953][DEBUG] SugarBean.load_relationships, Loading relationship (meetings).
    Mon Feb 11 03:57:06 2013 [31100][50ee1355-2e15-1474-366b-50db333bf953][DEBUG] Link Constructor, relationship name: meetings_users
    Mon Feb 11 03:57:06 2013 [31100][50ee1355-2e15-1474-366b-50db333bf953][DEBUG] Link Constructor, Table name: 
    Mon Feb 11 03:57:06 2013 [31100][50ee1355-2e15-1474-366b-50db333bf953][DEBUG] Link Constructor, Key name: 
    Mon Feb 11 03:57:06 2013 [31100][50ee1355-2e15-1474-366b-50db333bf953][DEBUG] Link Constructor, _bean_table_name: users
    Mon Feb 11 03:57:06 2013 [31100][50ee1355-2e15-1474-366b-50db333bf953][DEBUG] Link Constructor, _bean_key_name: id
    Mon Feb 11 03:57:06 2013 [31100][50ee1355-2e15-1474-366b-50db333bf953][DEBUG] Link Constructor, relationship record found.
    Mon Feb 11 03:57:06 2013 [31100][50ee1355-2e15-1474-366b-50db333bf953][DEBUG] SugarBean.load_relationships, Loading relationship (modified_user_link).
    Mon Feb 11 03:57:06 2013 [31100][50ee1355-2e15-1474-366b-50db333bf953][DEBUG] SugarBean.load_relationships, Error Loading relationship (modified_user_link).
    Mon Feb 11 03:57:06 2013 [31100][50ee1355-2e15-1474-366b-50db333bf953][DEBUG] process_list_query:  SELECT  users.* , ISNULL(users.first_name,'') + ' ' + ISNULL(users.last_name,'') as full_name, ISNULL(users.first_name,'') + ' ' + ISNULL(users.last_name,'') as name, jtl0.call_id call_id, jtl0.id accept_status_id, jtl0.accept_status accept_status_name, jtl1.meeting_id meeting_id FROM users   LEFT JOIN  calls_users jtl0 ON users.id=jtl0.user_id  AND jtl0.deleted=0
     LEFT JOIN  calls jt0 ON jt0.id=jtl0.call_id AND jt0.deleted=0
     AND jt0.deleted=0  LEFT JOIN  meetings_users jtl1 ON users.id=jtl1.user_id  AND jtl1.deleted=0
     LEFT JOIN  meetings jt1 ON jt1.id=jtl1.meeting_id AND jt1.deleted=0
     AND jt1.deleted=0 where (( users.id IN (SELECT rel.bean_id FROM email_addr_bean_rel rel INNER JOIN email_addresses ea ON rel.email_address_id = ea.id AND rel.bean_module LIKE 'Users' AND rel.deleted = 0 AND ea.deleted = 0 WHERE (LTRIM(RTRIM(ea.email_address)) LIKE 'abc@test.com')))) AND users.deleted=0 Group By users.id  ORDER BY users.date_modified
    Mon Feb 11 03:57:06 2013 [31100][50ee1355-2e15-1474-366b-50db333bf953][DEBUG] Array
    (
        [0] =>  SELECT  users.* , ISNULL(users.first_name,'') + ' ' + ISNULL(users.last_name,'') as full_name, ISNULL(users.first_name,'') + ' ' + ISNULL(users.last_name,'') as name, jtl0.call_id call_id, jtl0.id accept_status_id, jtl0.accept_status accept_status_name, jtl1.meeting_id meeting_id FROM users   LEFT JOIN  calls_users jtl0 ON users.id=jtl0.user_id  AND jtl0.deleted=0
     LEFT JOIN  calls jt0 ON jt0.id=jtl0.call_id AND jt0.deleted=0
     AND jt0.deleted=0  LEFT JOIN  meetings_users jtl1 ON users.id=jtl1.user_id  AND jtl1.deleted=0
     LEFT JOIN  meetings jt1 ON jt1.id=jtl1.meeting_id AND jt1.deleted=0
     AND jt1.deleted=0 where (( users.id IN (SELECT rel.bean_id FROM email_addr_bean_rel rel INNER JOIN email_addresses ea ON rel.email_address_id = ea.id AND rel.bean_module LIKE 'Users' AND rel.deleted = 0 AND ea.deleted = 0 WHERE (LTRIM(RTRIM(ea.email_address)) LIKE 'abc@test.com')))) AND users.deleted=0 Group By users.id  ORDER BY users.date_modified
        [1] => 0
        [2] => 21
        [3] => 1
        [4] => Error retrieving User list: 
    )
    
    Mon Feb 11 03:57:06 2013 [31100][50ee1355-2e15-1474-366b-50db333bf953][DEBUG] Limit Query:  SELECT  TOP 21  users.* , ISNULL(users.first_name,'') + ' ' + ISNULL(users.last_name,'') as full_name, ISNULL(users.first_name,'') + ' ' + ISNULL(users.last_name,'') as name, jtl0.call_id call_id, jtl0.id accept_status_id, jtl0.accept_status accept_status_name, jtl1.meeting_id meeting_id FROM users   LEFT JOIN  calls_users jtl0 ON users.id=jtl0.user_id  AND jtl0.deleted=0
     LEFT JOIN  calls jt0 ON jt0.id=jtl0.call_id AND jt0.deleted=0
     AND jt0.deleted=0  LEFT JOIN  meetings_users jtl1 ON users.id=jtl1.user_id  AND jtl1.deleted=0
     LEFT JOIN  meetings jt1 ON jt1.id=jtl1.meeting_id AND jt1.deleted=0
     AND jt1.deleted=0 where (( users.id IN (SELECT rel.bean_id FROM email_addr_bean_rel rel INNER JOIN email_addresses ea ON rel.email_address_id = ea.id AND rel.bean_module LIKE 'Users' AND rel.deleted = 0 AND ea.deleted = 0 WHERE (LTRIM(RTRIM(ea.email_address)) LIKE 'abc@test.com')))) AND users.deleted=0 Group By users.id  ORDER BY users.date_modified
    Mon Feb 11 03:57:06 2013 [31100][50ee1355-2e15-1474-366b-50db333bf953][INFO] Query: SELECT  TOP 21  users.* , ISNULL(users.first_name,'') + N' ' + ISNULL(users.last_name,'') as full_name, ISNULL(users.first_name,'') + N' ' + ISNULL(users.last_name,'') as name, jtl0.call_id call_id, jtl0.id accept_status_id, jtl0.accept_status accept_status_name, jtl1.meeting_id meeting_id FROM users   LEFT JOIN  calls_users jtl0 ON users.id=jtl0.user_id  AND jtl0.deleted=0
     LEFT JOIN  calls jt0 ON jt0.id=jtl0.call_id AND jt0.deleted=0
     AND jt0.deleted=0  LEFT JOIN  meetings_users jtl1 ON users.id=jtl1.user_id  AND jtl1.deleted=0
     LEFT JOIN  meetings jt1 ON jt1.id=jtl1.meeting_id AND jt1.deleted=0
     AND jt1.deleted=0 where (( users.id IN (SELECT rel.bean_id FROM email_addr_bean_rel rel INNER JOIN email_addresses ea ON rel.email_address_id = ea.id AND rel.bean_module LIKE N'Users' AND rel.deleted = 0 AND ea.deleted = 0 WHERE (LTRIM(RTRIM(ea.email_address)) LIKE N'abc@test.com')))) AND users.deleted=0 Group By users.id  ORDER BY users.date_modified
    Mon Feb 11 03:57:06 2013 [31100][50ee1355-2e15-1474-366b-50db333bf953][FATAL] SQL Error : Column 'users.user_name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
    Mon Feb 11 03:57:06 2013 [31100][50ee1355-2e15-1474-366b-50db333bf953][DEBUG] Including generic hook file
    Mon Feb 11 03:57:06 2013 [31100][50ee1355-2e15-1474-366b-50db333bf953][DEBUG] Calling DBManager::disconnect()
    Any help on this?
    Thanks.
    Regards,
    Dhaval Darji

  2. #2
    dhavaldarji's Avatar
    dhavaldarji is offline Sugar Community Member
    Join Date
    Mar 2012
    Posts
    115

    Default Re: Problem with MSSQL and soap get entry list

    Some one using SugarCRM with mssql and found this type of failed query?
    Code:
    SELECT  users.* , ISNULL(users.first_name,'') + ' ' + ISNULL(users.last_name,'') as full_name, ISNULL(users.first_name,'') + ' ' + ISNULL(users.last_name,'') as name, jtl0.call_id call_id, jtl0.id accept_status_id, jtl0.accept_status accept_status_name, jtl1.meeting_id meeting_id FROM users   LEFT JOIN  calls_users jtl0 ON users.id=jtl0.user_id  AND jtl0.deleted=0
     LEFT JOIN  calls jt0 ON jt0.id=jtl0.call_id AND jt0.deleted=0
     AND jt0.deleted=0  LEFT JOIN  meetings_users jtl1 ON users.id=jtl1.user_id  AND jtl1.deleted=0
     LEFT JOIN  meetings jt1 ON jt1.id=jtl1.meeting_id AND jt1.deleted=0
     AND jt1.deleted=0 where (( users.id IN (SELECT rel.bean_id FROM email_addr_bean_rel rel INNER JOIN email_addresses ea ON rel.email_address_id = ea.id AND rel.bean_module LIKE 'Users' AND rel.deleted = 0 AND ea.deleted = 0 WHERE (LTRIM(RTRIM(ea.email_address)) LIKE 'abc@test.com')))) AND users.deleted=0 Group By users.id  ORDER BY users.date_modified
    Error :
    Code:
    SQL Error : Column 'users.user_name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
    This is working fine on mysql (except i remove the isnull - as it is not supported!)

    How do i now change this core code to create correct query?
    Regards,
    Dhaval Darji

  3. #3
    jmertic's Avatar
    jmertic is offline Sugar Community Manager
    Join Date
    Dec 2007
    Posts
    3,996

    Default Re: Problem with MSSQL and soap get entry list

    Can you add a bug for this at http://bugs.sugarcrm.com?
    John Mertic
    Sugar Community Manager

  4. #4
    dhavaldarji's Avatar
    dhavaldarji is offline Sugar Community Member
    Join Date
    Mar 2012
    Posts
    115

    Default Re: Problem with MSSQL and soap get entry list

    Hi John,

    Actually upgrading to SugarCE - 6.5.9 has resolved the issue. but now i am getting another error

    Code:
    Tue Feb 19 02:30:58 2013 [25795][50ee1355-2e15-1474-366b-50db333bf953][FATAL] The column 'account_id' was specified multiple times for 'a'.: SELECT TOP 21 * FROM
                                    (
                                         SELECT  project.*  ,project_cstm.end_date_c,project_cstm.project_code_c,project_cstm.billable_after_hours_c,project_cstm.esp_incident_c,project_cstm.value_c,project_cstm.actual_after_hours_c,project_cstm.sales_order_billable_hours_c,project_cstm.estimated_hours2_c,project_cstm.user_id_c as usrid,project_cstm.billable_hours_c,project_cstm.total_hours_c,project_cstm.date_worked_c,project_cstm.fixed_billable_amount_c,project_cstm.currency_id , LTRIM(RTRIM(ISNULL(jt0.first_name,'')+N' '+ISNULL(jt0.last_name,''))) modified_by_name , jt0.created_by modified_by_name_owner  , N'Users' modified_by_name_mod , LTRIM(RTRIM(ISNULL(jt1.first_name,'')+N' '+ISNULL(jt1.last_name,''))) created_by_name , jt1.created_by created_by_name_owner  , N'Users' created_by_name_mod , LTRIM(RTRIM(ISNULL(jt2.first_name,'')+N' '+ISNULL(jt2.last_name,''))) assigned_user_name , jt2.created_by assigned_user_name_owner  , N'Users' assigned_user_name_mod , accounts.name account_name, jtl3.acc_id account_id, project_cstm.account_id_c as pc_acc_id, jt4.name account_id_c , project_cstm.user_id_c, LTRIM(RTRIM(ISNULL(jt5.first_name,'')+N' '+ISNULL(jt5.last_name,''))) last_worked_by_c  , LTRIM(RTRIM(ISNULL(jt6.first_name,'')+N' '+ISNULL(jt6.last_name,''))) modified_user_name , jt6.created_by modified_user_name_owner  , N'Users' modified_user_name_mod , ROW_NUMBER()
                                        OVER (ORDER BY project.date_modified) AS row_number
                                        FROM project   LEFT JOIN project_cstm ON project.id = project_cstm.id_c   LEFT JOIN  users jt0 ON project.modified_user_id=jt0.id AND jt0.deleted=0
    
     AND jt0.deleted=0  LEFT JOIN  users jt1 ON project.created_by=jt1.id AND jt1.deleted=0
    
     AND jt1.deleted=0  LEFT JOIN  users jt2 ON project.assigned_user_id=jt2.id AND jt2.deleted=0
    
     AND jt2.deleted=0  LEFT JOIN  projects_accounts jtl3 ON project.id=jtl3.project_id AND jtl3.deleted=0
    
     LEFT JOIN  accounts accounts ON accounts.id=jtl3.account_id AND accounts.deleted=0
     AND accounts.deleted=0 LEFT JOIN accounts jt4 ON project_cstm.account_id_c = jt4.id AND jt4.deleted=0  LEFT JOIN users jt5 ON project_cstm.user_id_c = jt5.id AND jt5.deleted=0   LEFT JOIN  users jt6 ON project.modified_user_id=jt6.id AND jt6.deleted=0
    
     AND jt6.deleted=0 where ((project.date_modified >= N'2013-02-07 12:20:37' AND project.date_modified <= N'2013-02-20 10:30:56') AND (project.assigned_user_id = N'50ee1355-2e15-1474-366b-50db333bf953')) AND project.deleted=0 
                                    ) AS a
                                    WHERE row_number > 0
    when i rename and put alias for account_id it gives another error for user_id_c, you note here project_cstm.user_id_c is also being put multiple times in select list.

    Any Idea or i should add a new bug?
    Regards,
    Dhaval Darji

  5. #5
    jmertic's Avatar
    jmertic is offline Sugar Community Manager
    Join Date
    Dec 2007
    Posts
    3,996

    Default Re: Problem with MSSQL and soap get entry list

    Sounds like this is from a customization; could you back it out and see if that fixes the problem?
    John Mertic
    Sugar Community Manager

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. SOAP Entry in Database
    By Niggy in forum Developer Help
    Replies: 6
    Last Post: 2011-03-28, 05:52 PM
  2. soap get_entry_list duplicate entry
    By nbocquet in forum Developer Help
    Replies: 0
    Last Post: 2010-06-03, 03:43 PM
  3. SOAP set entry issue
    By cchiu in forum Developer Help
    Replies: 9
    Last Post: 2010-05-21, 08:21 AM
  4. SOAP Entry Script. Can you cast your eye over!?
    By Poynton in forum Developer Help
    Replies: 6
    Last Post: 2009-11-08, 08:50 PM
  5. SOAP and max_results for entry lists
    By andreasr in forum Developer Help
    Replies: 1
    Last Post: 2006-07-31, 10:45 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •