SQL Server: Truncating Dates

Posted on October 20, 2010 by OZ

TRUNC is a function I used a lot when I worked with Oracle and helped me slicing my data in several different ways, and keeping the columns in date format.

That function is not available in SQL Server unless you work it around by using CONVERT or DATEPART. But both change the content to VARCHAR.

Well, several years ago I found a way to workaround the issue and keep the data in DATE format (and it took me this long to post it):

1 2	-- Truncate to the current hour SELECT DATEADD(HH, DATEDIFF(HH,0,GETDATE()), 0)

1 2	-- Truncate to the current day SELECT DATEADD(DD, DATEDIFF(DD,0,GETDATE()), 0)

1 2	-- Truncate to the current month SELECT DATEADD(MONTH, DATEDIFF(MONTH,0,GETDATE()), 0)

As per BOL, we can use any of these datepart arguments:

year (or yy, yyyy)
quarter (or qq, q)
month (or mm, m)
dayofyear (or dy, y)
day (or dd, d)
week (or wk, ww)
weekday (or dw, w)
hour (or hh)
minute (or mi, n)
second (or ss, s)
millisecond (or ms)
microsecond (or mcs)
nanosecond (or ns)

You can come up with all kinds of GROUP BYs, and filtering and keep the data in Date format at the same time.

A couple of days ago, while browsing the AskSSC community forum, a question came up: “How to get quarter start date and end date”. Because I wanted to exercise my brain a little, decided to help out and posted:

1
2
3
4
5
6
7
8
9
10
11
12
13
14

DECLARE @min_date DATETIME, @max_date DATETIME;
SET @min_date = '1/1/2009';
SET @max_date = '12/31/2010';

-- Working Vars
SET @max_date = DATEADD(QUARTER, DATEDIFF(QUARTER,0,@max_date), 0) + 1;

WITH CTE_dummy AS (
SELECT TOP(DATEDIFF(QUARTER, @min_date, @max_date) + 1) ROW_NUMBER() OVER(ORDER BY object_id) - 1 AS sequence from sys.columns
)
SELECT CASE WHEN (DATEPART(QUARTER, @min_date) + sequence) % 4 = 0 THEN 4 ELSE (DATEPART(QUARTER, @min_date) + sequence) % 4 END AS [Quarter],
DATEADD(QUARTER, sequence, DATEADD(QUARTER, DATEDIFF(QUARTER,0, @min_date), 0)) AS [Start_Date],
DATEADD(QUARTER, sequence, DATEADD(QUARTER, 1, DATEADD(QUARTER, DATEDIFF(QUARTER,0, @min_date), 0)) - 1) AS [End_Date]
FROM CTE_dummy;

For the solution I used the ROW_NUMBER() windowing function, but deals with dates truncation to tackle the core question.

Hope it helps.

Figuring out Android internals

Posted on October 19, 2010 by OZ

My first task was to change the ringtone to music I used to have in my Windows phone. A simple search let me to a guide with this information:

Music/ – Media scanner classifies all media found here as user music.
Podcasts/ – Media scanner classifies all media found here as a podcast.
Ringtones/ – Media scanner classifies all media found here as a ringtone.
Alarms/ – Media scanner classifies all media found here as an alarm sound.
Notifications/ – Media scanner classifies all media found here as a notification sound.
Pictures/ – All photos (excluding those taken with the camera).
Movies/ – All movies (excluding those taken with the camcorder).
Download/ – Miscellaneous downloads.

These are under the media folder located in the microSD card. As soon as the Android phone is plugged to a PC, a USB Mass Storage device is detected and drivers are installed.

My second task was to have the ability to browse the storage card within Android (kind of File Explorer on Windows Phone). I found Astro file manager and installed it.

My third task was to find applications I used often in other environments and install them into the device. TweetDeck, Foursquare, Skyfire, Retro Radio and Google Reader came to mind. Facebbok and Twitter apps came pre-installed.

Fourth task. Figure out why is my device losing signal in my house. This is ironic because I used to laugh at the iPhone 4 when all the articles of bad reception popped up a week after it was launched. Several sites started posting funny pics about it. Well it is just bad luck as my Windows Phone has better reception. I figured out spots where I can leave it and let it get some signal.

The situation prompt me to ask a question on Twitter. 2 mentions came back quickly pointing out about WiFi calling. This is why Twitter is so awesome!

