Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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: 869 | 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

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones