Problem running an SQL Update query in a table script using CreateQueryObj() Exec()

SOLVED

Hi all, I have a problem running a query to update a Company record from inside of the Comm_Link (secondary) entity. The query starts in Comm_Link > grabs Communication details > grabs Company details > updates a new custom field in the Customer entity.

The query runs on a local version I have installed without proble, , but when it is being put live it times out. I get a  SafeCall Exception:SQL Error in the log (with no other explanation) and the update doesn't run. I have enabled the log() function which tells me the query is timing out. Pulling the SQL that is generated out and running it on the server runs without problem, assuming the CRM database has been selected and is in scope. I have tried using fully qualified names for the database table and still it proves problematic. I have also tried running a update query on other custom fields, but this fails.

One thing to note is SQL server / the db exists on a different physical server to the CRM install. I have read that this could cause problems and I might need to pass a database name as a second argument to the CreateQueryObj() to get it to work. I can't find any documentation on forming the string to pass it, nor can I find examples, and the few examples I have tried are likely wrong.

The database is registered successfully as the default database within Sage CRM however. 

The code running looks like the following:

var sql = "UPDATE Company SET Company.comp_NextCallDue = '" + dateAsString + "' WHERE Company.comp_companyId = " + companyId;
var updateCompanyQuery = CRM.CreateQueryObj(sql"CRM");
updateCompanyQuery.ExecSQL();
dateAsString looks like '2020-10-09 00:00:00.000' and is a date only field. 

Any help or suggestions would be gratefully appreciated. 

Best regards, Paul 

  • 0

    You probably should cast the date. Try running a SQL trace when you run this script. If you don't know how to do that, I would suggest looking up how to use SQL Profiler.

    To test this, dump the sql var string to the logs so you can see what it is actually trying to send to the database. Then try running that manually in SQL management studio. If it fails, you should get a useful error message.

    The date value could be your issue here. I've tried everything to paste the SQL in here but it won't let me. I guess the editor is a bit stupid.

    The dateAsString may cause an issue because it might not be clear whether the date you're sending is YYYY-MM-DD or YYYY-DD-MM. My guess is the former but then dates are a nightmare when handling them from a web app to a back end server. When doing a cast, I find if the date is written something like '10 january 2020' then casting in SQL won't get it wrong.

  • +1 in reply to Vega
    verified answer

    Thank you for the input, I had done all of those things before posting - but still had the issue.

    I have found the answer however this morning: The script just would not run from the Comm_Link entity. I don't know why this is so seeing as though it did so on my local test development environment and it worked fine, but when the solution was pushed live it would not run. I made a few changes and transferred it into the Communication entity and it worked without problem.

    If anybody could shed any light on why it wouldn't run from Comm_Link, I would be grateful.

    Thank you again