Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Reporting Services Extras Bug - Date Sort Problem

By Additional Articles,

SQL Server's Reporting Services comes with some pre-built reports to be able to monitor who is doing what with your Reporting Services Server. You can find these reports in the \Extras\Execution Log Sample Reports folder on the product CD-ROM (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsadmin/htm/arp_rslogfiles_v1_88gy.asp). One of the reports is titled "Todays Reports" (Todays Reports.rdl/pToday Dataset). It should show the reports and the users that executed the reports that were executed within the last day (depending on when the associated DTS package was run). However, during the month of January, the script returns December dates as the "top" dates. The reason for this is that the SQL script is doing an order by on varchar data. Microsoft wanted to have the date in the mm/dd/yy format and remove the time portion from the data, but this will not sort properly due to the way that character strings are interpreted by a sort. Example sort returned by the script provided by Microsoft:

Script:
SELECT DISTINCT CONVERT(varchar(10), TimeStart, 1) AS theDate
FROM ExecutionLogs
ORDER BY theDate DESC

Returns:
12/31/04
12/30/04
12/29/04
12/28/04
01/06/05
01/05/05
01/04/05
01/03/05

The associated script is an alternative solution that will also remove the time portion of the underlying datetime field and also sort by date rather than by the character string.

Total article views: 877 | Views in the last 30 days: 3
 
Related Articles
FORUM

URGENT: Error executing scripts......

URGENT: Error executing scripts......

FORUM

Connditional executing of script

Connditional executing of script

FORUM

Reporting Services sample reports (execution log) issue

Reporting Services sample reports (execution log) issue

FORUM

DTExec: The package execution returned DTSER_FAILURE (1)

DTExec: The package execution returned DTSER_FAILURE (1)

 
Contribute