Guest

Cisco Unified Intelligent Contact Management Enterprise

DateTime Order Issue from SQL Query Results with Upgrade from SQL Version 6.5 to 7.0

Document ID: 52760

Updated: Oct 25, 2006

   Print

Introduction

This document describes why the SQL query result order by DateTime for the Route_Call_Detail or the Termination_Call_Detail table between Microsoft SQL version 6.5 and 7.0 is different and provides a workaround in a Cisco Intelligent Contact Management (ICM) environment.

Prerequisites

Requirements

Cisco recommends you have knowledge of these topics:

  • Cisco ICM

  • Microsoft SQL

Components Used

The information in this document is based on these software and hardware versions:

  • Cisco ICM

  • Microsoft SQL Server version 6.5 and 7.0

The information in this document was created from the devices in a specific lab environment. All of the devices used in this document started with a cleared (default) configuration. If your network is live, make sure that you understand the potential impact of any command.

Conventions

Refer to Cisco Technical Tips Conventions for more information on document conventions.

Problem

When you upgrade to Cisco ICM version 4.6.2 or later, Microsoft SQL Server version 6.5 is upgraded to version 7. After the upgrade, executing the SQL query against the Route_Call_Detail or the Termination_Call_Detail table on the ICM system that runs SQL version 7 returns different results from SQL version 6.5. See this SQL query:

Figure 1: Microsoft SQL Server Query

datetime-order-sql-1.gif

When you compare the results of executing the same SQL query on the old ICM system that runs SQL version 6.5, the content is identical. However, the new results are not in the ascending DateTime order like the original results. Before the upgrade, this query returned data in DateTime order. Since the upgrade, data is not returned in DateTime order, as shown here.

Figure 2: SQL Query Results in DateTime Order

datetime-order-sql-2.gif

Solution

After you upgrade from SQL version 6.5 to version 7.0, the results of select queries completed against the Route_Call_Detail or the Termination_Call_Detail are no longer in the DateTime order. An order by clause must be inserted in order to obtain the DateTime results. This is an issue because the order by clause can add significant overhead to the Route_Call_Detail and the Termination_Call_Detail queries, which can produce very large result sets.

The order by primary key in SQL Server version 6.5 came from the old Sybase system where Microsoft SQL originated. Microsoft tightened up conformance to the SQL standard in SQL Server version 7.0 which does not guarantee an order without an order by clause in the SQL query. This is a relational database not a physical sequential file. There is no assumed sequence in a relational database like there is in a physical sequential file. Therefore, it is necessary to use an order by clause to establish a sequence in the result.

Note: This is not a Cisco issue. It is a Microsoft SQL Server standard issue.

Related Information

Updated: Oct 25, 2006
Document ID: 52760