Ben Hamilton

Category Archives: SQL

When SugarCRM Case Closed

Using the cases.date_modified is not the same as the date a case is closed, as the case may have been modified after it was closed. Thus, the query below will return the date the case was transitioned to ‘Closed’ provided the cases.status field is being audited. select CONVERT_TZ(cases_audit.date_created,’+00:00′,’+10:00′) as ‘date_closed’, accounts.name as ‘account’, cases.case_number, cases.name […]

Also posted in SugarCRM | | Tagged , | Comments Off on When SugarCRM Case Closed

Tracking down long running queries in SQL

I’ve had a client whose Linux Server (CentOS) gradually gets slower and slower, then falls over. By logging into the MySQL command line, we were able to see that there were some long running queries that never end. show processlist; returns | Id | User | Host | db | Command | Time | State […]

Also posted in *nix, SugarCRM | | Tagged , | Comments Off on Tracking down long running queries in SQL

How long running are those queries?

At the MySQL commandline, you can enter show processlist; and see what processes are running. If you’re tracking down some problematic code and you’ve got a query (or set of queries) that don’t end, consume CPU cycles and generally bog everything down to a crawl, it can be helpful to know what time you’ve run […]

Also posted in *nix | | Tagged , | Comments Off on How long running are those queries?

How to backup SugarCRM from the command line

From the linux command line, these commands let you backup and restore a SugarCRM database. Update 2016-04-26: Just saw this SugarCRM KB Providing a Backup Without Sensitive Data which is really useful. Thanks Jared. Firstly, for a proper back up of SugarCRM you’ll need two files, one containing the application files, one containing the SQL database. […]

Also posted in *nix | | Tagged , , , , | Comments Off on How to backup SugarCRM from the command line

Find Duplicate Contacts in SugarCRM

Find duplicate contact records, by contact name, in SugarCRM using MySQL.

| Tagged , , | Comments Off on Find Duplicate Contacts in SugarCRM

SQL Query ORDER BY last_name, then first_name

This snippet of MySQL code shows how to concatenate two fields into one column, also shows how to reverse the concatenation and order by last name then first name.

| Tagged , | Comments Off on SQL Query ORDER BY last_name, then first_name

What version of MySQL is running?

On a CentOS 6.x linux box, typing the following will show the MySQL version mysql -v or if not the root user, then mysql -u root -p -v (Which says user the root user, prompt for the password, then get the version).

| Tagged , | Comments Off on What version of MySQL is running?

Sql snippet to show just opportunities from current month

I’m needing to do a sql query to show just the opportunities from the current month, the sql snippet for the WHERE clause to do this is the following: WHERE MONTH(opportunities.date_closed) = MONTH(NOW()) and YEAR(opportunities.date_closed) = Year(now())

| Tagged , , | Comments Off on Sql snippet to show just opportunities from current month

Why keeping data clean in a database is important

Every user of a database is responsible for ‘gardening’ the database, removing the ‘weeds’, watering and maintaining it. Otherwise this sort of thing occurs. Dad gets OfficeMax mail addressed ‘Daughter Killed In Car Crash’

Also posted in ACT!, SugarCRM | | Tagged | Comments Off on Why keeping data clean in a database is important

Who should pay for Microsoft SQL installations that go wrong?

The day is almost over, so I’ll fill you in on some of what occurred today, as it relates directly to this post by Mike at GLComputing. This was originally written as a comment to his post and kinda grew, so I’m posting it here in full. SQL 2008 Express R2 has finally let me […]

Also posted in ACT!, Microsoft | | Tagged , , , | 1 Comment

SQL 2005 Versions

I have to thank Kevin Chieff (ACT! guru) for pointing me to this link on how to tell the difference between versions of Microsoft SQL 2005 – and the MS page it points to re the SERVERPROPERTY (Transact-SQL) command. The number of times I’ve had to work out just which version of SQL is running […]

Also posted in ACT! | | Tagged , , | 2 Comments

Evolving the ACT

It is now two weeks in to my new digs at Evolution Marketing – and it sure is fun. Primarily I’ve been learning how to drive ACT!, software to take care of your contact management. Yesterday and today I’ve been playing with custom tables in the ACT! MS-SQL database. While ACT! looks after contacts and […]

Also posted in ACT!, General, Microsoft | | Tagged , , , , , , , , , | Comments Off on Evolving the ACT