none
Unable to execute query using ssis

    Question

  • if exists (select * from tempdb.sys.all_objects where name like '%#dbsize%') 
    drop table #dbsize 
    create table #dbsize 
    (Dbname sysname,dbstatus varchar(50),Recovery_Model varchar(40) default ('NA'), file_Size_MB decimal(30,2)default (0),Space_Used_MB decimal(30,2)default (0),Free_Space_MB decimal(30,2) default (0),LastBackUpTime datetime) 

    insert into #dbsize(Dbname,dbstatus,Recovery_Model,file_Size_MB,Space_Used_MB,Free_Space_MB,LastBackUpTime)
    exec sp_msforeachdb 
    'use [?]; 
    select DB_NAME() AS DbName, 
        CONVERT(varchar(20),DatabasePropertyEx(''?'',''Status'')) ,  
        CONVERT(varchar(20),DatabasePropertyEx(''?'',''Recovery'')),  
    sum(size)/128.0 AS File_Size_MB, 
    sum(CAST(FILEPROPERTY(sy.name, ''SpaceUsed'') AS INT))/128.0 as Space_Used_MB, 
    SUM( size)/128.0 - sum(CAST(FILEPROPERTY(sy.name,''SpaceUsed'') AS INT))/128.0 AS Free_Space_MB  ,
    COALESCE(CONVERT(VARCHAR(12), MAX(bus.backup_finish_date), 101),''01/01/1900'') AS LastBackUpTime
    from sys.database_files sy 
     inner join msdb.dbo.backupset bus ON bus.database_name = DB_NAME()
     where sy.type=0 and  bus.type=''D'''

    select @@SERVERNAME as ServerName,
    ('SQL Server  ' + 
        CASE SUBSTRING(CONVERT(VARCHAR(50),SERVERPROPERTY('productversion')), 1,4)
              WHEN '8.0' THEN '2000'
              WHEN '9.00' THEN '2005'
              WHEN '10.0' THEN '2008'
               WHEN '10.5' THEN '2008R2'
              WHEN '11.0' THEN '2012'
        END) 
        as Version,SERVERPROPERTY('productlevel')   as ServicePack,SD.Name as DatabaseName,db.file_Size_MB,db.Space_Used_MB ,db.Free_Space_MB,
        suser_sname(SD.owner_sid) as DB_Owner,SD.compatibility_level as CompatabilityLevel,Convert(varchar(12),SD.create_date,101) As DB_CreatedDate,SD.recovery_model_desc as RecoveryModel,Trustyworthy=
    case SD.is_trustworthy_on
    when 1 then 'Enabled'
    when 0 then 'Disabled'
    END,CONVERT(VARCHAR(10),db.LastBackupTime,111) AS LastFullBackUp
      from sys.databases SD
      LEFT OUTER JOIN #dbsize as db ON db.Dbname = sd.name

       where SD.name <> 'Tempdb'

    If i execute above query it says 


    ------------------------------

    There was an error displaying the preview.

    ------------------------------
    ADDITIONAL INFORMATION:

    Cannot drop the table '#dbsize', because it does not exist or you do not have permission. (Microsoft SQL Server, Error: 3701)


    ------------------------------

    if i execute in ssms it executes well i was using this query to loop through all servers and collect information

         
    Tuesday, October 15, 2013 7:31 PM

Answers

All replies