Rename database

ALTER DATABASE AssetMgmtServices SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

ALTER DATABASE AssetMgmtServices
MODIFY NAME = AssetMgmtServices_Nov2012
GO

ALTER DATABASE AssetMgmtServices_Nov2012
SET MULTI_USER
GO

Renaming databse mdf and ldf file

USE AssetMgmtServices_May2012

-- Replace all MyDBs with the name of the DB you want to change its name
USE AssetMgmtServices_May2012;

-- Changing Physical names and paths
-- Replace all NewMyDB with the new name you want to set for the DB
-- Replace 'C:\...\NewMyDB.mdf' with full path of new DB file to be used
ALTER DATABASE AssetMgmtServices_May2012 MODIFY FILE (NAME = 'AssetMgmtServices', FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER2012\MSSQL\DATA\AssetMgmtServices_May2012.mdf');


-- Replace 'C:\...\NewMyDB_log.ldf' with full path of new DB log file to be used
ALTER DATABASE AssetMgmtServices_May2012 MODIFY FILE (NAME = 'AssetMgmtServices_log', FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER2012\MSSQL\DATA\AssetMgmtServices_May2012_log.ldf');


-- Changing logical names
ALTER DATABASE AssetMgmtServices_May2012 MODIFY FILE (NAME = 'AssetMgmtServices', NEWNAME = 'AssetMgmtServices_May2012');
ALTER DATABASE AssetMgmtServices_May2012 MODIFY FILE (NAME = 'AssetMgmtServices_log', NEWNAME = 'AssetMgmtServices_May2012_log');


ALTER DATABASE AssetMgmtServices_May2012 SET OFFLINE WITH
ROLLBACK IMMEDIATE

--Physically change the file name

ALTER DATABASE AssetMgmtServices_May2012 SET ONLINE

BCP bulk export and import

To export data

declare @sql varchar(8000)select @sql = 'bcp [Database_name].[schema_name].[Table_name] out c:\Table_name.txt -c -t"|^" -T'

exec master..xp_cmdshell @sql



To import data

declare @sql varchar(8000)select @sql = 'bcp [Database_name].[schema_name].[Table_name] int c:\Table_name.txt -c -t"|^" -T'

exec master..xp_cmdshell @sql

SSRS exporing as Excel 97-2003 (.xls) and Excel 2012 (.xlsx)

Format should be excel, for downloading as .xlsx file format.

http://tocgjtim1pv.bns.bns/Reportserver?/RFS%20Reports/Major+Projects+-+Singleview&rs:Format=excel


Format should be EXCELOPENXML, for downloading as .xlsx file format.

http://localhost/Reportserver?/RFS%20Reports/Major+Projects+-+Singleview&rs:Format=EXCELOPENXML

Extreme wait-time when taking a SQL Server database offline

When it takes long time to make a database offline try with following sql.

ALTER DATABASE <dbname> SET OFFLINE WITH ROLLBACK IMMEDIATE

If it does not help, kill the session.

First find out the spid using following command.

EXEC sp_who2

KILL <SPID>

Downgrading SSIS package from 2014 to 2012.

The Workaround (Step by Step)

1. If you haven’t done so, open DTSX Version 2012/01 schema
2. Open the SSDT-BI VS 2013 DTSX package in a text editor
3. Search for PackageFormatVersion. Change the value from 8 to 6
4. Replace all instances of Microsoft.Package with SSIS.Package.3
5. Replace all instances of Microsoft.Pipeline with SSIS.Pipeline.3
6. Search for all instances of DTS:ExecutableType=”
   
7. For each ExecutableType value (Microsoft.ExecuteSQLTask, etc.), go to theDTSX Version 2012/01 schema and search under

   <xs:simpleType name="AnyNonPackageExecutableTypeExecutableTypeEnum">

   for the correct ExecutableType. For example, Microsoft.ExecuteSQLTask in SSIS 2014 should be Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ExecuteSQLTask, Microsoft.SqlServer.SQLTask, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc9 in SSIS 2012.
8. Replace ALL instances (including CreationName attributes) of the ExecutableType value with the one you got from Step 7
9. Search for all instances of componentClassID=”Microsoft
   
10. For each componentClassID value (Microsoft.DerivedColumn, Microsoft.Lookup, etc.), go to the DTSX Version 2012/01 schema and search under

    <xs:simpleType name="PipelineComponentComponentClassIDEnum">

    for the corresponding GUID.
11. Replace ALL instances (including CreationName attributes) of the componentClassID value with the one from Step 10.
12. Now open the package in Visual Studio 2012. If your package does not have upgraded components, then it should open by now. Otherwise, read further.

The Exception

Some components, such as the Script Component in the Data Flow Task, have been upgraded from SSDT-BI 2012 to SSDT-BI 2014. In this case, it’s not only the property / attribute value that is different, but also the structure of the XML node. In this case, replace the Version 2014/01 schema value (e.g. componentClassID=”Microsoft.ManagedComponentHost”)  with any valid DTSX Version 2012/01 componentClassID. Then open the package in VS 2012. The component will be marked as with error, that’s OK. If you have followed all the steps above, your package should at least load the designer by now. Now remove this erroneous component and re-do it in VS 2012 itself.

DELETE ALL SUBSCRIPTIONS IN REPORTING SERVICES

DELETE FROM ReportSchedule GO DELETE FROM Subscriptions GO DELETE FROM Schedule GO

Failed to initialize MSDB database for tuning (exit code: -1073741819). (DTAClient)

Could not start tuning advisor. Getting the following error 

"Failed to initialize MSDB database for tuning (exit code: -1073741819). (DTAClient)"

Then you have to run the following script.

USE [msdb]

GO/****** Object:  StoredProcedure [dbo].[sp_DTA_start_xmlprefix]    Script Date: 12/31/2008 10:56:11 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOcreate procedure [dbo].[sp_DTA_start_xmlprefix]asbegin    declare @startTags nvarchar(128)    set @startTags = N'<DTAXML><DTAOutput><AnalysisReport>'    select @startTagsendGO/****** Object:  Table [dbo].[DTA_input]    Script Date: 12/31/2008 10:56:59 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[DTA_input](    [SessionName] [sysname] NOT NULL,    [SessionID] [int] IDENTITY(1,1) NOT NULL,    [TuningOptions] [ntext] NOT NULL,    [CreationTime] [datetime] NOT NULL DEFAULT (getdate()),    [ScheduledStartTime] [datetime] NOT NULL DEFAULT (getdate()),    [ScheduledJobName] [sysname] NOT NULL DEFAULT (''),    [InteractiveStatus] [tinyint] NOT NULL DEFAULT ((0)),    [LogTableName] [nvarchar](1280) NOT NULL DEFAULT (''),    [GlobalSessionID] [uniqueidentifier] NULL DEFAULT (newid()),PRIMARY KEY CLUSTERED (    [SessionID] ASC)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]GO/****** Object:  Table [dbo].[DTA_progress]    Script Date: 12/31/2008 10:57:29 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[DTA_progress](    [ProgressEventID] [int] IDENTITY(1,1) NOT NULL,    [SessionID] [int] NULL,    [TuningStage] [tinyint] NOT NULL DEFAULT ((0)),    [WorkloadConsumption] [tinyint] NOT NULL,    [EstImprovement] [int] NOT NULL DEFAULT ((0)),    [ProgressEventTime] [datetime] NOT NULL DEFAULT (getdate()),    [ConsumingWorkLoadMessage] [nvarchar](256) NULL,    [PerformingAnalysisMessage] [nvarchar](256) NULL,    [GeneratingReportsMessage] [nvarchar](256) NULL) ON [PRIMARY]GO/****** Object:  Table [dbo].[DTA_output]    Script Date: 12/31/2008 10:57:10 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[DTA_output](    [SessionID] [int] NOT NULL,    [TuningResults] [ntext] NOT NULL,    [StopTime] [datetime] NOT NULL DEFAULT (getdate()),    [FinishStatus] [tinyint] NOT NULL DEFAULT ((0)),PRIMARY KEY CLUSTERED (    [SessionID] ASC)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]GO/****** Object:  Table [dbo].[DTA_tuninglog]    Script Date: 12/31/2008 11:01:21 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[DTA_tuninglog](    [SessionID] [int] NOT NULL,    [RowID] [int] NOT NULL,    [CategoryID] [nvarchar](4) NOT NULL,    [Event] [ntext] NULL,    [Statement] [ntext] NULL,    [Frequency] [int] NOT NULL,    [Reason] [ntext] NULL) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]GO/****** Object:  Table [dbo].[DTA_reports_database]    Script Date: 12/31/2008 10:57:56 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[DTA_reports_database](    [DatabaseID] [int] IDENTITY(1,1) NOT NULL,    [SessionID] [int] NOT NULL,    [DatabaseName] [sysname] NOT NULL,    [IsDatabaseSelectedToTune] [int] NULL,PRIMARY KEY CLUSTERED (    [DatabaseID] ASC)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]) ON [PRIMARY]GO/****** Object:  Table [dbo].[DTA_reports_query]    Script Date: 12/31/2008 10:59:42 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[DTA_reports_query](    [QueryID] [int] NOT NULL,    [SessionID] [int] NOT NULL,    [StatementType] [smallint] NOT NULL,    [StatementString] [ntext] NOT NULL,    [CurrentCost] [float] NOT NULL,    [RecommendedCost] [float] NOT NULL,    [Weight] [float] NOT NULL,    [EventString] [ntext] NULL,    [EventWeight] [float] NOT NULL,CONSTRAINT [DTA_reports_query_pk] PRIMARY KEY CLUSTERED (    [SessionID] ASC,    [QueryID] ASC)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]GO/****** Object:  Table [dbo].[DTA_reports_querydatabase]    Script Date: 12/31/2008 11:00:04 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[DTA_reports_querydatabase](    [QueryID] [int] NOT NULL,    [SessionID] [int] NOT NULL,    [DatabaseID] [int] NOT NULL) ON [PRIMARY]GO/****** Object:  Table [dbo].[DTA_reports_table]    Script Date: 12/31/2008 11:00:52 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[DTA_reports_table](    [TableID] [int] IDENTITY(1,1) NOT NULL,    [DatabaseID] [int] NOT NULL,    [SchemaName] [sysname] NOT NULL,    [TableName] [sysname] NOT NULL,    [IsView] [bit] NOT NULL DEFAULT ((0)),PRIMARY KEY CLUSTERED (    [TableID] ASC)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]) ON [PRIMARY]GO/****** Object:  Table [dbo].[DTA_reports_partitionfunction]    Script Date: 12/31/2008 10:58:57 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[DTA_reports_partitionfunction](    [PartitionFunctionID] [int] IDENTITY(1,1) NOT NULL,    [DatabaseID] [int] NOT NULL,    [PartitionFunctionName] [sysname] NOT NULL,    [PartitionFunctionDefinition] [ntext] NOT NULL,PRIMARY KEY CLUSTERED (    [PartitionFunctionID] ASC)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]GO/****** Object:  Table [dbo].[DTA_reports_partitionscheme]    Script Date: 12/31/2008 10:59:19 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[DTA_reports_partitionscheme](    [PartitionSchemeID] [int] IDENTITY(1,1) NOT NULL,    [PartitionFunctionID] [int] NOT NULL,    [PartitionSchemeName] [sysname] NOT NULL,    [PartitionSchemeDefinition] [ntext] NOT NULL,PRIMARY KEY CLUSTERED (    [PartitionSchemeID] ASC)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]GO/****** Object:  Table [dbo].[DTA_reports_tableview]    Script Date: 12/31/2008 11:01:02 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[DTA_reports_tableview](    [TableID] [int] NOT NULL,    [ViewID] [int] NOT NULL) ON [PRIMARY]GO/****** Object:  Table [dbo].[DTA_reports_querytable]    Script Date: 12/31/2008 11:00:33 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[DTA_reports_querytable](    [QueryID] [int] NOT NULL,    [SessionID] [int] NOT NULL,    [TableID] [int] NOT NULL) ON [PRIMARY]GO/****** Object:  Table [dbo].[DTA_reports_index]    Script Date: 12/31/2008 10:58:24 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[DTA_reports_index](    [IndexID] [int] IDENTITY(1,1) NOT NULL,    [TableID] [int] NOT NULL,    [IndexName] [sysname] NOT NULL,    [IsClustered] [bit] NOT NULL DEFAULT ((0)),    [IsUnique] [bit] NOT NULL DEFAULT ((0)),    [IsHeap] [bit] NOT NULL DEFAULT ((1)),    [IsExisting] [bit] NOT NULL DEFAULT ((1)),    [Storage] [float] NOT NULL,    [NumRows] [int] NOT NULL,    [IsRecommended] [bit] NOT NULL DEFAULT ((0)),    [RecommendedStorage] [float] NOT NULL,    [PartitionSchemeID] [int] NULL,    [SessionUniquefier] [int] NULL,PRIMARY KEY CLUSTERED (    [IndexID] ASC)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]) ON [PRIMARY]GO/****** Object:  Table [dbo].[DTA_reports_column]    Script Date: 12/31/2008 10:57:42 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[DTA_reports_column](    [ColumnID] [int] IDENTITY(1,1) NOT NULL,    [TableID] [int] NOT NULL,    [ColumnName] [sysname] NOT NULL,PRIMARY KEY CLUSTERED (    [ColumnID] ASC)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]) ON [PRIMARY]GO/****** Object:  Table [dbo].[DTA_reports_queryindex]    Script Date: 12/31/2008 11:00:19 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[DTA_reports_queryindex](    [QueryID] [int] NOT NULL,    [SessionID] [int] NOT NULL,    [IndexID] [int] NOT NULL,    [IsRecommendedConfiguration] [bit] NOT NULL) ON [PRIMARY]GO/****** Object:  Table [dbo].[DTA_reports_querycolumn]    Script Date: 12/31/2008 10:59:50 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[DTA_reports_querycolumn](    [QueryID] [int] NOT NULL,    [SessionID] [int] NOT NULL,    [ColumnID] [int] NOT NULL) ON [PRIMARY]GO/****** Object:  Table [dbo].[DTA_reports_indexcolumn]    Script Date: 12/31/2008 10:58:39 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[DTA_reports_indexcolumn](    [IndexID] [int] NOT NULL,    [ColumnID] [int] NOT NULL,    [ColumnOrder] [int] NULL,    [PartitionColumnOrder] [int] NOT NULL DEFAULT ((0)),    [IsKeyColumn] [bit] NOT NULL DEFAULT ((1)),    [IsDescendingColumn] [bit] NOT NULL DEFAULT ((1))) ON [PRIMARY]GO/****** Object:  StoredProcedure [dbo].[sp_DTA_check_permission]    Script Date: 12/31/2008 10:53:21 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOcreate procedure [dbo].[sp_DTA_check_permission]                 @SessionID intas begin    declare @retcode  int    declare @dbname nvarchar(128)    declare @sql nvarchar(256)    declare @dbid int
    set nocount on
    -- Check if SA    if (isnull(IS_SRVROLEMEMBER(N'sysadmin'), 0) = 1)    begin        return(0)     end
    -- if not yukon return    if (patindex('%9.00.%',@@version) = 0)    begin        return (1)    end    -- declare and open a cursor and get all the databases specified in the input    declare db_cursor cursor for    select DatabaseName from DTA_reports_database    where SessionID = @SessionID and IsDatabaseSelectedToTune  = 1    -- open    open db_cursor    -- fetch first db name    fetch next from db_cursor    into @dbname    -- loop and get all the databases selected to tune    while @@fetch_status = 0    begin        -- build use db string        select  @dbid = DB_ID(@dbname)
        -- In Yukon this masks the error messages        set @sql = N'begin try            dbcc autopilot(5,@dbid) WITH NO_INFOMSGS         end try        begin catch            set @retcode = 1        end catch'
        execute sp_executesql @sql            , N'@dbid int output, @retcode int OUTPUT'             , @dbid output             , @retcode output
        -- if caller is not member of dbo        if (@retcode = 1)        begin            -- close and reset cursor,switch context to current            -- database and return 1            close db_cursor            deallocate db_cursor            return(1)        end
        fetch from db_cursor into @dbname    end    -- close and reset cursor,switch context to current    -- database and return 1    close db_cursor    deallocate db_cursor    return(0) endGO/****** Object:  StoredProcedure [dbo].[sp_DTA_index_usage_helper_xml]    Script Date: 12/31/2008 10:54:29 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOcreate procedure [dbo].[sp_DTA_index_usage_helper_xml]    @SessionID        int,    @IsRecommended    intas    beginselect 1            as Tag,         NULL          as Parent,        '' as [IndexUsageReport!1!!ELEMENT],        case when @IsRecommended = 1 then 'false'        else 'true' end as [IndexUsageReport!1!Current],        NULL as [Database!2!DatabaseID!hide],        NULL  as [Database!2!Name!ELEMENT] ,        NULL  as [Schema!3!Name!ELEMENT] ,        NULL as [Table!4!TableID!hide],        NULL as [Table!4!Name!ELEMENT],        NULL as [Index!5!IndexID!hide],        NULL as [Index!5!Name!ELEMENT],        NULL as [Index!5!NumberOfReferences!ELEMENT],        NULL as [Index!5!PercentUsage!ELEMENT]    union allselect 2            as Tag,         1          as Parent,        NULL as [IndexUsageReport!1!!ELEMENT],        NULL as [IndexUsageReport!1!Current],        D.DatabaseID as [Database!2!DatabaseID!hide],        D.DatabaseName  as [Database!2!Name!ELEMENT] ,        NULL  as [Schema!3!Name!ELEMENT] ,        NULL as [Table!4!TableID!hide],        NULL as [Table!4!Name!ELEMENT],        NULL as [Index!5!IndexID!hide],        NULL as [Index!5!Name!ELEMENT],        NULL as [Index!5!NumberOfReferences!ELEMENT],        NULL as [Index!5!PercentUsage!ELEMENT]    from [msdb].[dbo].[DTA_reports_database] as D    where    D.SessionID = @SessionID and    D.DatabaseID in    (select D.DatabaseID from            [msdb].[dbo].[DTA_reports_queryindex] as QI,            [msdb].[dbo].[DTA_reports_index] as I,            [msdb].[dbo].[DTA_reports_table] as T,            [msdb].[dbo].[DTA_reports_database] as D            where            QI.IndexID = I.IndexID  and            I.TableID = T.TableID and            T.DatabaseID = D.DatabaseID and            D.SessionID = @SessionID and            QI.IsRecommendedConfiguration = @IsRecommended            GROUP BY D.DatabaseID)    union allselect 3            as Tag,         2          as Parent,        NULL as [IndexUsageReport!1!!ELEMENT],        NULL as [IndexUsageReport!1!Current],        D.DatabaseID as [Database!2!DatabaseID!hide],        D.DatabaseName  as [Database!2!Name!ELEMENT] ,        R.SchemaName  as [Schema!3!Name!ELEMENT] ,        NULL as [Table!4!TableID!hide],        NULL as [Table!4!Name!ELEMENT],        NULL as [Index!5!IndexID!hide],        NULL as [Index!5!Name!ELEMENT],        NULL as [Index!5!NumberOfReferences!ELEMENT],        NULL as [Index!5!PercentUsage!ELEMENT]    from [msdb].[dbo].[DTA_reports_database] as D,    (        select D.DatabaseID,T.SchemaName from        [msdb].[dbo].[DTA_reports_queryindex] as QI,        [msdb].[dbo].[DTA_reports_index] as I,        [msdb].[dbo].[DTA_reports_table] as T,        [msdb].[dbo].[DTA_reports_database] as D        where        QI.IndexID = I.IndexID  and        I.TableID = T.TableID and        T.DatabaseID = D.DatabaseID and        QI.IsRecommendedConfiguration = @IsRecommended and        D.SessionID = @SessionID        GROUP BY D.DatabaseID,T.SchemaName    ) R    where    D.SessionID = @SessionID and    D.DatabaseID = R.DatabaseIDunion all
select 4            as Tag,         3          as Parent,        NULL as [IndexUsageReport!1!!ELEMENT],        NULL as [IndexUsageReport!1!Current],        D.DatabaseID as [Database!2!DatabaseID!hide],        D.DatabaseName as [Database!2!Name!ELEMENT] ,        R.SchemaName  as [Schema!3!Name!ELEMENT] ,        R.TableID as [Table!4!TableID!hide],        T.TableName as [Table!4!Name!ELEMENT],        NULL as [Index!5!IndexID!hide],        NULL as [Index!5!Name!ELEMENT],        NULL as [Index!5!NumberOfReferences!ELEMENT],        NULL as [Index!5!PercentUsage!ELEMENT]
    from    [msdb].[dbo].[DTA_reports_database] as D,            [msdb].[dbo].[DTA_reports_table] as T,    (        select D.DatabaseID,T.SchemaName,T.TableID from        [msdb].[dbo].[DTA_reports_queryindex] as QI,        [msdb].[dbo].[DTA_reports_index] as I,        [msdb].[dbo].[DTA_reports_table] as T,        [msdb].[dbo].[DTA_reports_database] as D        where        QI.IndexID = I.IndexID  and        I.TableID = T.TableID and        T.DatabaseID = D.DatabaseID and        D.SessionID = @SessionID and        QI.IsRecommendedConfiguration = @IsRecommended        GROUP BY D.DatabaseID,T.SchemaName, T.TableID    ) R    where    D.SessionID = @SessionID and    D.DatabaseID = R.DatabaseID and    R.TableID = T.TableID and    T.DatabaseID = D.DatabaseID
union allselect 5            as Tag,         4          as Parent,        NULL as [IndexUsageReport!1!!ELEMENT],        NULL as [IndexUsageReport!1!Current],        D1.DatabaseID as [Database!2!DatabaseID!hide],        D1.DatabaseName as [Database!2!Name!ELEMENT] ,        T1.SchemaName  as [Schema!3!Name!ELEMENT] ,        T1.TableID as [Table!4!TableID!hide],        T1.TableName as [Table!4!Name!ELEMENT],        I1.IndexID as [Index!5!IndexID!hide],        I1.IndexName as [Index!5!Name!ELEMENT],        R.Count as [Index!5!NumberOfReferences!ELEMENT],        CAST(R.Usage as decimal(38,2))  as [Index!5!PercentUsage!ELEMENT]        from            [msdb].[dbo].[DTA_reports_database] as D1 ,            [msdb].[dbo].[DTA_reports_index] as I1,            [msdb].[dbo].[DTA_reports_table] as T1,            (                select D.DatabaseID,T.TableID ,                        I.IndexID  ,SUM(Q.Weight) as Count,                        100.0 *  SUM(Q.Weight) /                         ( 1.0 * (    select    CASE WHEN SUM(Q.Weight) > 0 THEN  SUM(Q.Weight)                                            else 1                                            end    
                                    from [msdb].[dbo].[DTA_reports_query] as Q                                    where Q.SessionID = @SessionID ))            as Usage        from             [msdb].[dbo].[DTA_reports_index] as I                LEFT OUTER JOIN            [msdb].[dbo].[DTA_reports_queryindex] as QI ON QI.IndexID = I.IndexID            LEFT OUTER JOIN            [msdb].[dbo].[DTA_reports_query] as Q ON QI.QueryID = Q.QueryID            JOIN            [msdb].[dbo].[DTA_reports_table] as T ON I.TableID = T.TableID            JOIN            [msdb].[dbo].[DTA_reports_database] as D ON T.DatabaseID = D.DatabaseID            and Q.SessionID = QI.SessionID and             QI.IsRecommendedConfiguration = @IsRecommended and            Q.SessionID = @SessionID            GROUP BY I.IndexID,T.TableID,D.DatabaseID) as R            where R.DatabaseID = D1.DatabaseID and            R.TableID = T1.TableID and            R.IndexID = I1.IndexID and            D1.SessionID = @SessionID  and            R.Count > 0    order by [Database!2!DatabaseID!hide],[Schema!3!Name!ELEMENT],[Table!4!TableID!hide],            [Index!5!NumberOfReferences!ELEMENT] , [Index!5!IndexID!hide] 
    FOR XML EXPLICITendGO/****** Object:  StoredProcedure [dbo].[sp_DTA_index_usage_helper_relational]    Script Date: 12/31/2008 10:54:26 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOcreate procedure [dbo].[sp_DTA_index_usage_helper_relational]    @SessionID        int,    @IsRecommended    int    as begin select D1.DatabaseName as 'Database Name' ,T1.SchemaName as 'Schema Name' ,T1.TableName as 'Table/View Name' ,I1.IndexName as 'Index Name' ,R.Count as 'Number of references' ,CAST(R.Usage as decimal(38,2)) as 'Percent Usage' from                 DTA_reports_database as D1 ,                DTA_reports_index as I1,                DTA_reports_table as T1,                (                    select D.DatabaseID,T.TableID ,                            I.IndexID  ,SUM(Q.Weight) as Count,                            100.0 *  SUM(Q.Weight) /                             ( 1.0 * (    select    CASE WHEN SUM(Q.Weight) > 0 THEN  SUM(Q.Weight)                                                else 1                                                end    
                                        from [msdb].[dbo].[DTA_reports_query] as Q                                        where Q.SessionID = @SessionID ))                as Usage        from                 [msdb].[dbo].[DTA_reports_index] as I                    LEFT OUTER JOIN                [msdb].[dbo].[DTA_reports_queryindex] as QI ON QI.IndexID = I.IndexID                LEFT OUTER JOIN                [msdb].[dbo].[DTA_reports_query] as Q ON QI.QueryID = Q.QueryID                JOIN                [msdb].[dbo].[DTA_reports_table] as T ON I.TableID = T.TableID                JOIN                [msdb].[dbo].[DTA_reports_database] as D ON T.DatabaseID = D.DatabaseID                and Q.SessionID = QI.SessionID and                 QI.IsRecommendedConfiguration = @IsRecommended and                Q.SessionID = @SessionID
                GROUP BY I.IndexID,T.TableID,D.DatabaseID) as R                where R.DatabaseID = D1.DatabaseID and                R.TableID = T1.TableID and                R.IndexID = I1.IndexID and                D1.SessionID = @SessionID  and                R.Count > 0                order by R.Count desc endGO/****** Object:  StoredProcedure [dbo].[sp_DTA_database_access_helper_xml]   Script Date: 12/31/2008 10:53:31 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOcreate procedure [dbo].[sp_DTA_database_access_helper_xml]            @SessionID        intasbegin    select 1            as Tag,             NULL          as Parent,            '' as [DatabaseAccessReport!1!!ELEMENT],            NULL  as [Database!2!Name!ELEMENT] ,            NULL as [Database!2!NumberOfReferences!ELEMENT],            NULL as [Database!2!PercentUsage!ELEMENT]        union all

    select 2 as Tag, 1 as Parent,NULL,D1.DatabaseName  ,    R.Count  ,    CAST(R.Usage as decimal(38,2))  from                     [msdb].[dbo].[DTA_reports_database] as D1 ,                    (                        select D.DatabaseID,SUM(Q.Weight) as Count,                                100.0 *  SUM(Q.Weight) /                                 ( 1.0 * (    select    CASE WHEN SUM(Q.Weight) > 0 THEN  SUM(Q.Weight)                                                    else 1                                                    end    
                                            from [msdb].[dbo].[DTA_reports_query] as Q                                            where Q.SessionID = @SessionID ))                    as Usage            from                         [msdb].[dbo].[DTA_reports_database] as D                        LEFT OUTER JOIN                        [msdb].[dbo].[DTA_reports_querydatabase] as QD ON QD.DatabaseID = D.DatabaseID                        LEFT OUTER JOIN                        [msdb].[dbo].[DTA_reports_query] as Q ON QD.QueryID = Q.QueryID                        and Q.SessionID = QD.SessionID and                         Q.SessionID = @SessionID                                GROUP BY D.DatabaseID                    ) as R                    where R.DatabaseID = D1.DatabaseID  and                    D1.SessionID = @SessionID and                    R.Count > 0    order by Tag,[Database!2!NumberOfReferences!ELEMENT] desc    FOR XML EXPLICITendGO