So my fifth task was to research about Wifi Calling and try to make it work. Well this wasn’t as trivial, as I needed to follow these steps:

Download the Android SDK and install
Install USB drivers – but seems that this method did not work properly in my machine
Download proper X64 USB drivers
Download the T-Mobile Wifi-Calling App
Plug in the G2 to my PC
Enable usb debugging option in the device
Open the Device Manager, find out the new device that has no drivers and update it with the ones you just downloaded
Open the command prompt window and change the folder to “tools” under the android SDK folder
Issue adb commands:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

Microsoft Windows [Version 6.1.7600]
Copyright (c) 2009 Microsoft Corporation. All rights reserved.

C:\Users\Oscar>cd C:\Users\Oscar\Downloads\android sdk\tools

C:\Users\Oscar\Downloads\android sdk\tools>adb devices
* daemon not running. starting it now on port 5037 *
* daemon started successfully *
List of devices attached
HT09XR207992 device

C:\Users\Oscar\Downloads\android sdk\tools>adb devices
List of devices attached
HT09XR207992 device

C:\Users\Oscar\Downloads\android sdk\tools>adb install WiFiCalling.apk
2565 KB/s (3478662 bytes in 1.324s)
pkg: /data/local/tmp/WiFiCalling.apk
Success

C:\Users\Oscar\Downloads\android sdk\tools>

I installed the Wi-Fi application but seems that T-Mobile has not enabled it on their end. I will wait and post as soon as I get any success using it.

Enjoy your [soon to be] Android phone.

From Palm to Windows Phone to Android

Posted on October 18, 2010 by OZ

I have been a hardcore Windows Phone user (formerly Windows Mobile, and CE) for several years, mainly because it was very easy to synchronize directly with Outlook and Microsoft Exchange. The GUI has been pretty nice since the first releases and resembled Windows in a way.

Windows Phone let me transition from a Windows CE PDA (Toshiba E335) which was a great jump from the Palm m125; years 2006, 2003 and 2001 respectively. By the end of 2005 I wanted a true PDA/Phone that wasn’t as bulky as the HP iPaq. T-Mobile launched the SDA (HTC Torando) which to me was a godsend. It had Wi-Fi (WPA-PSK support) and bluetooth, running Windows Mobile 5.0. It could play MP3s, Video and synchronize with Outlook/Exchange. The form factor was similar to a dumbphone. It was one of the best phones I have ever owned and prompted me to create a web page with streaming broadcasters posted here.

In early 2007 I bought a T-Mobile Dash (HTC Excalibur) and was able to live with it for 2 years running several cooked Roms (thanks to XDA forums). I had the opportunity to run Windows Mobile versions 5.0, 6.0, 6.1 and 6.5. The latest was the most stable one and the fastest considering the outdated hardware. Many people thought it was a Blackberry.

In 2009 I bought the HTC Touch Pro 2 (HTC Rhodium) Runing Windows Mobile 6.0. I wanted to keep it stock as much as I could for warranty purposes but had problems with the clock and was never able to sync up properly. I decided to go ahead and install a cooked ROM. Since then It as been running different releases of the Energy Rom. I was happy about the capabilities of the phone but had too many OS reliability issues. A reboot every day was not out of the norm, and in some cases several of them on the same day.

Last week I decided to pursue the Android route; T-Mobile just delivered me a brand new HTC G2. The phone is by far the fastest and the most intuitive phone I have ever owned. It is running Android 2.2 and integrates seamlessly with Exchange, Gmail, Facebook and Twitter. There are tons of applications for Android and includes Text-to-Speech directions integrated within Google Maps. Android is light years ahead of Windows Phone 6.5.

I still will try a Windows Phone 7 device and have very high expectations about it.

Next post will be about the Android SDK and how to root the phone to push an application.

[Show as slideshow]

SQL Server: Migrating from 2000 EE X86 to 2008 R2 SE X64

Posted on October 13, 2010 by OZ

Today we had a production migration and brought over one of our legacy applications from a data center located up north to one located in South Florida.

The challenges:

1. The locations are disconnected and far from eachother
2. We are going up 3 releases. From 2000 to 2008 R2 (8.0 to 10.5)
3. We are Downgrading from Enterprise Edition to Standard Edition
4. We are going from a 32-bit architecture server to a 64-bit one (X86 to X64)

