Finding Duplicates using SQL

While migrating old data to a new database schema I was unable to activate a PRIMARY KEY on the legacy_customer_id field because there were duplicates. It turned out that the old application did not clean the data really well, and it allowed duplicates to be created where one of the customer_ids had a space character making it unique.

I used the following query to test for others:

SELECT
    customer_id,
    COUNT(customer_id)
FROM
    customers
GROUP BY
    customer_id
HAVING
    (COUNT(customer_id) > 1);

This allowed me to find all customer_ids that had duplicates and clean things up.

Windows backup bat script using xcopy

Recently I had the need to create a bat script that could be executed by the Windows Scheduled Tasks. The purpose was to copy files from one server to another as a cheap way to backup files created by MSSQL backing up the databases. Here is the .bat file contents (cleaned up to protect sensitive data):

@echo
:: variables
set sourcedrive=D:\
set backupdrive=\\servername\d$
set backupcmd=xcopy /s /c /d /e /h /i /r /y
 
echo # # # Moving files
%backupcmd% "%sourcedrive%\directory_to_backup" "%backupdrive%\directory_to_store_backup"
 
echo # # Moveing Complete!

(NOTE: I am doing this backup via an internal network and using a user account that exists on both systems. Security may dictate that you handle this differently based on your circumstances.)

Notice that for the backupdrive I am calling another Windows server and using the d$. This would require that the Windows Scheduled Task be executed using a user that is trusted on both machines. Also you could specify a local directory on the same server if you did not need to copy the files to another server.
Continue reading Windows backup bat script using xcopy

Adding mssql capability to PHP5 on CentOS

I had a need to connect to MSSQL using PHP version 5 from a CentOS 5 server. To do this I needed FreeTDS and the module mssql in PHP. After a diligent search I found that there was no quick and easy way to install the mssql module, like “up2date install php-gd” to get GD to work from PHP.

I found a few places that had ideas and hints to make this work, but finally used the directions I found at http://www.howtoforge.com/installing_php_mssql_centos5.0 (slightly altered to fit my means) and will duplicate here so I can easily find it later.

At first I thought I needed to recompile PHP, but as it turned out I only needed to pre-compile it so that I could fetch the mssql.so, and then manually edit the php.ini to use it.

Continue reading Adding mssql capability to PHP5 on CentOS