External Secondary Entity SQL DB List failing queries because of lowercased column names

  • Hi all,

    As the title suggest, I have Sage CRM 5.6 linked up to an external SQL DB and I setup a List inside the CRM with one of the tables. All its columns show up in the CRM so the connection is correctly established.

    Unfortunately Sage CRM is automatically lowercasing all the column names so when it generates a query which I execute inside an asp scripted screen, it fails because the DB is case sensitive when it comes to column names.

    Changing the entire db to lowercase or rendering it case insensitive is not an option for me sadly.

    After lots of searching online I've come accross something called "Hidden Parameters" which I believe alludes to playing with registry values for Sage CRM. There is one setting called "CaseSensitive" which I suspect might help me out. Unfortunaltely there is no instructions on how to properly set it.

    I would greatly appreciate any suggestions and or any help with my problem.

    Karim

  • Please see this article

    community.sagecrm.com/.../database-case-sensitivity-and-connecting-to-an-external-case-sensitive-database.aspx


  • Jeff,

    Many thanks for the quick and informative response. It is greatly appreciated. I've read your article but I do not know where to find the "Custom_SysParams table". Could you please point me to it?

  • Hi

    the easiest way for me was to work with views to the external database.

    Create first of all the connection to the database. then connect the tables.

    after this create a view and work with alias in the view.

    regards

    Christian

  • Karim

    The Custom_SysParams table is in the Sage CRM database.  You will need to use a tool like the SQL Server Management Studio to allow you to run queries against it.  Most of the records stored in the custom_sysparams table are controlled through the CRM interface however some like CaseSensitive have to be access at the database level.

    BUT my point in the article is that you shouldn't really change you database settings when you can use the technique that Christian mentions above.  You can create a view that makes all the columns lowercase.

    e.g.

    CREATE VIEW cSuppliers AS

    SELECT

    CompanyName as companyname,

    ContactName as contactname

    FROM northwind..suppliers


  • Thank you very much Jeff and Christian for your kind help.

    I am very new to this environment as I come from a pure C++ development background. Someone in my office recognized the name and pointed me to the table. We flipped the parameter to yes and it worked.

    I agree with both you that creating the view with aliases to the column names is a much more elegant and preferable way to do it. Unfortunately I am also very new to the world of ASP scripting and I still need to familiarize myself with the CRM/eWare api. So I am still learning on how to do all this by looking at the code already in place within SageCRM.

    Is there any documentation and/or tutorials that you guys are aware of that might help me quickly get up to speed on all this?

    Many thanks,

    Karim

  • Karim

    Hum....  If I were you my starting point would be to contact 1) The partner who implemented Sage CRM for you, 2) The Sage team in Canada, where I think you are based.

    But I would also make sure that I used the article that have been written over time on this site.  These are not really tutorials but rather examples on how specific problems can be solved or approached.

    The Hints,Tips and Tricks blog is really aimed at partners but is open to the public

    community.sagecrm.com/.../default.aspx

    And I another useful link is the online Developer Guide.

    www.sagecrm.com/developerhelp

    As you explore the help information you will find links at the bottom of the help page to articles on this site which take the subject further.


  • Jeff,

    Your help has been invaluable and I thank you again for all you've done.

    Karim

  • Jeff:  I realize this is a really old article.  However, I wanted to ask about creating this view.  It has always been my understanding that views could not be created on external databases.  With that said, is the view below possible because northwind is another SQL database?

    CREATE VIEW cSuppliers AS

    SELECT

    CompanyName as companyname,

    ContactName as contactname

    FROM northwind..suppliers

    I need to create views on a database that is not another SQL database.  If I created a linked server for that database, would I be able to create queries for my external table by referencing the linked server similar to how you have created the example above?

    If so, this might resolve a lot of data issues for me! :-)

    As always -- Thank you for your assistance!

  • Hello michelle

    did you look at this article ?

    community.sagecrm.com/.../building-cross-database-views-for-reporting.aspx

     

    Kannan Srinivasan

  • If you can link the server and query via 3 dot notation this can be queried in a view from crm. Sometimes it’s then easier to add a new table to crm by the reference the new view in crm that it is to try to reference the linked server via 3 dot notation.

    So, create a view against a suitable crm entity that reference the linked server. Use the view to add a new table via tables and database as a new entity.

    Sage CRM specialist and all round fan! Please feel free to follow me on twitter @dannycrm