The solution. We separated this into 2 pieces. A pre-migration and production migration process.

Pre-Migration Process:

First, ensure that there are no enterprise edition features used. We were lucky this time as there were really none in the source database. But as an example, we got another server running 2005 EE which will be migrated to 2008 R2 SE and had partitioned tables. That is something to be taken care of before migration.

Second, ensure that the database has no corruption. A DBCC CheckDB needed to be performed on the source before backing it up.

Third, prepare for a test with a latest full backup. We FTP’d over the last full backup during a weekend and restored onto the target server running 2008 R2. We gzipped the files before transferring, but it was painfully slow still.

Fourth, restore and ensure that the databases open with no issues whatsoever. Update statistics and reindex any important table that is really defragmented. This is a challenge in Standard Edition as reindexing cannot be performed online.

Fifth, test and retest. This requires a lot of help from the Application Developers and QA Team. Ensure that functionality is intact and most importantly, that the database is always in a healthy condition (no excessive I/O reads, no high CPU utilization, etc).

Sixth, let the QA team sign-off.

Production Migration Process:

First. Plan the work. Create checklists with detailed steps to be performed before, during and after migration.

Second. Perform a full backup on the source server. We chose Saturday night. Disable the full backup job on the source server. Ensure that transaction log backups are still running at the source.

Third. Compress the backups, FTP to Florida, decompress and start restoring onto the target server.

Fourth. Bring over the transaction logs and continuously apply to the target server.

Fifth. In our environment we were able to disable outbound services and show a maintenance page on the web apps. This way the source database was accepting data but not processing it.

Sixth. Take a last transaction log backup, apply it to the target and open the databases.

Seventh. Update statistics, reindex for required objects and perform sanity check tests.

Eighth. Let the Infrastructure team make appropiate changes (like DNS) and enable services.

Ninth. Sync up any missing data. In our scenario, we were able to code an application that extracted missing transactions (just a few thousand) from the source server and apply to the target server. Let QA Test and Sign-Off.

Tenth. Perform a full backup and enable all maintenance jobs with appropriate schedules.

Notes:

Each migration will have its own steps and some will be more difficult than others. I have specified very high level bullet points of what needed to happen for this particular migration.

What if the unplanned happens:

First, every checklist should have a rollback plan. In our case, we had steps to follow in case we needed to stop the migration. On the DB side was pretty simple as we just stop restoring onto the target server, go back to the source and enable maintenance jobs. Of course there are other steps that needed to be followed by development and infrastructure members.

But today we had an interesting scenario. While recreating indexes on a 25 Million row table, the DBA stopped the SQL Agent, which was part of the process outlined on the checklist. The problem, it was stopped through the Services Panel instead of the SQL Configuration Manager. So what happened? The Clustered service freaked out and decided to perform a fail-over.

The instance started but the database status was “in recovery”. As per the SQL Log, it was going to take like 2 hours to rollback all the changes. We had 2 options; 1 to just execute the rollback plan, or 2 drop the database and restore it once more.

We decided to execute option 2 as the transaction logs were small. The bulk was restoring the full backup, but fortunately it was done in under 30 minutes; not bad for a 120 GB database.

Now how did we drop the “in recovery” database? We opened the SQL Configuration Manager, offlined the database instance, deleted the datafiles from the “in recovery” database and re-started the instance. Of course the SQL log was complaining about missing files, so we went ahead and dropped the database.

We are now live on a new 2008 R2 instance, and are current with backups. Interesting to see that with database compression a 120 GB database backup uses just 25 GB of disk space.

This was our last SQL Server 2000 instance.

SQL Server: Can I upgrade to 2008 R2 from 2008, 2005 or 2000? #TSQL2sDay

Posted on October 12, 2010 by OZ

It is known that you can go up 2 major releases. Let’s consider 2008 R2 as a “half release” as we can “floor” 10.5 to 10. That is because we can go from 8.0 (2000) to 10.5 (2008 R2). Microsoft is smart as it is allowing several costumers running SQL Server 2000 to upgrade to 2008 R2.

But there is a caveat, you cannot upgrade from SQL Server 2008 SP2 to 2008 R2 RTM if you have the option to support 15,000 partitions enabled on 2008 SP2. So you are pretty much stuck until Microsoft releases a CU or SP to address it in R2.

Make sure you test before upgrading a production environment.