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.
Cisco recommends you have knowledge of these topics:
The information in this document is based on these software and hardware versions:
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.
Refer to Cisco Technical Tips Conventions for more information on document conventions.
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
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
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.
The Cisco Support Community is a forum for you to ask and answer questions, share suggestions, and collaborate with your peers.
Refer to Cisco Technical Tips Conventions for information on conventions used in this document.