Friday, September 24, 2010

Use iOS Address Book

Recently I have encountered an issue about using iOS Contacts or Apple's Address Book as data source for my application. The AB provides nice detail data structure for contacts, as well as views and APIs for adding, editing and deleting contacts. The issue is how I build a bridge between the AB contacts and my application.

Initially, I thought that the contacts in my app are mainly company contacts. In the AB framework, a contact record can be a person with names such first and last name, or an organization with a name, or both. Actually, when I stand back, my app's contact can be a company or a person. There should be no clear line between them. Therefore, I should not limit my data source to organization or company only.

Then I was puzzled by the issue who to present contacts from AB to my app. I don't mind to present all the contacts, but I would like to provide a convenient way to present a sub-set of contacts. I don't feel that it is good way to divide contacts into persons and companies.

Further exploration on AB framework and studying some example codes, I realized that contacts can be categorized into groups. Groups are defined by user. A group in AB is also record which has only one property kABGroupNameProperty. That opened my eyes. Why not let users to define their contact collection in group? Then my app will use that group as data source. Now I think that it's the best solution.

With this discovery, I find out a very useful and simple application case. By searching for the case in iTunes, I found a couple of apps. The app is very simple. Its main feature is for a user to manage or organize contacts into groups. I could work out a different and simple app. It is not hard to do it. I may get it done in a very short period of time. Sit back and rethink about this struggle period, my past pause and hard time are not a waste. You never know that a bright, wide and smooth pathway will suddenly present to your front when you think you are driving into a no-exit road.


Thursday, September 23, 2010

Wrapper Helper Classes for ABAddressBook

I have been looking for ways to integrate Address Book - Contracts to my application. Although Address Book Programming Guide for iOS provides information, APIs and example codes, all those resources are far away from what I need.

Then I found this ABContactHelper project on github. The project contains several wrapper ObjC classes to wrap CF classes: ABContact for contact, ABGroup for group and ABContractHelper for CF functions. I like those helper classes. The only thing I found so far I don't like is the contactName property. It formats a name in western way, not localized. Instead, the compositeName is an alternative choice, which just uses iPhone's AddressBook C function to return a composite name.

Those helper ObjC classes provide a good overview of ABAddress Book framework.


Monday, September 13, 2010

Backup SQL Server by Using PowerShell Scripts

There are many ways to back up SQL server databases. Normally it is done through DBA to create a scheduled job on SQL Server. However, this requires a full version of SQL server. For Microsoft SQL Server 2005/2008 Express version, one of its limitation is that the free version does not provide job scheduling. During my past working experience, I fount several ways to the backup.

The basic requirements for the database backup job are:

  • The job can be scheduled as an automation job without user interaction
  • The job is preferred in script for each maintenance, for example, database, user/pwd, and backup location changes.
  • The backup job is centralized on one place so that several SQL databases are backed up on one central location.

SQL Server Utility

The first tool I found is to use SQL management tool SQLMaint.exe. It comes with SQL Server Management Studio 2005. Here is the technical information of this tool. For example, I use the following batch commands to do a database backup for MyDatabase on SQL server PC001\sqlexpress:

@echo off
REM This path is SQL binary folder for sql maintanance app
pushd "C:\MSSQL2005\MSSQL.2\MSSQL\Binn"
sqlmaint.exe -S PC001\sqlexpress -U dbbackup -P pwd
-D myDatabase -CkDB -BkUpOnlyIfClean
-Rpt C:\MSSQL_Backup\Log\PC001MyDatabase_backup_log.txt
-VrfyBackup -BkUpMedia DISK
-BkUpDB C:\MSSQL_Backup\DB
-DelBkUps 3days -DelTxtRpt 3weeks
-HtmlRpt C:\MSSQL_Backup\HTMLRpt\PC001MyDatabase_backup_report.html
-DelHtmlRpt -3weeks
@echo on

The batch commands work fine with only SQL Server 2005 database; however, it does not work for SQL Server 2008 or Expression databases.

PowerShell Solution One

By googling web, quickly I found an alternative way to do the job. Those scripts are based on classes. That's very cool! Based on those scripts and my requirement, I created a function. This function does database backup by either Windows log-on user credential, or SQL Server user credential. In either case, the credential user should be configured in the SQL server with db_backupoperator permission.

The function takes following parameters:

  • SQL Server Name, for example, PC001\SQLEXPRESS
  • database name, for example, myDatabase
  • folder: a path on SQL server where the backup file will be saved
  • SQL user name. This is optional. If it is not supplied, the current Windows log-on user's credential will be used
  • password for the above SQL server user. Optional

Here is the script:

