Hi.

I have problem with one query with syntax ROLLUP.

If I tried query in hosting service I have this error:
Code:
'ROLLUP' is not a recognized built-in function name.
If I tried the same query in local service it's all ok.

In hosting service the version of SQL Server is:
Code:
Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) 
Oct 14 2005 00:33:37   Copyright (c) 1988-2005 Microsoft Corporation  
Standard Edition on Windows NT 6.1 (Build 7600: )
In local service:
Code:
Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (Intel X86) 
Jun 17 2011 00:57:23 Copyright (c) Microsoft Corporation 
Express Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
I can not upgrade the version sql server in hosting service...

Can you help me?
Thanks in advance...
Code:
CREATE TABLE TestTable (
	[MAT] VARCHAR (100),
	[NVE] INT,
	[NVES] INT
);

INSERT INTO TestTable ([MAT], [NVE], [NVES]) SELECT
	COALESCE ([MAT], 'Total') AS [MAT],
	[NVE],
	[NVES]
FROM
	(
		SELECT DISTINCT
			CASE
		WHEN LEFT (TZZ, 2) = 'AD'
		OR LEFT (TZZ, 2) = 'AF' THEN
			'MAO'
		WHEN LEFT (TZZ, 2) = 'AG'
		OR LEFT (TZZ, 2) = 'AH' THEN
			'MAE'
		WHEN LEFT (TZZ, 2) = 'AI'
		OR LEFT (TZZ, 2) = 'AM'
		OR LEFT (TZZ, 2) = 'AS'
		OR LEFT (TZZ, 2) = 'AO' THEN
			'MAC'
		WHEN LEFT (TZZ, 2) = 'AP'
		OR LEFT (TZZ, 2) = 'AQ'
		OR LEFT (TZZ, 2) = 'AR' THEN
			'MAS'
		ELSE
			'Total'
		END AS [MAT],
		COUNT (*) AS [NVE],
		SUM (
			CASE
			WHEN (
				[R-M] = '1'
				OR [R-M] = '2'
				OR [R-M] = '3'
				OR [R-M] = '4'
			)
			AND [DATE-V] IS NOT NULL THEN
				1
			ELSE
				0
			END
		) AS [NVES]
	FROM
		dbo_40
	WHERE
		1 = 1
	AND [TZZ] NOT LIKE 'LG%'
	GROUP BY
		ROLLUP (
			CASE
			WHEN LEFT (TZZ, 2) = 'AD'
			OR LEFT (TZZ, 2) = 'AF' THEN
				'MAO'
			WHEN LEFT (TZZ, 2) = 'AG'
			OR LEFT (TZZ, 2) = 'AH' THEN
				'MAE'
			WHEN LEFT (TZZ, 2) = 'AI'
			OR LEFT (TZZ, 2) = 'AM'
			OR LEFT (TZZ, 2) = 'AS'
			OR LEFT (TZZ, 2) = 'AO' THEN
				'MAC'
			WHEN LEFT (TZZ, 2) = 'AP'
			OR LEFT (TZZ, 2) = 'AQ'
			OR LEFT (TZZ, 2) = 'AR' THEN
				'MAS'
			ELSE
				'Total'
			END
		)
	) AS SubQs
WHERE
	1 = 1
ORDER BY
	CASE MAT
WHEN 'MAO' THEN
	1
WHEN 'MAE' THEN
	2
WHEN 'MAC' THEN
	3
WHEN 'MAS' THEN
	4
ELSE
	5
END;

SELECT
	[MAT],
	[NVE],
	[NVES]
FROM
	(
		SELECT
			[MAT],
			[NVE],
			[NVES]
		FROM
			TestTable
		UNION
			SELECT
				COALESCE ([MAT], 'Total') AS [MAT],
				SUM ([NVE]),
				SUM ([NVES])
			FROM
				TestTable
			GROUP BY
				(mat)
	) q
ORDER BY
	CASE MAT
WHEN 'MAO' THEN
	1
WHEN 'MAE' THEN
	2
WHEN 'MAC' THEN
	3
WHEN 'MAS' THEN
	4
ELSE
	5
END;

DROP TABLE TestTable;