Auxiliary Months of Year Table - SQL

IF OBJECT_ID('dbo.CalendarMonth') IS NOT NULL
DROP TABLE dbo.CalendarMonth;
GO

CREATE TABLE dbo.CalendarMonth
(
mon nvarchar(10) NOT NULL
);

INSERT INTO dbo.CalendarMonth(mon)
SELECT 'January ' UNION ALL
SELECT 'February ' UNION ALL
SELECT 'March ' UNION ALL
SELECT 'April ' UNION ALL
SELECT 'May ' UNION ALL
SELECT 'June ' UNION ALL
SELECT 'July ' UNION ALL
SELECT 'August ' UNION ALL
SELECT 'September ' UNION ALL
SELECT 'October ' UNION ALL
SELECT 'November ' UNION ALL
SELECT 'December '

If, for example you are trying to provide an output of sales data for every month and the sale table doesn't have records for all 12 months, by creating an OUTER JOIN to the Calendar table, each month is returned.

IF OBJECT_ID('dbo.yearsales') IS NOT NULL
DROP TABLE dbo.yearsales;
GO
CREATE TABLE dbo.yearsales
(
sales int NOT NULL,saledate smalldatetime NOT NULL
)


INSERT INTO dbo.yearsales (sales, saledate)
SELECT '4','Jan 1 2009 12:00AM' UNION ALL
SELECT '5','Feb 1 2009 12:00AM' UNION ALL
SELECT '1','Apr 3 2009 12:00AM' UNION ALL
SELECT '11','May 5 2009 12:00AM' UNION ALL
SELECT '1','Jun 15 2009 12:00AM' UNION ALL
SELECT '4','Aug 5 2009 12:00AM' UNION ALL
SELECT '3','Oct 12 2009 12:00AM' UNION ALL
SELECT '17','Nov 2 2009 12:00AM' UNION ALL
SELECT '19','Dec 14 2009 12:00AM'

SELECT yearsales.sales, yearsales.saledate, DATENAME(month, yearsales.saledate) AS salesmonthname, CalendarMonth.mon
FROM yearsales RIGHT OUTER JOINCalendarMonth ON DATENAME(month, yearsales.saledate) = CalendarMonth.mon

Datetime types in SQL - SQL

You cannot store only the date or time portion.

If only the date is specified, 00:00:00.000 is stored in the time portion.

If only the time is specified, 1900-01-01 is stored in the date portion.

For example,

SELECT CAST ('20100526' as datetime)

SELECT CAST ('12:47:59:009' as datetime)

SOS! - Please help! - Report Manager times out - ***CULPRIT - AVG antivirus LinkScanner***

Any SQL Server installation/admin experts out there?


I have a development environment on my home PC running:

Windows 7 Ultimate 64-bit

(Windows Ultimate was installed as a 'clean copy' via an upgrade. The previous OS, Vista had SQL Server and all components including Reporting Services running successfully)

IIS spec

Prior to installing SQL Server I ensured the following IIS windows features were enabled:

-Internet Information Services
- -Web Management Tools
- - -IIS 6 Management Compatibility
- - - -IIS 6 Scripting Tools
- - - -IIS 6 WMI Compatibility
- - - -IIS Metabase and IIS 6 configuration compatibility
- - -IIS Management Console
- -World Wide Web Services
- - -Application Development Features
- - - -.NET Extensibility
- - - -ASP.NET
- - - -ISAPI Extensions
- - - -ISAPI Filters
- - -Common Http Features
- - - -Default Document
- - - -Directory Browsing
- - - -HTTP Errors
- - - -HTTP Redirection
- - - -Static Content
- - -Health and Diagnostics
- - - -HTTP Logging
- - - -Request Monitor
- - -Security
- - - -Request Filtering
- - - -Windows Authentication




















Navigating to http://mycomputername successfully displays the IIS 7.0 welcome screen

SQL Server spec
All components of SQL Server 2005 Developer Edition 64-bit were then installed successfully, followed by SP3.
I also installed SP1 for Visual Studio 2005 followed by SP1 update.

A default configuration of SSRS was also implemented.

Each service is running under the following:
SQL Server - LocalSystem
SQL Server Agent - LocalSystem
SQL Server Analysis Services - LocalSystem
SQL Server Browser - LocalSystem
SQL Server FullText Search - LocalSystem
SQL Server Integration Services - NT Authority\NetworkService
SQL Server Reporting Services - NT Authority\NetworkService

Shared Memory & TCP/IP enabled







Reporting Services Configuration

All relevant parts are correctly configured including the following:

Windows Service Identity:
Sevice Account - NT Authority\NetworkService
Built-in Account - Network Service

Web Service Identity:
ASP .NET Service Account - NT Authority\NetworkService
Report Service & Report Manager run under ReportServer application Pool

IIS Manager