function BackupSQLDb (
[string]$p_sqlServerName = ${throw "Missing sql server name "},
[string]$p_db = ${throw "Missing parameter database name"},
[string]$p_DestFolder = ${throw "Missing parameter destination folder"},
#load assemblies
#note need to load SqlServer.SmoExtended to use SMO backup in SQL Server 2008
#otherwise may get this error
#Cannot find type [Microsoft.SqlServer.Management.Smo.Backup]: make sure
#the assembly containing this type is loaded.

LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
#Need SmoExtended for smo.backup
LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null
LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") | Out-Null
LoadWithPartialName("Microsoft.SqlServer.SmoEnum") | Out-Null

$sqlServername = $p_sqlServerName

$sqlUserName = $p_userName
$sqlPWD = $p_password
#create a new server object
$server = New-Object ("Microsoft.SqlServer.Management.Smo.Server")
-ArgumentList $sqlServername # "PC001\sqlexpress"
$backupDirectory = $p_DestFolder
#display default backup directory
Write-Debug ("Default Backup Directory: {0}" -f $backupDirectory)
if ( $sqlUserName -ne $null -and $sqlUserName.length -gt 0 ) {
$securePassword = ConvertTo-SecureString $sqlPWD -AsPlainText -Force

$db = $server.Databases[$p_db]
$dbName = $db.Name
if ( $dbName.length -gt 0 )
$timestamp = Get-Date -format yyyyMMdd_HHmmss
$backupFile = $backupDirectory + $dbName + "_" + $timestamp + ".bak"
Write-Output ("Start backup database ""{0}"" to ""{1}"""
-f $dbName, $backupFile)

$smoBackup = New-Object ("Microsoft.SqlServer.Management.Smo.Backup")
#BackupActionType specifies the type of backup.
#Options are Database, Files, Log
#This belongs in Microsoft.SqlServer.SmoExtended assembly
$smoBackup.Action = "Database"
$smoBackup.BackupSetDescription = "Full Backup of " + $dbName
$smoBackup.BackupSetName = $dbName + " Backup"
$smoBackup.Database = $dbName
$smoBackup.MediaDescription = "Disk"
$smoBackup.Devices.AddDevice($backupFile, "File")
Write-Output ("Finished backup database ""{0}"" to ""{1}"""
-f $dbName, $backupFile)
else {
Write-Output ("ERROR: invalid database name or database does not exist: {0}"
-f $p_db)

I only tested this function on Windows XP and Windows 2008 Server with SQL Server 2008 Express installed.

PowerShell Solution Two

The above script function works fine with SQL Server 2005 and 2008 and Express versions. However, it does not work for SQL Server 2000! I realized one day that there is option to obtain the backup TSQL scripts from SQL Server Management studio. I verified that in the TSQL command stays same in all SQL Server versions: 2000, 2005 and 2008. How about to make a connection to SQL server and run the TSQL command? Quickly I come to the solution two.

function BackupSQLDb (
[string]$p_sqlServerName = ${throw "Missing sql server name "},
[string]$p_db = ${throw "Missing parameter database name"},
[string]$p_DestFolder = ${throw "Missing parameter destination folder"},
$timestamp = Get-Date -format yyyyMMdd_HHmmss
$backupFile = $p_DestFolder + $p_db + "_" + $timestamp + ".bak"
$backupDescription = "Full backup of {0}" -f $p_db
Write-Output ("Start backup database ""{0}"" on SQL Server({2}) to ""{1}"""
-f $p_db, $backupFile, $p_sqlServerName)
# TSQL command for backup
-f $backupFile, $backupDescription, $p_db
$con = $null
if ( $p_userName -ne $null -and $p_userName.length -gt 0 ) {
# Use SQL user/password
$con = "Data Source={0};Initial Catalog={1};User ID={2};Password={3}"
-f $p_sqlServerName, $p_db, $p_userName, $p_password
else {
# Use Windows log on credential
$con = "Data Source={0};Integrated Security=SSPI;Persist Security Info=True;Initial Catalog={1}"
-f $p_sqlServerName, $p_db
Write-Output ("Connecting to {0} ..." -f $con)
$cn = new-object System.Data.SqlClient.SqlConnection ($con)
$cmd2 = new-object "System.Data.SqlClient.SqlCommand" ($tsqlCmd, $cn)
$result = $cmd2.ExecuteNonQuery()
Write-Output ("Backup database is done with result {0}" -f $result)

I did similar tests for this function. It seems that it works for all SQL Servers, 2000, 2005, and 2008.


SQL Server PowerShell : How to Backup SQL Server Databases Using SMO and PowerShell

SQL Server PowerShell : Basics – Connecting to SQL Server

JBs Powershell blog: SQL Queries.


Saturday, September 11, 2010

Old Windows Tool: XCOPY

XCOPY is an old Windows Tool. I remember that I first used this tool to copy files with sub-directiories when my computer was DOS based one. It is a nice tool to copy files. At my current work, this tool is also used to copy only most recently changed files as a way to back files. The option is /D/Y.

XCOPY source [destination] /D /Y

In the past week, I was working on a project to migrate services and scheduled jobs to a new Windows Server 2008 box. One simple task was to move a XCOPY job, copying files from a mapped Windows box to an imported UNIX based network drive. To my surprise, the same command does not work any more. It always copy whole files again, about several GBs. Somehow, XCOPY does not work for CIFS type network drive.

It is possible that the file date time stamp retrieved from the network drive might be truncated after seconds, while the full date time stamp are available in Windows file system. As a result, this may lead to always copy new files to the destination. I got this explaining from my SF question.

I also tried to use ROBOCOPY. It did not work neither. Finally, one of my network team member suggested me back to the old tool XCOPY with different options. It works again! Here is the command:

XCOPY source [destination] /M /Y /C

/M for copying only archived files and clear archive flag after the copy. I added /C option to let copy process to continue regardless of any failure.

The reason I like to use XCOPY is that it is more tolerance or not so aggressive. Even a file is hold by another process, it still does the copy. I tried SyncToy tool and it does not work with files being accessed by another process. My backup job is doing realtime file backup and those files are constantly updated by realtime process. Therefore, /C option lets me to continue the backup process and try it again on the next day.

I am happy with the alternative solution.