SQL Report error

Hi,

I'm trying to run the following TSQL statement on a Windows SQL 2000 platform;

/* Space used per DB */

USE master
GO
SET NOCOUNT ON
GO
DECLARE AllDatabases CURSOR FOR
SELECT name FROM sysdatabases -- WHERE dbid > 4

OPEN AllDatabases
DECLARE @DBNameVar NVARCHAR(128)
DECLARE @Statement NVARCHAR(500)

 
PRINT ''
PRINT ''
PRINT 'Server Name: ' + @@SERVERNAME
PRINT ''

PRINT 'Report Generated on:  ' + cast(getdate() as char(20))
PRINT ''

PRINT 'Report Name: Space used per database'
PRINT ''


declare @user CHAR(40)
set @user = suser_sname()
PRINT 'Report Executed by: ' + @user
PRINT ''

FETCH NEXT FROM AllDatabases INTO @DBNameVar
WHILE (@@FETCH_STATUS = 0)
BEGIN
    PRINT N'CHECKING DATABASE ' + @DBNameVar


    SET @Statement = N'USE [' + @DBNameVar + ']' + CHAR(13)
      
        + N'declare @dbsize dec(15,0)' + char(13)
      + N'declare @bytesperpage      dec(15,0)' + char(13)
      + N'declare @pagesperMB            dec(15,0)' + char(13)

      + N'select @dbsize = sum(convert(dec(15),size))
            from dbo.sysfiles' + char(13)

      + N'select @bytesperpage = low
            from master.dbo.spt_values
            where number = 1
                  and type = "E"' + char(13)

      + N'select @pagesperMB = 1048576 /@bytesperpage' + char(13)

      + N'select  database_name = substring(db_name(),1,30),
            database_size =
                  ltrim(str(@dbsize/@pagesperMB,15,2) + " MB")'

    EXEC sp_executesql @Statement
    PRINT CHAR(13) + CHAR(13)
     
    FETCH NEXT FROM AllDatabases INTO @DBNameVar
END

CLOSE AllDatabases
DEALLOCATE AllDatabases
GO
SET NOCOUNT OFF
GO

PRINT ''
PRINT ''
PRINT 'END OF REPORT'
PRINT ''

It returns the following error:

CHECKING DATABASE BELd
Server: Msg 207, Level 16, State 3, Line 2
Invalid column name 'E'.
Server: Msg 207, Level 16, State 1, Line 5
Invalid column name ' MB'.



CHECKING DATABASE Bel
Server: Msg 207, Level 16, State 3, Line 2
Invalid column name 'E'.
Server: Msg 207, Level 16, State 1, Line 5
Invalid column name ' MB'.



CHECKING DATABASE BS
Server: Msg 207, Level 16, State 3, Line 2
Invalid column name 'E'.
Server: Msg 207, Level 16, State 1, Line 5
Invalid column name ' MB'.



CHECKING DATABASE ca
Server: Msg 207, Level 16, State 3, Line 2
Invalid column name 'E'.
Server: Msg 207, Level 16, State 1, Line 5
Invalid column name ' MB'.



CHECKING DATABASE CAO
Server: Msg 207, Level 16, State 3, Line 2
Invalid column name 'E'.
Server: Msg 207, Level 16, State 1, Line 5
Invalid column name ' MB'.



CHECKING DATABASE CSS_W
Server: Msg 207, Level 16, State 3, Line 2
Invalid column name 'E'.
Server: Msg 207, Level 16, State 1, Line 5
Invalid column name ' MB'.



CHECKING DATABASE CSS
Server: Msg 207, Level 16, State 3, Line 2
Invalid column name 'E'.
Server: Msg 207, Level 16, State 1, Line 5
Invalid column name ' MB'.



CHECKING DATABASE CT
Server: Msg 207, Level 16, State 3, Line 2
Invalid column name 'E'.
Server: Msg 207, Level 16, State 3, Line 5
Invalid column name ' MB'.