ReportServer Application Pool runs under the Classic Managed Pipeline mode and NetworkService Identity.

Internet Explorer settings
Running IE8

Local intranet settings:
Include all local (intranet) sites....
Include all sites that bypass....
Include all network paths....

Set my login which has admin rights Content Manager in Report Manager

PROBLEM

I can view and render reports via reportserver
I can view reports but CANNOT render reports in report manager. I get the 'Report is being generated' message and it eventually times out with the message 'The underlying connection was closed: An unexpected error occurred on a receive.
Unable to read data from the transport connection: An existing connection was forcibly closed by the remote host.
An existing connection was forcibly closed by the remote host'

I've tried with various reports, some that pull data from my local db, some that have parameters and some that simply display text. NONE render in report manager, although the report paramaters are successfully retrieved from the local db.
I tested this using Mozilla firefox and although the layout is poor the reports render successfully.

I'm at a complete loss!
I've tried reinstalling the OS and SQL Server.
Tried setting up a new application pool in IIS using my login account and Classic managed pipeline mode.
In Reporting Services config I've tried using my login account for Windows Service Identity and ASP .NET Service Account.
All to no avail.


NIC & Wireless settings
This may/may not have any bearing on matters, I'm not sure!
When in the process of upgrading from Vista to Windows 7, it mentioned that the DELL Wireless WLAN Card Utility was not compatible with Windows 7 and I removed it.

Also, I was directed to this post (many thanks Jerry Nee) http://blogs.msdn.com/sql_protocols/archive/2008/04/08/understanding-connection-forcibly-closed-by-remote-host-errors-caused-by-toe-chimney.aspx regarding network adapters. Not sure if this is relevant seeing as I'm running a development system all on one PC. But, Im not sure! Please advise!


I hope someone can help me rectify this issue as I've been at it now for a good ten days!

I'm willing to flatten the whole lot and start again if recommended, if someone can state the best setup required for a 'stand-alone' development system such as mine.

Links to my forum posts are at:
http://www.sqlservercentral.com/Forums/Topic917776-162-1.aspx
http://social.technet.microsoft.com/Forums/en/sqlreportingservices/thread/e976a8c9-a8a0-4223-8aec-36a3899a8e77
http://forums.iis.net/t/1167401.aspx



Many thanks,
Dom Horton

==============================
UPDATE
==============================


I've managed to solve this problem after many frustrating hours!
I'm running the free edition of AVG 9.0 and by disabling the 'LinkScanner' component I've got things to work. Funny thing was that I was sure I'd tried disabling the anti-virus software as part of my process of elimination!


==============================
Further info
==============================


http://forums.avg.com/gb-en/avg-forums?sec=thread&act=show&id=151271

Nth largest value - SSRS

I recently had a query regarding replicating Crystal Reports nth largest functionality in Reporting Services (hope this helps, Andy!).

For example, In Crystal Reports, its simply a case of using Insert>>Summary

I believe the most efficient way to go about is to use a subquery in T-SQL and incorporate into a dataset.

Using the AdventureWorks db, the first query uses the ROW_NUMBER function to sequentially order rows, purely for this examples sake. (ROW_NUMBER is only available in SQL Server 2005 onwards)

SELECT SalesPersonID, SalesYTD, ROW_NUMBER() OVER(ORDER BY SalesYTD desc) as rownum
FROM AdventureWorks.Sales.SalesPerson

The second query, using a subquery, firstly selects the top 3 SalesYTD values and then selects the smallest of these 3, the third largest of all values.

SELECT TOP 1 SalesPersonID, SalesYTD FROM
(SELECT TOP 3 SalesPersonID, SalesYTD FROM AdventureWorks.Sales.SalesPerson ORDER BY SalesYTD desc) as Sales
ORDER BY SalesYTD asc


By simply replacing the 3 in the subquery by n, the nth largest value can be obtained.

Select All option - SSRS

It's often necessary to have a 'select all' option when using parameters in Reporting Services.
Here's a way to do it:

For my report I've created three datasets.

1) The first dataset, named 'Package' creates the parameter values, using UNION command to adjoin a '** SELECT ALL **' value to those obtained from a database.

SELECT '** SELECT ALL **' as PACKAGE

UNION

SELECT PACKAGE.PACKAGE as PACKAGE

FROM PACKAGE

order by PACKAGE.PACKAGE

2) The second dataset, named 'SelectAll' is used to populate the Default Value for the parameter.

SELECT '** SELECT ALL **' as PACKAGE

These two datasets are then used to populate the Report Parameters window as below:

3) The third parameter, in this case named 'ChangedRequest', provides the select logic.

SELECT .....

FROM .....

WHERE PACKAGE.PACKAGE = @Package or '** SELECT ALL **' in (@Package)

order by PACKAGE.PACKAGE

Which results in something like this: