I have tried and tried and can't seem to get this view to build since upgrading to SS2008R2. If I manually MAKE a view it will drop it (when I remove the --), but I can't get it to update or create a view. Any ideas?

ALTER PROCEDURE [dbo].[refresh_view_for_inspection_tables]

AS

BEGIN

SET NOCOUNT ON;

DECLARE @sql nvarchar(max);

DECLARE @tables TABLE (

table_id int PRIMARY KEY CLUSTERED

);

INSERT INTO @tables

VALUES (OBJECT_ID('BeachInspection')),

(OBJECT_ID('BodyArtInspection')),

(OBJECT_ID('CampInspection')),

(OBJECT_ID('ChildCareInspection')),

(OBJECT_ID('FoodInspection')),

(OBJECT_ID('GeneralInspection')),

(OBJECT_ID('HotelInspection')),

(OBJECT_ID('LaborCampInspection')),

(OBJECT_ID('MobileHomeInspection')),

(OBJECT_ID('PoolInspection')),

(OBJECT_ID('SchoolInspection')),

(OBJECT_ID('SepticRemovalInspection')),

(OBJECT_ID('SummerCampInspection')),

(OBJECT_ID('WaterInspection'));

SELECT @sql = STUFF((

SELECT ',' + QUOTENAME(name) AS [text()]

FROM sys.columns

WHERE object_id IN (

SELECT table_id

FROM @tables

)

GROUP BY name

FOR XML PATH('')

),1,1,SPACE(0));

SELECT @sql = STUFF((

SELECT 'UNION ALL ' + char(10) + 'SELECT ' + @sql + ' FROM ' + name + CHAR(10) AS [text()]

FROM sys.tables

WHERE object_id IN (

SELECT table_id

FROM @tables

)

FOR XML PATH('')

),1,10,SPACE(0));

--EXEC('IF OBJECT_ID(''InspectionListAutoTest'',''V'') IS NOT NULL DROP VIEW InspectionListAutoTest;');

SET @sql = 'CREATE VIEW [InspectionListAutoTest] AS ' + @sql;

EXEC(@sql);

END