CHECKING DATABASE CW
Server: Msg 207, Level 16, State 3, Line 2
Invalid column name 'E'.
Server: Msg 207, Level 16, State 1, Line 5
Invalid column name ' MB'.



CHECKING DATABASE D
Server: Msg 207, Level 16, State 3, Line 2
Invalid column name 'E'.
Server: Msg 207, Level 16, State 1, Line 5
Invalid column name ' MB'.



CHECKING DATABASE FRA
Server: Msg 207, Level 16, State 3, Line 2
Invalid column name 'E'.
Server: Msg 207, Level 16, State 1, Line 5
Invalid column name ' MB'.



CHECKING DATABASE Jon's DB
Server: Msg 207, Level 16, State 3, Line 2
Invalid column name 'E'.
Server: Msg 207, Level 16, State 1, Line 5
Invalid column name ' MB'.



CHECKING DATABASE LON
Server: Msg 207, Level 16, State 3, Line 2
Invalid column name 'E'.
Server: Msg 207, Level 16, State 1, Line 5
Invalid column name ' MB'.



CHECKING DATABASE master
Server: Msg 207, Level 16, State 3, Line 2
Invalid column name 'E'.
Server: Msg 207, Level 16, State 1, Line 5
Invalid column name ' MB'.



CHECKING DATABASE model
Server: Msg 207, Level 16, State 3, Line 2
Invalid column name 'E'.
Server: Msg 207, Level 16, State 1, Line 5
Invalid column name ' MB'.



CHECKING DATABASE mon
Server: Msg 207, Level 16, State 3, Line 2
Invalid column name 'E'.
Server: Msg 207, Level 16, State 1, Line 5
Invalid column name ' MB'.



CHECKING DATABASE msdb
Server: Msg 207, Level 16, State 3, Line 2
Invalid column name 'E'.
Server: Msg 207, Level 16, State 1, Line 5
Invalid column name ' MB'.



CHECKING DATABASE Northwind
Server: Msg 207, Level 16, State 3, Line 2
Invalid column name 'E'.
Server: Msg 207, Level 16, State 1, Line 5
Invalid column name ' MB'.



CHECKING DATABASE pubs
Server: Msg 207, Level 16, State 3, Line 2
Invalid column name 'E'.
Server: Msg 207, Level 16, State 1, Line 5
Invalid column name ' MB'.



CHECKING DATABASE rec
Server: Msg 207, Level 16, State 3, Line 2
Invalid column name 'E'.
Server: Msg 207, Level 16, State 1, Line 5
Invalid column name ' MB'.



CHECKING DATABASE S
Server: Msg 207, Level 16, State 3, Line 2
Invalid column name 'E'.
Server: Msg 207, Level 16, State 1, Line 5
Invalid column name ' MB'.



CHECKING DATABASE tempdb
Server: Msg 207, Level 16, State 3, Line 2
Invalid column name 'E'.
Server: Msg 207, Level 16, State 1, Line 5
Invalid column name ' MB'.



CHECKING DATABASE T
Server: Msg 207, Level 16, State 3, Line 2
Invalid column name 'E'.
Server: Msg 207, Level 16, State 1, Line 5
Invalid column name ' MB'.



CHECKING DATABASE V
Server: Msg 207, Level 16, State 3, Line 2
Invalid column name 'E'.
Server: Msg 207, Level 16, State 1, Line 5
Invalid column name ' MB'.



CHECKING DATABASE wf
Server: Msg 207, Level 16, State 3, Line 2
Invalid column name 'E'.
Server: Msg 207, Level 16, State 1, Line 5
Invalid column name ' MB'.



CHECKING DATABASE Y
Server: Msg 207, Level 16, State 3, Line 2
Invalid column name 'E'.
Server: Msg 207, Level 16, State 1, Line 5
Invalid column name ' MB'.

I'm sure I'm overlooking something really simple. Can anyone help?

thanksStart Free Trial