Sage CRM Views

  • Hi All

    Im no expert when it comes to sql or sage crm for that matter so please excuse me if this is a stupid question, but is there a limit to the number of fields / tables that can be added / displayed in a view when created in the GUI of sage CRM

    The reason i ask is i have a view that brings several tables together already. If i try to add another table, when i save the view all i get is an sql error. I have removed a different table from the view and added the new table to the view and it saves. If try to add the previously removed table and it errors again.

    hope that makes sense

    Thanks

    Adam

     

  • Yes, and no!

    You are a bit mean because you haven't said what the SQL error is!  Does the error occur when you try and save the view in Sage CRM or when you try and use the view?  If only "SQL Error" is displayed in the interface then the real error will have been included in the Sage CRM's SQL Log.

    I suspect that the error will be similar to the error in this post community.sagecrm.com/.../3748.aspx

    There is a maximum row length that the database will want to work with

    msdn.microsoft.com/.../ms143432.aspx

    In everyday experience this will be affected by the version of your database and the version of Sage CRM.  For example if the fields being returned are nchars they take up more space than if they are nvarchars.  And in version 7.1 many fields were changed from being nchar to nvarchar.

    Have a look at the thread above, and see if that helps.  

    You may have to construct a new view that limits the number of columns returned to only those needed for they report of list that you need.


  • Hi Again Jeff

    There is no more detail about the error on screen. it is just "SQL Error". It happens when i try to save the view. Which log will the error be displayed in.

    Again, thanks for your help Jeff

    Adam

  • Adam

    If you have access to the admin screens have a look at

    Administration -> System -> Logging

    If you have the "SQL Logging Level" set to "Errors Only" then you can use the drop down list to look at the SQL logs.


  • ok, found the error and it is

    CREATE VIEW failed because column 'field_name' in view 'viewname' exceeds the maximum of 1024 columns

    I presume the max number of columns cant be extended, and unless we upgrade to 7.1 where the varchar fields will make a difference then my only option is to be more specific with the fields that i include in the view

    Thanks