Database Engine Tuning Advisor

In this case I am using an account that has sys admin rights. I have tried
both with my domain account which is in the sys admin group and with the sa
account.

I have tried with both 2005 and 2008.

I have uninstalled and reinstalled 2005 with dev and standard versions,
latest available download from MSDN site, 3 times and each time with the same
result:

Database Engine Tuning Advisor
Failed to open a new connection.
Additional information:
Could not find stored procedure 'msdb..sp_DTA_help_session'. (Microsoft SQL
Server.Error:2812)

I have installed the latest service packs as well.

I am attempting to run this on a 64bit machine with XP 64bit installed. I am
running the 64 bit version of SQL 2005. I have the exact same issue on my
laptop. If i look at the tables and stored procs in MSDB I can see that they
are indeed missing.

I was able to install SQL to Server 2003 and use the tool but need to do so
from my workstation.
It appears that if I script the tables and stored procedures created in MSDB
when run for the first time from the server installation that is working and
run that against MSDB on my workstation that the tool works correctly. But I
would like to know why it is failing to work properly on my workstation.
Searching for the resolution to this issue revealed that many other users are
having the same issue.
--
Regards,
James M


"Mark Han[MSFT]" wrote:

> Hi James,
>
> Thank you for using the Microsoft MSDN Managed Newsgroup. My name is Mark
> Han. I am delighted to work with you on this issue.
>
> From your description, I understand that we failed to use Database Engine
> Tuning Advisor to connect to SQL server database.
> If I have misunderstood, please let me know.
>
> Based on the current situation, it seems to be a known issue. The cause of
> the issue seems to be the DTAEngine not able to initialize MSDB tables when
> user is not a sys admin. To work around, we need to log in as sysadmin when
> launching DTA for the first time.
>
> Besides, if the above doesn't resolve the issue, the issue might be related
> to the version of the SQL Server. are you using RTM or SQL 2005 sp2 version?
>
> Besides, You may experience problems if you use a pre-June Community
> Technology Preview (CTP) version of Microsoft SQL Server 2005 Database
> Tuning Advisor (DTA) client to tune Microsoft SQL Server 2000 or pre-June
> CTP versions of SQL Server 2005. There is an article to share with
> you:http://support.microsoft.com/kb/899634.
>
> Hope the above helpful.
>
> If there are any additional questions or concerns, please let me know.
>
> Best regards,
> Mark Han
> Microsoft Online Community Support
> ===========================================================
> Delighting our customers is our #1 priority. We welcome your
> comments and suggestions about how we can improve the
> support we provide to you. Please feel free to let my manager
> know what you think of the level of service provided. You can
> send feedback directly to my manager at: msdnmg.TakeThisOut@microsoft.com.
> ===========================================================
> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications.
>
> Note: MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or
> a Microsoft Support Engineer within 2 business day is acceptable. Please
> note that each follow up response may take approximately
> 2 business days as the support professional working with you may need
> further investigation to reach the most efficient resolution.
> The offering is not appropriate for situations that require urgent,
> real-time or phone-based interactions. Issues of this nature are
> best handled working with a dedicated Microsoft Support Engineer by
> contacting Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/en-us/subscriptions/aa948874.aspx
>
> ============================================================
> This posting is provided "AS IS" with no warranties, and confers no rights.
> =========================================================
>
>
 >> Stay informed about: Database Engine Tuning Advisor 

For anyone who is having this issue and is using a sys admin account, I
ran the following script to adding the missing objects to MSDB. It is
posted across 6 posts since there is a limit to the post size:

-- BEGIN SCRIPT 1 of 6
USE [msdb]
GO
/****** Object: StoredProcedure [dbo].[sp_DTA_start_xmlprefix] Script
Date: 12/31/2008 10:56:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[sp_DTA_start_xmlprefix]
as
begin
declare @startTags nvarchar(128)
set @startTags = N'<DTAXML><DTAOutput><AnalysisReport>'
select @startTags
end
GO
/****** Object: Table [dbo].[DTA_input] Script Date: 12/31/2008 10:56:59
******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE 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 ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE 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 ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE 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 ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE 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 ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE 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 ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE 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 ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE 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 ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE 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 ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE 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 ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE 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 ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE 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 ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE 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 ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE 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 ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE 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 ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE 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 ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE 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 ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE 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 ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[sp_DTA_check_permission]
@SessionID int
as
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)
end
GO
/****** Object: StoredProcedure [dbo].[sp_DTA_index_usage_helper_xml]
Script Date: 12/31/2008 10:54:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[sp_DTA_index_usage_helper_xml]
@SessionID int,
@IsRecommended int
as
begin
select 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 all
select 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 all
select 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.DatabaseID
union 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 all
select 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 EXPLICIT
end
GO
/****** Object: StoredProcedure
[dbo].[sp_DTA_index_usage_helper_relational] Script Date: 12/31/2008
10:54:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create 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 end
GO
/****** Object: StoredProcedure [dbo].[sp_DTA_database_access_helper_xml]
Script Date: 12/31/2008 10:53:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[sp_DTA_database_access_helper_xml]
@SessionID int
as
begin
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 EXPLICIT
end
GO
-- END SCRIPT 1 of 6
 >> Stay informed about: Database Engine Tuning Advisor 

For anyone who is having this issue and is using a sys admin account, I
ran the following script to adding the missing objects to MSDB. It is
posted across 6 posts since there is a limit to the post size:

-- BEGIN SCRIPT 2 of 6
/****** Object: StoredProcedure [dbo].[sp_DTA_table_access_helper_xml]
Script Date: 12/31/2008 10:56:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[sp_DTA_table_access_helper_xml]
@SessionID int
as
begin
select 1 as Tag,
NULL as Parent,
'' as [TableAccessReport!1!!ELEMENT],
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 [Table!4!NumberOfReferences!ELEMENT],
NULL as [Table!4!PercentUsage!ELEMENT]
union all
select
2 as Tag,
1 as Parent,
NULL as [TableAccessReport!1!!ELEMENT],
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 [Table!4!NumberOfReferences!ELEMENT],
NULL as [Table!4!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_querytable] as QT,
[msdb].[dbo].[DTA_reports_table] as T,
[msdb].[dbo].[DTA_reports_database] as D
where
QT.TableID = T.TableID and
T.DatabaseID = D.DatabaseID and
D.SessionID = @SessionID
group by D.DatabaseID)


union all

select 3 as Tag, 2 as Parent,
NULL as [TableAccessReport!1!!ELEMENT],
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 [Table!4!NumberOfReferences!ELEMENT],
NULL as [Table!4!PercentUsage!ELEMENT]

from [msdb].[dbo].[DTA_reports_database] as D,
(
select D.DatabaseID,T.SchemaName from
[msdb].[dbo].[DTA_reports_querytable] as QT,
[msdb].[dbo].[DTA_reports_table] as T,
[msdb].[dbo].[DTA_reports_database] as D
where
QT.TableID = T.TableID and
T.DatabaseID = D.DatabaseID and
D.SessionID = @SessionID
group by D.DatabaseID,T.SchemaName
) R

where
D.SessionID = @SessionID and
D.DatabaseID = R.DatabaseID
union all

select 4 as Tag, 3 as Parent,

NULL as [TableAccessReport!1!!ELEMENT],
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],
R.Count as [Table!4!NumberOfReferences!ELEMENT],
CAST(R.Usage as decimal(38,2)) as [Table!4!PercentUsage!ELEMENT]

from
[msdb].[dbo].[DTA_reports_database] as D1 ,
[msdb].[dbo].[DTA_reports_table] as T1,
(
select D.DatabaseID,T.TableID
,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_table] as T
LEFT OUTER JOIN
[msdb].[dbo].[DTA_reports_querytable] as QT ON QT.TableID = T.TableID
LEFT OUTER JOIN
[msdb].[dbo].[DTA_reports_query] as Q ON QT.QueryID = Q.QueryID
JOIN
[msdb].[dbo].[DTA_reports_database] as D ON T.DatabaseID = D.DatabaseID
and Q.SessionID = QT.SessionID and
Q.SessionID = @SessionID
GROUP BY T.TableID,D.DatabaseID) as R
where R.DatabaseID = D1.DatabaseID and
R.TableID = T1.TableID and
D1.SessionID = @SessionID and
R.Count > 0

order by
[Database!2!DatabaseID!hide],[Schema!3!Name!ELEMENT],[Table!4!TableID!hide],[Table!4!NumberOfReferences!ELEMENT]

FOR XML EXPLICIT
end
GO
/****** Object: StoredProcedure
[dbo].[sp_DTA_table_access_helper_relational] Script Date: 12/31/2008
10:56:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[sp_DTA_table_access_helper_relational]
@SessionID int
as
begin select D1.DatabaseName as 'Database Name' ,T1.SchemaName as 'Schema
Name' ,T1.TableName as 'Table Name' ,R.Count as 'Number of references'
,CAST(R.Usage as decimal(38,2)) as 'Percent Usage' from
[msdb].[dbo].[DTA_reports_database] as D1 ,
[msdb].[dbo].[DTA_reports_table] as T1,
(
select D.DatabaseID,T.TableID
,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_table] as T
LEFT OUTER JOIN
[msdb].[dbo].[DTA_reports_querytable] as QT ON QT.TableID = T.TableID
LEFT OUTER JOIN
[msdb].[dbo].[DTA_reports_query] as Q ON QT.QueryID = Q.QueryID
JOIN
DTA_reports_database as D ON T.DatabaseID = D.DatabaseID
and Q.SessionID = QT.SessionID and
Q.SessionID = @SessionID
GROUP BY T.TableID,D.DatabaseID) as R
where R.DatabaseID = D1.DatabaseID and
R.TableID = T1.TableID and
D1.SessionID = @SessionID and
R.Count > 0
order by R.Count desc end
GO
/****** Object: StoredProcedure [dbo].[sp_DTA_column_access_helper_xml]
Script Date: 12/31/2008 10:53:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[sp_DTA_column_access_helper_xml]
@SessionID int
as
begin
select 1 as Tag,
NULL as Parent,
'' as [ColumnAccessReport!1!!ELEMENT],
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 [Column!5!ColumnID!hide],
NULL as [Column!5!Name!ELEMENT],
NULL as [Column!5!NumberOfReferences!ELEMENT],
NULL as [Column!5!PercentUsage!ELEMENT]
union all
select 2 as Tag, 1 as Parent, NULL,
D.DatabaseID,D.DatabaseName,
NULL,NULL,NULL,NULL,NULL,NULL,NULL
from [msdb].[dbo].[DTA_reports_database] as D
where
D.SessionID = @SessionID and
D.DatabaseID in
(select D.DatabaseID from
[msdb].[dbo].[DTA_reports_querycolumn] as QC,
[msdb].[dbo].[DTA_reports_column] as C,
[msdb].[dbo].[DTA_reports_table] as T,
[msdb].[dbo].[DTA_reports_database] as D
where
QC.ColumnID = C.ColumnID and
C.TableID = T.TableID and
T.DatabaseID = D.DatabaseID and
D.SessionID = @SessionID
group by D.DatabaseID)
union all
select 3 as Tag, 2 as Parent, NULL,
R.DatabaseID,D.DatabaseName,
R.SchemaName,NULL,NULL,NULL,NULL,NULL,NULL
from [msdb].[dbo].[DTA_reports_database] as D,
(
select D.DatabaseID,T.SchemaName from
[msdb].[dbo].[DTA_reports_querycolumn] as QC,
[msdb].[dbo].[DTA_reports_column] as C,
[msdb].[dbo].[DTA_reports_table] as T,
[msdb].[dbo].[DTA_reports_database] as D
where
QC.ColumnID = C.ColumnID and
C.TableID = T.TableID and
T.DatabaseID = D.DatabaseID and
D.SessionID = @SessionID
group by D.DatabaseID,T.SchemaName
) R

where
D.SessionID = @SessionID and
D.DatabaseID = R.DatabaseID
union all
select 4 as Tag, 3 as Parent, NULL,
R.DatabaseID,D.DatabaseName,
R.SchemaName,R.TableID,T.TableName,NULL,NULL,NULL,NULL
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_querycolumn] as QC,
[msdb].[dbo].[DTA_reports_column] as C,
[msdb].[dbo].[DTA_reports_table] as T,
[msdb].[dbo].[DTA_reports_database] as D
where
QC.ColumnID = C.ColumnID and
C.TableID = T.TableID and
T.DatabaseID = D.DatabaseID and
D.SessionID = @SessionID
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 all
select 5 as Tag, 4 as Parent, NULL,
D1.DatabaseID,D1.DatabaseName,
T1.SchemaName,T1.TableID,T1.TableName,C1.ColumnID,C1.ColumnName,
R.Count,
CAST(R.Usage as decimal(38,2))
from
[msdb].[dbo].[DTA_reports_database]as D1 ,
[msdb].[dbo].[DTA_reports_table] as T1,
[msdb].[dbo].[DTA_reports_column] as C1,
(
select D.DatabaseID,T.TableID,C.ColumnID,
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_column] as C
LEFT OUTER JOIN
[msdb].[dbo].[DTA_reports_querycolumn] as QC ON QC.ColumnID = C.ColumnID
LEFT OUTER JOIN
[msdb].[dbo].[DTA_reports_query] as Q ON QC.QueryID = Q.QueryID
JOIN
[msdb].[dbo].[DTA_reports_table] as T ON C.TableID = T.TableID
JOIN
[msdb].[dbo].[DTA_reports_database] as D ON T.DatabaseID = D.DatabaseID
and Q.SessionID = QC.SessionID and
Q.SessionID = @SessionID
GROUP BY C.ColumnID,T.TableID,D.DatabaseID ) as R

where R.DatabaseID = D1.DatabaseID and
R.TableID = T1.TableID and
R.ColumnID = C1.ColumnID and
D1.SessionID = @SessionID and
R.Count > 0

order by
[Database!2!DatabaseID!hide],[Schema!3!Name!ELEMENT],[Table!4!TableID!hide],[Column!5!NumberOfReferences!ELEMENT] , [Column!5!ColumnID!hide]
FOR XML EXPLICIT
end
GO
/****** Object: StoredProcedure
[dbo].[sp_DTA_column_access_helper_relational] Script Date: 12/31/2008
10:53:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[sp_DTA_column_access_helper_relational]
@SessionID int
as
begin select D1.DatabaseName as 'Database Name' ,T1.SchemaName as 'Schema
Name' ,T1.TableName as 'Table/View Name' ,C1.ColumnName as 'Column Name'
,R.Count as 'Number of references' ,CAST(R.Usage as decimal(38,2)) as
'Percent Usage' from
[msdb].[dbo].[DTA_reports_database] as D1 ,
[msdb].[dbo].[DTA_reports_table] as T1,
[msdb].[dbo].[DTA_reports_column] as C1,

(
select D.DatabaseID,T.TableID,C.ColumnID,
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_column] as C
LEFT OUTER JOIN
DTA_reports_querycolumn as QC ON QC.ColumnID = C.ColumnID
LEFT OUTER JOIN
DTA_reports_query as Q ON QC.QueryID = Q.QueryID
JOIN
DTA_reports_table as T ON C.TableID = T.TableID
JOIN
DTA_reports_database as D ON T.DatabaseID = D.DatabaseID
and Q.SessionID = QC.SessionID and
Q.SessionID = @SessionID
GROUP BY C.ColumnID,T.TableID,D.DatabaseID) as R
where R.DatabaseID = D1.DatabaseID and
R.TableID = T1.TableID and
R.ColumnID = C1.ColumnID and
D1.SessionID = @SessionID and
R.Count > 0
order by R.Count desc end
GO
/****** Object: StoredProcedure
[dbo].[sp_DTA_query_indexrelations_helper_xml] Script Date: 12/31/2008
10:55:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[sp_DTA_query_indexrelations_helper_xml]
@SessionID int ,
@Recommended int
as
begin

select 1 as Tag,
NULL as Parent,
'' as [StatementIndexReport!1!!ELEMENT],
case when @Recommended = 1 then 'false'
else'true' end
as [StatementIndexReport!1!Current],
NULL as [StatementIndexDetail!2!stmtID!hide],
NULL as [StatementIndexDetail!2!StatementString!ELEMENT] ,
NULL as [Database!3!DatabaseID!hide],
NULL as [Database!3!Name!ELEMENT] ,
NULL as [Schema!4!Name!ELEMENT] ,
NULL as [Table!5!TableID!hide],
NULL as [Table!5!Name!ELEMENT],
NULL as [Index!6!IndexID!hide],
NULL as [Index!6!Name!ELEMENT]
union all
select 2 as Tag,
1 as Parent,
NULL as [StatementIndexReport!1!!ELEMENT],
NULL as [StatementIndexReport!1!Current],
Q.QueryID as [StatementIndexDetail!2!stmtID!hide],
Q.StatementString as [StatementIndexDetail!2!StatementString!ELEMENT] ,
NULL as [Database!3!DatabaseID!hide],
NULL as [Database!3!Name!ELEMENT] ,
NULL as [Schema!4!Name!ELEMENT] ,
NULL as [Table!5!TableID!hide],
NULL as [Table!5!Name!ELEMENT],
NULL as [Index!6!IndexID!hide],
NULL as [Index!6!Name!ELEMENT]
from [msdb].[dbo].[DTA_reports_database] as D,
[msdb].[dbo].[DTA_reports_query] Q,
( select Q.QueryID,D.DatabaseID
from
[msdb].[dbo].[DTA_reports_query] Q,
[msdb].[dbo].[DTA_reports_queryindex] QI,
[msdb].[dbo].[DTA_reports_index] I,
[msdb].[dbo].[DTA_reports_table] T,
[msdb].[dbo].[DTA_reports_database] D
where
Q.SessionID=QI.SessionID and
Q.QueryID=QI.QueryID and
QI.IndexID=I.IndexID and
I.TableID=T.TableID and
T.DatabaseID = D.DatabaseID and
QI.IsRecommendedConfiguration = @Recommended and
Q.SessionID=@SessionID
group by Q.QueryID,D.DatabaseID) as R
where
R.QueryID = Q.QueryID and
R.DatabaseID = D.DatabaseID and
Q.SessionID = @SessionID
and R.DatabaseID IS NOT NULL
union all
select 3 as Tag,
2 as Parent,
NULL as [StatementIndexReport!1!!ELEMENT],
NULL as [StatementIndexReport!1!Current],
Q.QueryID as [StatementIndexDetail!2!stmtID!hide],
Q.StatementString as [StatementIndexDetail!2!StatementString!ELEMENT] ,
D.DatabaseID as [Database!3!DatabaseID!hide],
D.DatabaseName as [Database!3!Name!ELEMENT] ,
NULL as [Schema!4!Name!ELEMENT] ,
NULL as [Table!5!TableID!hide],
NULL as [Table!5!Name!ELEMENT],
NULL as [Index!6!IndexID!hide],
NULL as [Index!6!Name!ELEMENT]
from [msdb].[dbo].[DTA_reports_database] as D,
[msdb].[dbo].[DTA_reports_query] Q,
( select Q.QueryID,D.DatabaseID
from
[msdb].[dbo].[DTA_reports_query] Q,
[msdb].[dbo].[DTA_reports_queryindex] QI,
[msdb].[dbo].[DTA_reports_index] I,
[msdb].[dbo].[DTA_reports_table] T,
[msdb].[dbo].[DTA_reports_database] D
where
Q.SessionID=QI.SessionID and
Q.QueryID=QI.QueryID and
QI.IndexID=I.IndexID and
I.TableID=T.TableID and
T.DatabaseID = D.DatabaseID and
QI.IsRecommendedConfiguration = @Recommended and
Q.SessionID=@SessionID
group by Q.QueryID,D.DatabaseID) as R
where
R.QueryID = Q.QueryID and
R.DatabaseID = D.DatabaseID and
Q.SessionID = @SessionID
union all
select 4 as Tag,
3 as Parent,
NULL as [StatementIndexReport!1!!ELEMENT],
NULL as [StatementIndexReport!1!Current],
Q.QueryID as [StatementIndexDetail!2!stmtID!hide],
Q.StatementString as [StatementIndexDetail!2!StatementString!ELEMENT] ,
D.DatabaseID as [Database!3!DatabaseID!hide],
D.DatabaseName as [Database!3!Name!ELEMENT] ,
R.SchemaName as [Schema!4!Name!ELEMENT] ,
NULL as [Table!5!TableID!hide],
NULL as [Table!5!Name!ELEMENT],
NULL as [Index!6!IndexID!hide],
NULL as [Index!6!Name!ELEMENT]
from [msdb].[dbo].[DTA_reports_database] as D,
[msdb].[dbo].[DTA_reports_query] Q,
( select Q.QueryID,D.DatabaseID,T.SchemaName
from
[msdb].[dbo].[DTA_reports_query] Q,
[msdb].[dbo].[DTA_reports_queryindex] QI,
[msdb].[dbo].[DTA_reports_index] I,
[msdb].[dbo].[DTA_reports_table] T,
[msdb].[dbo].[DTA_reports_database] D
where
Q.SessionID=QI.SessionID and
Q.QueryID=QI.QueryID and
QI.IndexID=I.IndexID and
I.TableID=T.TableID and
T.DatabaseID = D.DatabaseID and
QI.IsRecommendedConfiguration = @Recommended and
Q.SessionID=@SessionID
group by Q.QueryID,D.DatabaseID,T.SchemaName) as R
where
R.QueryID = Q.QueryID and
R.DatabaseID = D.DatabaseID and
Q.SessionID = @SessionID

union all
select 5 as Tag,
4 as Parent,
NULL as [StatementIndexReport!1!!ELEMENT],
NULL as [StatementIndexReport!1!Current],
Q.QueryID as [StatementIndexDetail!2!stmtID!hide],
Q.StatementString as [StatementIndexDetail!2!StatementString!ELEMENT] ,
D.DatabaseID as [Database!3!DatabaseID!hide],
D.DatabaseName as [Database!3!Name!ELEMENT] ,
R.SchemaName as [Schema!4!Name!ELEMENT] ,
R.TableID as [Table!5!TableID!hide],
T.TableName as [Table!5!Name!ELEMENT],
NULL as [Index!6!IndexID!hide],
NULL as [Index!6!Name!ELEMENT]
from [msdb].[dbo].[DTA_reports_database] as D,
[msdb].[dbo].[DTA_reports_query] Q,
[msdb].[dbo].[DTA_reports_table] T,
( select Q.QueryID,D.DatabaseID,T.SchemaName,T.TableID
from
[msdb].[dbo].[DTA_reports_query] Q,
[msdb].[dbo].[DTA_reports_queryindex] QI,
[msdb].[dbo].[DTA_reports_index] I,
[msdb].[dbo].[DTA_reports_table] T,
[msdb].[dbo].[DTA_reports_database] D
where
Q.SessionID=QI.SessionID and
Q.QueryID=QI.QueryID and
QI.IndexID=I.IndexID and
I.TableID=T.TableID and
T.DatabaseID = D.DatabaseID and
QI.IsRecommendedConfiguration = @Recommended and
Q.SessionID=@SessionID
group by Q.QueryID,D.DatabaseID,T.SchemaName,T.TableID) as R
where
R.QueryID = Q.QueryID and
R.DatabaseID = D.DatabaseID and
Q.SessionID = @SessionID and
R.TableID = T.TableID
union all
select 6 as Tag,
5 as Parent,
NULL as [StatementIndexReport!1!!ELEMENT],
NULL as [StatementIndexReport!1!Current],
Q.QueryID as [StatementIndexDetail!2!stmtID!hide],
Q.StatementString as [StatementIndexDetail!2!StatementString!ELEMENT] ,
D.DatabaseID as [Database!3!DatabaseID!hide],
D.DatabaseName as [Database!3!Name!ELEMENT] ,
T.SchemaName as [Schema!4!Name!ELEMENT] ,
T.TableID as [Table!5!TableID!hide],
T.TableName as [Table!5!Name!ELEMENT],
I.IndexID as [Index!6!IndexID!hide],
I.IndexName as [Index!6!Name!ELEMENT]
from
[msdb].[dbo].[DTA_reports_query] Q,
[msdb].[dbo].[DTA_reports_queryindex] QI,
[msdb].[dbo].[DTA_reports_index] I,
[msdb].[dbo].[DTA_reports_table] T,
[msdb].[dbo].[DTA_reports_database] D
where
Q.SessionID=QI.SessionID and
Q.QueryID=QI.QueryID and
QI.IndexID=I.IndexID and
I.TableID=T.TableID and
T.DatabaseID = D.DatabaseID and
QI.IsRecommendedConfiguration = @Recommended and
Q.SessionID=@SessionID
order by [StatementIndexDetail!2!stmtID!hide],[Database!3!DatabaseID!hide],
[Schema!4!Name!ELEMENT],[Table!5!TableID!hide],[Index!6!IndexID!hide]
FOR XML EXPLICIT
end
GO
/****** Object: StoredProcedure
[dbo].[sp_DTA_query_indexrelations_helper_relational] Script Date:
12/31/2008 10:55:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[sp_DTA_query_indexrelations_helper_relational]
@SessionID int,
@Recommended int
as
begin select 'Statement Id' =Q.QueryID, 'Statement String'
=Q.StatementString,'Database Name' =D.DatabaseName, 'Schema Name'
=T.SchemaName, 'Table/View Name' =T.TableName, 'Index Name' =I.IndexName
from
[msdb].[dbo].[DTA_reports_query] Q,
[msdb].[dbo].[DTA_reports_queryindex] QI,
[msdb].[dbo].[DTA_reports_index] I,
[msdb].[dbo].[DTA_reports_table] T,
[msdb].[dbo].[DTA_reports_database] D
where
Q.SessionID=QI.SessionID and
Q.QueryID=QI.QueryID and
QI.IndexID=I.IndexID and
I.TableID=T.TableID and
T.DatabaseID = D.DatabaseID and
QI.IsRecommendedConfiguration = @Recommended and
Q.SessionID=@SessionID order by Q.QueryID end
GO
/****** Object: StoredProcedure
[dbo].[sp_DTA_database_access_helper_relational] Script Date: 12/31/2008
10:53:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[sp_DTA_database_access_helper_relational]
@SessionID int
as
begin select D1.DatabaseName as 'Database Name' ,R.Count as 'Number of
references' ,CAST(R.Usage as decimal(38,2)) as 'Percent Usage' 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
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 R.Count desc end
GO
-- END SCRIPT 2 of 6
 >> Stay informed about: Database Engine Tuning Advisor 

For anyone who is having this issue and is using a sys admin account, I
ran the following script to adding the missing objects to MSDB. It is
posted across 6 posts since there is a limit to the post size:

-- BEGIN SCRIPT 3 of 6
/****** Object: StoredProcedure
[dbo].[sp_DTA_index_current_detail_helper_xml] Script Date: 12/31/2008
10:54:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[sp_DTA_index_current_detail_helper_xml]
@SessionID int
as
begin
select 1 as Tag,
NULL as Parent,
'' as [IndexDetailReport!1!!ELEMENT],
'true' as [IndexDetailReport!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!Clustered],
NULL as [Index!5!Unique],
NULL as [Index!5!Heap],
NULL as [Index!5!IndexSizeInMB],
NULL as [Index!5!NumberOfRows]
union all
select 2 as Tag,
1 as Parent,
NULL as [IndexDetailReport!1!!ELEMENT],
NULL as [IndexDetailReport!1!Recommended],
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!Clustered],
NULL as [Index!5!Unique],
NULL as [Index!5!Heap],
NULL as [Index!5!IndexSizeInMB],
NULL as [Index!5!NumberOfRows]
from [msdb].[dbo].[DTA_reports_database] as D
where
D.SessionID = @SessionID and
D.DatabaseID in
(select D.DatabaseID from
[msdb].[dbo].[DTA_reports_table] as T,
[msdb].[dbo].[DTA_reports_database] as D,
[msdb].[dbo].[DTA_reports_index] as I
where
D.SessionID = @SessionID and
D.DatabaseID = T.DatabaseID and
T.TableID = I.TableID and
I.IsExisting = 1
group by D.DatabaseID)
union all
select 3 as Tag,
2 as Parent,
NULL as [IndexDetailReport!1!!ELEMENT],
NULL as [IndexDetailReport!1!Recommended],
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!Clustered],
NULL as [Index!5!Unique],
NULL as [Index!5!Heap],
NULL as [Index!5!IndexSizeInMB],
NULL as [Index!5!NumberOfRows]
from [msdb].[dbo].[DTA_reports_database] as D,
(
select D.DatabaseID,T.SchemaName
from
[msdb].[dbo].[DTA_reports_table] as T,
[msdb].[dbo].[DTA_reports_database] as D,
[msdb].[dbo].[DTA_reports_index] as I
where
D.SessionID = @SessionID and
D.DatabaseID = T.DatabaseID and
T.TableID = I.TableID and
I.IsExisting = 1
group by D.DatabaseID,T.SchemaName
) R
where
D.SessionID = @SessionID and
D.DatabaseID = R.DatabaseID
union all
select 4 as Tag,
3 as Parent,
NULL as [IndexDetailReport!1!!ELEMENT],
NULL as [IndexDetailReport!1!Recommended],
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!Clustered],
NULL as [Index!5!Unique],
NULL as [Index!5!Heap],
NULL as [Index!5!IndexSizeInMB],
NULL as [Index!5!NumberOfRows]
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_table] as T,
[msdb].[dbo].[DTA_reports_database] as D,
[msdb].[dbo].[DTA_reports_index] as I
where
D.SessionID = @SessionID and
D.DatabaseID = T.DatabaseID and
T.TableID = I.TableID and
I.IsExisting = 1
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 all

select 5 as Tag,
4 as Parent,
NULL as [IndexDetailReport!1!!ELEMENT],
NULL as [IndexDetailReport!1!Recommended],
D.DatabaseID as [Database!2!DatabaseID!hide],
D.DatabaseName as [Database!2!Name!ELEMENT] ,
T.SchemaName as [Schema!3!Name!ELEMENT] ,
T.TableID as [Table!4!TableID!hide],
T.TableName as [Table!4!Name!ELEMENT],
I.IndexID as [Index!5!IndexID!hide],
I.IndexName as [Index!5!Name!ELEMENT],
CASE
WHEN I.IsClustered = 1 THEN 'true'
WHEN I.IsClustered = 0 THEN 'false'
end
as [Index!5!Clustered],
CASE
WHEN I.IsUnique = 1 THEN 'true'
WHEN I.IsUnique = 0 THEN 'false'
end
as [Index!5!Unique],
CASE
WHEN I.IsHeap = 1 THEN 'true'
WHEN I.IsHeap = 0 THEN 'false'
end
as [Index!5!Heap],
CAST(I.Storage as decimal(38,2)) as [Index!5!IndexSizeInMB],
I.NumRows as [Index!5!NumberOfRows]
from
[msdb].[dbo].[DTA_reports_database] D,
[msdb].[dbo].[DTA_reports_table] T,
[msdb].[dbo].[DTA_reports_index] as I
where
D.SessionID = @SessionID and
D.DatabaseID = T.DatabaseID and
T.TableID = I.TableID and
I.IsExisting = 1
order by
[Database!2!DatabaseID!hide],[Schema!3!Name!ELEMENT],[Table!4!TableID!hide],[Index!5!IndexID!hide]
FOR XML EXPLICIT

end
GO
/****** Object: StoredProcedure [dbo].[sp_DTA_view_table_helper_relational]
Script Date: 12/31/2008 10:56:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[sp_DTA_view_table_helper_relational]
@SessionID int
as
begin select 'View Id' =T2.TableID, 'Database Name' =D.DatabaseName,
'Schema Name' =T2.SchemaName, 'View Name' =T2.TableName, 'Database Name'
=D.DatabaseName, 'Schema Name' =T1.SchemaName, 'Table Name' =T1.TableName
from
[msdb].[dbo].[DTA_reports_database] D,
[msdb].[dbo].[DTA_reports_tableview] TV,
[msdb].[dbo].[DTA_reports_table] T1,
[msdb].[dbo].[DTA_reports_table] T2
where
D.DatabaseID=T1.DatabaseID and
D.DatabaseID=T2.DatabaseID and
T1.TableID=TV.TableID and
T2.TableID=TV.ViewID and
D.SessionID=@SessionID
order by TV.ViewID end
GO
/****** Object: StoredProcedure
[dbo].[sp_DTA_index_recommended_detail_helper_xml] Script Date: 12/31/2008
10:54:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[sp_DTA_index_recommended_detail_helper_xml]
@SessionID int
as
begin
select 1 as Tag,
NULL as Parent,
'' as [IndexDetailReport!1!!ELEMENT],
'false' as [IndexDetailReport!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!Clustered],
NULL as [Index!5!Unique],
NULL as [Index!5!Heap],
NULL as [Index!5!IndexSizeInMB],
NULL as [Index!5!NumberOfRows]
union all
select 2 as Tag,
1 as Parent,
NULL as [IndexDetailReport!1!!ELEMENT],
NULL as [IndexDetailReport!1!Recommended],
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!Clustered],
NULL as [Index!5!Unique],
NULL as [Index!5!Heap],
NULL as [Index!5!IndexSizeInMB],
NULL as [Index!5!NumberOfRows]
from [msdb].[dbo].[DTA_reports_database] as D
where
D.SessionID = @SessionID and
D.DatabaseID in
(select D.DatabaseID from
[msdb].[dbo].[DTA_reports_table] as T,
[msdb].[dbo].[DTA_reports_database] as D,
[msdb].[dbo].[DTA_reports_index] as I
where
D.SessionID = @SessionID and
D.DatabaseID = T.DatabaseID and
T.TableID = I.TableID and
IsRecommended = 1
group by D.DatabaseID)
union all
select 3 as Tag,
2 as Parent,
NULL as [IndexDetailReport!1!!ELEMENT],
NULL as [IndexDetailReport!1!Recommended],
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!Clustered],
NULL as [Index!5!Unique],
NULL as [Index!5!Heap],
NULL as [Index!5!IndexSizeInMB],
NULL as [Index!5!NumberOfRows]
from [msdb].[dbo].[DTA_reports_database] as D,
(
select D.DatabaseID,T.SchemaName
from
[msdb].[dbo].[DTA_reports_table] as T,
[msdb].[dbo].[DTA_reports_database] as D,
[msdb].[dbo].[DTA_reports_index] as I
where
D.SessionID = @SessionID and
D.DatabaseID = T.DatabaseID and
T.TableID = I.TableID and
IsRecommended = 1
group by D.DatabaseID,T.SchemaName
) R
where
D.SessionID = @SessionID and
D.DatabaseID = R.DatabaseID
union all
select 4 as Tag,
3 as Parent,
NULL as [IndexDetailReport!1!!ELEMENT],
NULL as [IndexDetailReport!1!Recommended],
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!Clustered],
NULL as [Index!5!Unique],
NULL as [Index!5!Heap],
NULL as [Index!5!IndexSizeInMB],
NULL as [Index!5!NumberOfRows]
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_table] as T,
[msdb].[dbo].[DTA_reports_database] as D,
[msdb].[dbo].[DTA_reports_index] as I
where
D.SessionID = @SessionID and
D.DatabaseID = T.DatabaseID and
T.TableID = I.TableID and
I.IsRecommended = 1
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 all

select 5 as Tag,
4 as Parent,
NULL as [IndexDetailReport!1!!ELEMENT],
NULL as [IndexDetailReport!1!Recommended],
D.DatabaseID as [Database!2!DatabaseID!hide],
D.DatabaseName as [Database!2!Name!ELEMENT] ,
T.SchemaName as [Schema!3!Name!ELEMENT] ,
T.TableID as [Table!4!TableID!hide],
T.TableName as [Table!4!Name!ELEMENT],
I.IndexID as [Index!5!IndexID!hide],
I.IndexName as [Index!5!Name!ELEMENT],
CASE
WHEN I.IsClustered = 1 THEN 'true'
WHEN I.IsClustered = 0 THEN 'false'
end
as [Index!5!Clustered],
CASE
WHEN I.IsUnique = 1 THEN 'true'
WHEN I.IsUnique = 0 THEN 'false'
end
as [Index!5!Unique],
CASE
WHEN I.IsHeap = 1 THEN 'true'
WHEN I.IsHeap = 0 THEN 'false'
end
as [Index!5!Heap],
CAST(I.RecommendedStorage as decimal(38,2)) as [Index!5!IndexSizeInMB],
I.NumRows as [Index!5!NumberOfRows]
from
[msdb].[dbo].[DTA_reports_database] D,
[msdb].[dbo].[DTA_reports_table] T,
[msdb].[dbo].[DTA_reports_index] as I
where
D.SessionID = @SessionID and
D.DatabaseID = T.DatabaseID and
T.TableID = I.TableID and
I.IsRecommended = 1
order by
[Database!2!DatabaseID!hide],[Schema!3!Name!ELEMENT],[Table!4!TableID!hide],[Index!5!IndexID!hide]
FOR XML EXPLICIT

end
GO
/****** Object: StoredProcedure
[dbo].[sp_DTA_index_detail_current_helper_relational] Script Date:
12/31/2008 10:54:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[sp_DTA_index_detail_current_helper_relational]
@SessionID int
as
begin select 'Database Name' = D.DatabaseName, 'Schema Name' =
T.SchemaName, 'Table/View Name' = T.TableName, 'Index Name' = I.IndexName,
'Clustered' = CASE
WHEN I.IsClustered = 1 THEN 'Yes'
WHEN I.IsClustered = 0 THEN 'No'
end, 'Unique' = CASE
WHEN I.IsUnique = 1 THEN 'Yes'
WHEN I.IsUnique = 0 THEN 'No'
end , 'Heap' = CASE
WHEN I.IsHeap = 1 THEN 'Yes'
WHEN I.IsHeap = 0 THEN 'No'
end , 'Index Size (MB)'= CAST(I.Storage as decimal(38,2)) , 'Number of
Rows'= NumRows from
DTA_reports_database D,
DTA_reports_table T,
DTA_reports_index as I
where
D.SessionID = @SessionID and
D.DatabaseID = T.DatabaseID and
T.TableID = I.TableID and
I.IsExisting = 1 end
GO
/****** Object: StoredProcedure
[dbo].[sp_DTA_index_detail_recommended_helper_relational] Script Date:
12/31/2008 10:54:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[sp_DTA_index_detail_recommended_helper_relational]
@SessionID int
as
begin select 'Database Name' = D.DatabaseName, 'Schema Name' =
T.SchemaName, 'Table/View Name' = T.TableName, 'Index Name' = I.IndexName,
'Clustered' = CASE
WHEN I.IsClustered = 1 THEN 'Yes'
WHEN I.IsClustered = 0 THEN 'No'
end, 'Unique' = CASE
WHEN I.IsUnique = 1 THEN 'Yes'
WHEN I.IsUnique = 0 THEN 'No'
end , 'Heap' = CASE
WHEN I.IsHeap = 1 THEN 'Yes'
WHEN I.IsHeap = 0 THEN 'No'
end , 'Index Size (MB)'= CAST(I.RecommendedStorage as decimal(38,2)) ,
'Number of Rows'= NumRows from
DTA_reports_database D,
DTA_reports_table T,
DTA_reports_index as I
where
D.SessionID = @SessionID and
D.DatabaseID = T.DatabaseID and
T.TableID = I.TableID and
I.IsRecommended = 1 end
GO
/****** Object: StoredProcedure [dbo].[sp_DTA_view_table_helper_xml]
Script Date: 12/31/2008 10:56:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[sp_DTA_view_table_helper_xml]
@SessionID int
as
begin
select 1 as Tag,
NULL as Parent,
'' as [ViewTableReport!1!!ELEMENT],
NULL as [Database!2!DatabaseID!hide],
NULL as [Database!2!Name!ELEMENT] ,
NULL as [Schema!3!Name!ELEMENT] ,
NULL as [View!4!ViewID!hide],
NULL as [View!4!Name!ELEMENT],
NULL as [Table!5!TableID!hide],
NULL as [Table!5!Name!ELEMENT]
union all
select 2 as Tag,
1 as Parent,
NULL as [ViewTableReport!1!!ELEMENT],
D.DatabaseID as [Database!2!DatabaseID!hide],
D.DatabaseName as [Database!2!Name!ELEMENT] ,
NULL as [Schema!3!Name!ELEMENT] ,
NULL as [View!4!ViewID!hide],
NULL as [View!4!Name!ELEMENT],
NULL as [Table!5!TableID!hide],
NULL as [Table!5!Name!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_table] as T,
[msdb].[dbo].[DTA_reports_database] as D
where
T.IsView = 1 and
T.DatabaseID = D.DatabaseID and
D.SessionID = @SessionID
GROUP BY D.DatabaseID)
union all
select 3 as Tag,
2 as Parent,
NULL as [ViewTableReport!1!!ELEMENT],
D.DatabaseID as [Database!2!DatabaseID!hide],
D.DatabaseName as [Database!2!Name!ELEMENT] ,
R.SchemaName as [Schema!3!Name!ELEMENT] ,
NULL as [View!4!ViewID!hide],
NULL as [View!4!Name!ELEMENT],
NULL as [Table!5!TableID!hide],
NULL as [Table!5!Name!ELEMENT]
from [msdb].[dbo].[DTA_reports_database] as D,
(select D.DatabaseID,T.SchemaName
from [msdb].[dbo].[DTA_reports_table] as T,
[msdb].[dbo].[DTA_reports_database] as D
where
T.IsView = 1 and
T.DatabaseID = D.DatabaseID and
D.SessionID = @SessionID
GROUP BY D.DatabaseID,T.SchemaName
) R
where
R.DatabaseID = D.DatabaseID and
D.SessionID = @SessionID

union all
select 4 as Tag,
3 as Parent,
NULL as [ViewTableReport!1!!ELEMENT],
D.DatabaseID as [Database!2!DatabaseID!hide],
D.DatabaseName as [Database!2!Name!ELEMENT] ,
R.SchemaName as [Schema!3!Name!ELEMENT] ,
T.TableID as [View!4!ViewID!hide],
T.TableName as [View!4!Name!ELEMENT],
NULL as [Table!5!TableID!hide],
NULL as [Table!5!Name!ELEMENT]
from [msdb].[dbo].[DTA_reports_table] as T,
[msdb].[dbo].[DTA_reports_database] as D,
(select D.DatabaseID,T.SchemaName,T.TableID
from [msdb].[dbo].[DTA_reports_table] as T,
[msdb].[dbo].[DTA_reports_database] as D
where
T.IsView = 1 and
T.DatabaseID = D.DatabaseID and
D.SessionID = @SessionID
GROUP BY D.DatabaseID,T.SchemaName,T.TableID
) R
where
R.DatabaseID = D.DatabaseID and
T.TableID = R.TableID and
D.SessionID = @SessionID
union all
select 5 as Tag,
4 as Parent,
NULL as [ViewTableReport!1!!ELEMENT],
D.DatabaseID as [Database!2!DatabaseID!hide],
D.DatabaseName as [Database!2!Name!ELEMENT] ,
T2.SchemaName as [Schema!3!Name!ELEMENT] ,
T2.TableID as [View!4!ViewID!hide],
T2.TableName as [View!4!Name!ELEMENT],
T1.TableID as [Table!5!TableID!hide],
T1.TableName as [Table!5!Name!ELEMENT]
from
[msdb].[dbo].[DTA_reports_database] D,
[msdb].[dbo].[DTA_reports_tableview] TV,
[msdb].[dbo].[DTA_reports_table] T1,
[msdb].[dbo].[DTA_reports_table] T2
where
D.DatabaseID=T1.DatabaseID and
D.DatabaseID=T2.DatabaseID and
T1.TableID=TV.TableID and
T2.TableID=TV.ViewID and
D.SessionID = @SessionID

order by
[Database!2!DatabaseID!hide],[Schema!3!Name!ELEMENT],[View!4!ViewID!hide],[Table!5!TableID!hide]
FOR XML EXPLICIT
end
GO
/****** Object: StoredProcedure [dbo].[sp_DTA_wkld_analysis_helper_xml]
Script Date: 12/31/2008 10:56:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[sp_DTA_wkld_analysis_helper_xml]
@SessionID int
as
begin
select 1 as Tag,
NULL as Parent,
'' as [WorkloadAnalysisReport!1!!ELEMENT],
NULL as [Statements!2!Type!ELEMENT] ,
NULL as [Statements!2!NumberOfStatements!ELEMENT],
NULL as [Statements!2!CostDecreased!ELEMENT],
NULL as [Statements!2!CostIncreased!ELEMENT],
NULL as [Statements!2!CostSame!ELEMENT]
union all
select 2 as Tag,
1 as Parent,
NULL as [WorkloadAnalysis!1!!ELEMENT],
CASE
WHEN StatementType = 0 THEN 'Select'
WHEN StatementType = 1 THEN 'Update'
WHEN StatementType = 2 THEN 'Insert'
WHEN StatementType = 3 THEN 'Delete'
end as [Statements!2!Type!ELEMENT] ,
COUNT(QueryID) as [Statements!2!NumberOfStatements!ELEMENT],
SUM(CASE WHEN RecommendedCost<CurrentCost THEN 1 else 0 end) as
[Statements!2!CostDecreased!ELEMENT],
SUM(CASE WHEN RecommendedCost>CurrentCost THEN 1 else 0 end) as
[Statements!2!CostIncreased!ELEMENT],
SUM(CASE WHEN RecommendedCost=CurrentCost THEN 1 else 0 end) as
[Statements!2!CostSame!ELEMENT]
from
[msdb].[dbo].[DTA_reports_query]
where
SessionID=@SessionID
group by StatementType
FOR XML EXPLICIT
end
GO
/****** Object: StoredProcedure
[dbo].[sp_DTA_wkld_analysis_helper_relational] Script Date: 12/31/2008
10:56:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[sp_DTA_wkld_analysis_helper_relational]
@SessionID int
as
begin select 'Statement Type' = CASE
WHEN StatementType = 0 THEN 'Select'
WHEN StatementType = 1 THEN 'Update'
WHEN StatementType = 2 THEN 'Insert'
WHEN StatementType = 3 THEN 'Delete'
end, 'Number of Statements' =COUNT(QueryID), 'Cost Decreased' =SUM(CASE
WHEN RecommendedCost<CurrentCost THEN 1 else 0 end), 'Cost
Increased' =SUM(CASE
WHEN RecommendedCost>CurrentCost THEN 1 else 0 end), 'No Change'
=SUM(CASE
WHEN RecommendedCost=CurrentCost THEN 1 else 0 end) from
[msdb].[dbo].[DTA_reports_query]
where
SessionID=@SessionID group by StatementType end
GO
/****** Object: StoredProcedure [dbo].[sp_DTA_query_costrange_helper_xml]
Script Date: 12/31/2008 10:55:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[sp_DTA_query_costrange_helper_xml]
@SessionID int
as
begin
declare @maxCost float
declare @minCost float
declare @maxCurrentCost float
declare @minCurrentCost float
declare @maxRecommendedCost float
declare @minRecommendedCost float
set nocount on
select @minCurrentCost = min(CurrentCost*Weight),@maxCurrentCost =
max(CurrentCost*Weight),
@minRecommendedCost = min(RecommendedCost*Weight),
@maxRecommendedCost = max(RecommendedCost*Weight)
from [msdb].[dbo].[DTA_reports_query]
where SessionID = @SessionID
-- Set the bucket boundaries
if @maxCurrentCost > @maxRecommendedCost
set @maxCost = @maxCurrentCost
else
set @maxCost = @maxRecommendedCost

if @minCurrentCost < @minRecommendedCost
set @minCost = @minCurrentCost
else
set @minCost = @minRecommendedCost

create table #stringmap(OutputString nvarchar(30),num int)
insert into #stringmap values(N'0% - 10%',0)
insert into #stringmap values(N'11% - 20%',1)
insert into #stringmap values(N'21% - 30%',2)
insert into #stringmap values(N'31% - 40%',3)
insert into #stringmap values(N'41% - 50%',4)
insert into #stringmap values(N'51% - 60%',5)
insert into #stringmap values(N'61% - 70%',6)
insert into #stringmap values(N'71% - 80%',7)
insert into #stringmap values(N'81% - 90%',
insert into #stringmap values(N'91% - 100%',9)



select num,count(*) as cnt
into #c
from
( select case
when (@maxCost=@minCost) then 9
when (CurrentCost*Weight-@minCost)/(@maxCost-@minCost) = 1 then 9
else
convert(int,floor(10*(CurrentCost*Weight-@minCost)/(@maxCost-@minCost)))
end as num
from
[msdb].[dbo].[DTA_reports_query]
where CurrentCost*Weight >= @minCost and
CurrentCost*Weight <= @maxCost
and SessionID = @SessionID
) t
group by num

select num,count(*) as cnt
into #r
from
( select case
when (@maxCost=@minCost) then 9
when (RecommendedCost*Weight-@minCost)/(@maxCost-@minCost) = 1 then 9
else
convert(int,floor(10*(RecommendedCost*Weight-@minCost)/(@maxCost-@minCost)))
end as num
from
[msdb].[dbo].[DTA_reports_query]
where RecommendedCost*Weight >= @minCost and
RecommendedCost*Weight <= @maxCost
and SessionID = @SessionID
) t
group by num

select 1 as Tag,
NULL as Parent,
'' as [StatementCostRangeReport!1!!ELEMENT],
NULL as [CostRange!2!Percent] ,
NULL as [CostRange!2!NumStatementsCurrent!ELEMENT],
NULL as [CostRange!2!NumStatementsRecommended!ELEMENT]
union all
select 2 as Tag,
1 as Parent,
NULL as [StatementCostRangeReport!1!!ELEMENT],
OutputString as [CostRange!2!ELEMENT] ,
ISNULL(c.cnt,0) as [CostRange!2!NumStatementsCurrent!ELEMENT],
ISNULL(r.cnt,0) as [CostRange!2!NumStatementsRecommended!ELEMENT]
from
(
select #stringmap.num, #r.cnt
from #stringmap LEFT OUTER JOIN #r
ON #stringmap.num = #r.num
) r,
(
select #stringmap.num, #c.cnt
from #stringmap LEFT OUTER JOIN #c
ON #stringmap.num = #c.num
) c,
#stringmap
where #stringmap.num = r.num and
#stringmap.num = c.num

FOR XML EXPLICIT

drop table #r
drop table #c
drop table #stringmap
end
GO
-- END SCRIPT 3 of 6
 >> Stay informed about: Database Engine Tuning Advisor 

For anyone who is having this issue and is using a sys admin account, I
ran the following script to adding the missing objects to MSDB. It is
posted across 6 posts since there is a limit to the post size:

-- BEGIN SCRIPT 6 of 6
/****** Object: StoredProcedure [dbo].[sp_DTA_insert_reports_queryindex]
Script Date: 12/31/2008 10:55:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[sp_DTA_insert_reports_queryindex]
@SessionID int,
@QueryID int,
@IndexID int,
@IsRecommendedConfiguration bit
as
begin
declare @retval int
set nocount on

exec @retval = sp_DTA_check_permission @SessionID

if @retval = 1
begin
raiserror(31002,-1,-1)
return(1)
end
insert into
[msdb].[dbo].[DTA_reports_queryindex]([SessionID],[QueryID],[IndexID],
[IsRecommendedConfiguration])
values(@SessionID,@QueryID,@IndexID,@IsRecommendedConfiguration)

end
GO
/****** Object: StoredProcedure [dbo].[sp_DTA_insert_reports_column]
Script Date: 12/31/2008 10:54:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[sp_DTA_insert_reports_column]
@SessionID int,
@TableID int,
@ColumnName sysname
as
begin
declare @retval int
set nocount on

exec @retval = sp_DTA_check_permission @SessionID

if @retval = 1
begin
raiserror(31002,-1,-1)
return(1)
end

insert into [msdb].[dbo].[DTA_reports_column]([TableID], [ColumnName])
values( @TableID ,@ColumnName)

end
GO
/****** Object: StoredProcedure [dbo].[sp_DTA_insert_reports_indexcolumn]
Script Date: 12/31/2008 10:54:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[sp_DTA_insert_reports_indexcolumn]
@SessionID int,
@IndexID int,
@ColumnID int,
@ColumnOrder int,
@PartitionColumnOrder int,
@IsKeyColumn bit,
@IsDescendingColumn bit
as
begin
declare @retval int
set nocount on

exec @retval = sp_DTA_check_permission @SessionID

if @retval = 1
begin
raiserror(31002,-1,-1)
return(1)
end
insert into [msdb].[dbo].[DTA_reports_indexcolumn]([IndexID], [ColumnID],
[ColumnOrder], [PartitionColumnOrder], [IsKeyColumn], [IsDescendingColumn])
values(@IndexID,@ColumnID,@ColumnOrder,@PartitionColumnOrder,@IsKeyColumn,@IsDescendingColumn)
end
GO
/****** Object: StoredProcedure [dbo].[sp_DTA_insert_reports_querycolumn]
Script Date: 12/31/2008 10:55:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[sp_DTA_insert_reports_querycolumn]
@SessionID int,
@QueryID int,
@ColumnID int
as
begin
declare @retval int
set nocount on

exec @retval = sp_DTA_check_permission @SessionID

if @retval = 1
begin
raiserror(31002,-1,-1)
return(1)
end
insert into [msdb].[dbo].[DTA_reports_querycolumn]([QueryID],
[ColumnID],[SessionID])
values(@QueryID,@ColumnID,@SessionID )
end
GO
/****** Object: StoredProcedure [dbo].[sp_DTA_get_session_report] Script
Date: 12/31/2008 10:53:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[sp_DTA_get_session_report]
@SessionID int,
@ReportID int,
@ReportType int
as
begin

declare @retval int
set nocount on

exec @retval = sp_DTA_check_permission @SessionID

if @retval = 1
begin
raiserror(31002,-1,-1)
return(1)
end

if @ReportType = 0
begin
/**************************************************************/
/* Query Cost Report */
/**************************************************************/
if @ReportID = 2
begin
exec sp_DTA_query_cost_helper_relational @SessionID
end
/**************************************************************/
/* Event Frequency Report */
/**************************************************************/
else if @ReportID = 3
begin
exec sp_DTA_event_weight_helper_relational @SessionID
end
/**************************************************************/
/* Query Detail Report */
/**************************************************************/
else if @ReportID = 4
begin
exec sp_DTA_query_detail_helper_relational @SessionID
end
/**************************************************************/
/* Current Query Index Relations Report */
/**************************************************************/
else if @ReportID = 5
begin
exec sp_DTA_query_indexrelations_helper_relational @SessionID,0
end
/**************************************************************/
/* Recommended Query Index Relations Report */
/**************************************************************/
else if @ReportID = 6
begin
exec sp_DTA_query_indexrelations_helper_relational @SessionID,1
end
/**************************************************************/
/* Current Query Cost Range */
/**************************************************************/
else if @ReportID = 7
begin
exec sp_DTA_query_costrange_helper_relational @SessionID
end
/**************************************************************/
/* Recommended Query Cost Range */
/**************************************************************/
else if @ReportID = 8
begin
exec sp_DTA_query_costrange_helper_relational @SessionID
end
/**************************************************************/
/* Current Query Index Usage Report */
/**************************************************************/
else if @ReportID = 9
begin
exec sp_DTA_index_usage_helper_relational @SessionID,0
end
/**************************************************************/
/* Recommended Query Index Usage Report */
/**************************************************************/
else if @ReportID = 10
begin
exec sp_DTA_index_usage_helper_relational @SessionID,1
end
/**************************************************************/
/* Current Index Detail Report */
/**************************************************************/
else if @ReportID = 11
begin
exec sp_DTA_index_detail_current_helper_relational @SessionID
end
/**************************************************************/
/* Recommended Index Detail Report */
/**************************************************************/
else if @ReportID = 12
begin
exec sp_DTA_index_detail_recommended_helper_relational @SessionID
end
/**************************************************************/
/* View Table Relations Report */
/**************************************************************/
else if @ReportID = 13
begin
exec sp_DTA_view_table_helper_relational @SessionID
end
/**************************************************************/
/* Workload Analysis Report */
/**************************************************************/
else if @ReportID = 14
begin
exec sp_DTA_wkld_analysis_helper_relational @SessionID
end
/**************************************************************/
/* All object access reports */
/**************************************************************/
else if @ReportID = 15
begin
exec sp_DTA_database_access_helper_relational @SessionID
end
else if @ReportID = 16
begin
exec sp_DTA_table_access_helper_relational @SessionID
end
else if @ReportID = 17
begin
exec sp_DTA_column_access_helper_relational @SessionID
end
end
-- XML Reports
else if @ReportType = 1
begin
/**************************************************************/
/* Query Cost Report */
/**************************************************************/
if @ReportID = 2
begin
exec sp_DTA_query_cost_helper_xml @SessionID
end
/**************************************************************/
/* Event Frequency Report */
/**************************************************************/
else if @ReportID = 3
begin
exec sp_DTA_event_weight_helper_xml @SessionID
end
/**************************************************************/
/* Query Detail Report */
/**************************************************************/
else if @ReportID = 4
begin
exec sp_DTA_query_detail_helper_xml @SessionID
end
/**************************************************************/
/* Current Query Index Relations Report */
/**************************************************************/
else if @ReportID = 5
begin
exec sp_DTA_query_indexrelations_helper_xml @SessionID,0
end
/**************************************************************/
/* Recommended Query Index Relations Report */
/**************************************************************/
else if @ReportID = 6
begin
exec sp_DTA_query_indexrelations_helper_xml @SessionID,1
end
/**************************************************************/
/* Current Query Cost Range */
/**************************************************************/
else if @ReportID = 7
begin
exec sp_DTA_query_costrange_helper_xml @SessionID
end
/**************************************************************/
/* Recommended Query Cost Range */
/**************************************************************/
else if @ReportID = 8
begin
exec sp_DTA_query_costrange_helper_xml @SessionID
end
/**************************************************************/
/* Current Query Index Usage Report */
/**************************************************************/
else if @ReportID = 9
begin
exec sp_DTA_index_usage_helper_xml @SessionID,0
end
/**************************************************************/
/* Recommended Query Index Usage Report */
/**************************************************************/
else if @ReportID = 10
begin
exec sp_DTA_index_usage_helper_xml @SessionID,1
end
/**************************************************************/
/* Current Index Detail Report */
/**************************************************************/
else if @ReportID = 11
begin
exec sp_DTA_index_current_detail_helper_xml @SessionID
end
/**************************************************************/
/* Recommended Index Detail Report */
/**************************************************************/
else if @ReportID = 12
begin
exec sp_DTA_index_recommended_detail_helper_xml @SessionID
end
/**************************************************************/
/* View Table Relations Report */
/**************************************************************/
else if @ReportID = 13
begin
exec sp_DTA_view_table_helper_xml @SessionID
end
/**************************************************************/
/* Workload Analysis Report */
/**************************************************************/
else if @ReportID = 14
begin
exec sp_DTA_wkld_analysis_helper_xml @SessionID
end
/**************************************************************/
/* All object access reports */
/**************************************************************/
else if @ReportID = 15
begin
exec sp_DTA_database_access_helper_xml @SessionID
end
else if @ReportID = 16
begin
exec sp_DTA_table_access_helper_xml @SessionID
end
else if @ReportID = 17
begin
exec sp_DTA_column_access_helper_xml @SessionID
end
end
end
GO
/****** Object: Check [CK__DTA_progr__Workl__21D600EE] Script Date:
12/31/2008 10:57:33 ******/
ALTER TABLE [dbo].[DTA_progress] WITH CHECK ADD CHECK
(([WorkloadConsumption]>=(0) AND [WorkloadConsumption]<=(100)))
GO
/****** Object: ForeignKey [FK__DTA_outpu__Sessi__269AB60B] Script Date:
12/31/2008 10:57:12 ******/
ALTER TABLE [dbo].[DTA_output] WITH CHECK ADD FOREIGN KEY([SessionID])
REFERENCES [dbo].[DTA_input] ([SessionID])
ON DELETE CASCADE
GO
/****** Object: ForeignKey [FK__DTA_progr__Sessi__1FEDB87C] Script Date:
12/31/2008 10:57:31 ******/
ALTER TABLE [dbo].[DTA_progress] WITH CHECK ADD FOREIGN KEY([SessionID])
REFERENCES [dbo].[DTA_input] ([SessionID])
ON DELETE CASCADE
GO
/****** Object: ForeignKey [FK__DTA_repor__Table__4F9CCB9E] Script Date:
12/31/2008 10:57:45 ******/
ALTER TABLE [dbo].[DTA_reports_column] WITH NOCHECK ADD FOREIGN
KEY([TableID])
REFERENCES [dbo].[DTA_reports_table] ([TableID])
ON DELETE CASCADE
GO
/****** Object: ForeignKey [FK__DTA_repor__Sessi__2D47B39A] Script Date:
12/31/2008 10:57:58 ******/
ALTER TABLE [dbo].[DTA_reports_database] WITH CHECK ADD FOREIGN
KEY([SessionID])
REFERENCES [dbo].[DTA_input] ([SessionID])
ON DELETE CASCADE
GO
/****** Object: ForeignKey [FK__DTA_repor__Table__451F3D2B] Script Date:
12/31/2008 10:58:26 ******/
ALTER TABLE [dbo].[DTA_reports_index] WITH NOCHECK ADD FOREIGN KEY([TableID])
REFERENCES [dbo].[DTA_reports_table] ([TableID])
ON DELETE CASCADE
GO
/****** Object: ForeignKey [FK__DTA_repor__Index__51851410] Script Date:
12/31/2008 10:58:42 ******/
ALTER TABLE [dbo].[DTA_reports_indexcolumn] WITH NOCHECK ADD FOREIGN
KEY([IndexID])
REFERENCES [dbo].[DTA_reports_index] ([IndexID])
ON DELETE CASCADE
GO
/****** Object: ForeignKey [FK__DTA_repor__Datab__30242045] Script Date:
12/31/2008 10:59:01 ******/
ALTER TABLE [dbo].[DTA_reports_partitionfunction] WITH CHECK ADD FOREIGN
KEY([DatabaseID])
REFERENCES [dbo].[DTA_reports_database] ([DatabaseID])
ON DELETE CASCADE
GO
/****** Object: ForeignKey [FK__DTA_repor__Parti__33008CF0] Script Date:
12/31/2008 10:59:23 ******/
ALTER TABLE [dbo].[DTA_reports_partitionscheme] WITH CHECK ADD FOREIGN
KEY([PartitionFunctionID])
REFERENCES [dbo].[DTA_reports_partitionfunction] ([PartitionFunctionID])
ON DELETE CASCADE
GO
/****** Object: ForeignKey [FK__DTA_repor__Sessi__3B95D2F1] Script Date:
12/31/2008 10:59:43 ******/
ALTER TABLE [dbo].[DTA_reports_query] WITH CHECK ADD FOREIGN KEY([SessionID])
REFERENCES [dbo].[DTA_input] ([SessionID])
ON DELETE CASCADE
GO
/****** Object: ForeignKey [DTA_reports_querycolumn_fk] Script Date:
12/31/2008 10:59:53 ******/
ALTER TABLE [dbo].[DTA_reports_querycolumn] WITH CHECK ADD CONSTRAINT
[DTA_reports_querycolumn_fk] FOREIGN KEY([SessionID], [QueryID])
REFERENCES [dbo].[DTA_reports_query] ([SessionID], [QueryID])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[DTA_reports_querycolumn] CHECK CONSTRAINT
[DTA_reports_querycolumn_fk]
GO
/****** Object: ForeignKey [FK__DTA_repor__Colum__5649C92D] Script Date:
12/31/2008 10:59:56 ******/
ALTER TABLE [dbo].[DTA_reports_querycolumn] WITH CHECK ADD FOREIGN
KEY([ColumnID])
REFERENCES [dbo].[DTA_reports_column] ([ColumnID])
GO
/****** Object: ForeignKey [DTA_reports_querydatabase_fk] Script Date:
12/31/2008 11:00:08 ******/
ALTER TABLE [dbo].[DTA_reports_querydatabase] WITH CHECK ADD CONSTRAINT
[DTA_reports_querydatabase_fk] FOREIGN KEY([SessionID], [QueryID])
REFERENCES [dbo].[DTA_reports_query] ([SessionID], [QueryID])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[DTA_reports_querydatabase] CHECK CONSTRAINT
[DTA_reports_querydatabase_fk]
GO
/****** Object: ForeignKey [FK__DTA_repor__Datab__414EAC47] Script Date:
12/31/2008 11:00:09 ******/
ALTER TABLE [dbo].[DTA_reports_querydatabase] WITH CHECK ADD FOREIGN
KEY([DatabaseID])
REFERENCES [dbo].[DTA_reports_database] ([DatabaseID])
GO
/****** Object: ForeignKey [DTA_reports_queryindex_fk] Script Date:
12/31/2008 11:00:22 ******/
ALTER TABLE [dbo].[DTA_reports_queryindex] WITH CHECK ADD CONSTRAINT
[DTA_reports_queryindex_fk] FOREIGN KEY([SessionID], [QueryID])
REFERENCES [dbo].[DTA_reports_query] ([SessionID], [QueryID])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[DTA_reports_queryindex] CHECK CONSTRAINT
[DTA_reports_queryindex_fk]
GO
/****** Object: ForeignKey [FK__DTA_repor__Index__4BCC3ABA] Script Date:
12/31/2008 11:00:24 ******/
ALTER TABLE [dbo].[DTA_reports_queryindex] WITH CHECK ADD FOREIGN
KEY([IndexID])
REFERENCES [dbo].[DTA_reports_index] ([IndexID])
GO
/****** Object: ForeignKey [DTA_reports_querytable_fk] Script Date:
12/31/2008 11:00:36 ******/
ALTER TABLE [dbo].[DTA_reports_querytable] WITH CHECK ADD CONSTRAINT
[DTA_reports_querytable_fk] FOREIGN KEY([SessionID], [QueryID])
REFERENCES [dbo].[DTA_reports_query] ([SessionID], [QueryID])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[DTA_reports_querytable] CHECK CONSTRAINT
[DTA_reports_querytable_fk]
GO
/****** Object: ForeignKey [FK__DTA_repor__Table__3E723F9C] Script Date:
12/31/2008 11:00:38 ******/
ALTER TABLE [dbo].[DTA_reports_querytable] WITH CHECK ADD FOREIGN
KEY([TableID])
REFERENCES [dbo].[DTA_reports_table] ([TableID])
GO
/****** Object: ForeignKey [FK__DTA_repor__Datab__35DCF99B] Script Date:
12/31/2008 11:00:54 ******/
ALTER TABLE [dbo].[DTA_reports_table] WITH NOCHECK ADD FOREIGN
KEY([DatabaseID])
REFERENCES [dbo].[DTA_reports_database] ([DatabaseID])
ON DELETE CASCADE
GO
/****** Object: ForeignKey [FK__DTA_repor__Table__38B96646] Script Date:
12/31/2008 11:01:04 ******/
ALTER TABLE [dbo].[DTA_reports_tableview] WITH CHECK ADD FOREIGN
KEY([TableID])
REFERENCES [dbo].[DTA_reports_table] ([TableID])
ON DELETE CASCADE
GO
/****** Object: ForeignKey [FK__DTA_repor__ViewI__39AD8A7F] Script Date:
12/31/2008 11:01:06 ******/
ALTER TABLE [dbo].[DTA_reports_tableview] WITH CHECK ADD FOREIGN
KEY([ViewID])
REFERENCES [dbo].[DTA_reports_table] ([TableID])
GO
/****** Object: ForeignKey [FK__DTA_tunin__Sessi__2A6B46EF] Script Date:
12/31/2008 11:01:23 ******/
ALTER TABLE [dbo].[DTA_tuninglog] WITH NOCHECK ADD FOREIGN KEY([SessionID])
REFERENCES [dbo].[DTA_input] ([SessionID])
ON DELETE CASCADE
GO
-- END SCRIPT 6 of 6
 >> Stay informed about: Database Engine Tuning Advisor 

For anyone who is having this issue and is using a sys admin account, I
ran the following script to adding the missing objects to MSDB. It is
posted across 6 posts since there is a limit to the post size:

-- BEGIN SCRIPT 4 of 6
/****** Object: StoredProcedure
[dbo].[sp_DTA_query_costrange_helper_relational] Script Date: 12/31/2008
10:55:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[sp_DTA_query_costrange_helper_relational]
@SessionID int
as
begin
declare @maxCost float
declare @minCost float
declare @maxCurrentCost float
declare @minCurrentCost float
declare @maxRecommendedCost float
declare @minRecommendedCost float

set nocount on
select @minCurrentCost = min(CurrentCost*Weight),@maxCurrentCost =
max(CurrentCost*Weight),
@minRecommendedCost = min(RecommendedCost*Weight),
@maxRecommendedCost = max(RecommendedCost*Weight)
from [msdb].[dbo].[DTA_reports_query]
where SessionID = @SessionID

-- Set the bucket boundaries
if @maxCurrentCost > @maxRecommendedCost
set @maxCost = @maxCurrentCost
else
set @maxCost = @maxRecommendedCost

if @minCurrentCost < @minRecommendedCost
set @minCost = @minCurrentCost
else
set @minCost = @minRecommendedCost

create table #stringmap(OutputString nvarchar(30),num int)
insert into #stringmap values(N'0% - 10%',0)
insert into #stringmap values(N'11% - 20%',1)
insert into #stringmap values(N'21% - 30%',2)
insert into #stringmap values(N'31% - 40%',3)
insert into #stringmap values(N'41% - 50%',4)
insert into #stringmap values(N'51% - 60%',5)
insert into #stringmap values(N'61% - 70%',6)
insert into #stringmap values(N'71% - 80%',7)
insert into #stringmap values(N'81% - 90%',
insert into #stringmap values(N'91% - 100%',9)



select num,count(*) as cnt
into #c
from
(
select case
when (@maxCost=@minCost) then 9
when (CurrentCost*Weight-@minCost)/(@maxCost-@minCost) = 1 then 9
else
convert(int,floor(10*(CurrentCost*Weight-@minCost)/(@maxCost-@minCost)))
end as num
from
[msdb].[dbo].[DTA_reports_query]
where CurrentCost*Weight >= @minCost and
CurrentCost*Weight <= @maxCost
and SessionID = @SessionID
) t
group by num

select num,count(*) as cnt
into #r
from
( select case
when (@maxCost=@minCost) then 9
when (RecommendedCost*Weight-@minCost)/(@maxCost-@minCost) = 1 then 9
else
convert(int,floor(10*(RecommendedCost*Weight-@minCost)/(@maxCost-@minCost)))
end as num
from
[msdb].[dbo].[DTA_reports_query]
where RecommendedCost*Weight >= @minCost and
RecommendedCost*Weight <= @maxCost
and SessionID = @SessionID
) t
group by num select 'Cost Range' =OutputString, 'Number of statements
(Current)' = ISNULL(c.cnt,0) , 'Number of statements (Recommended)' =
ISNULL(r.cnt,0) from
(
select #stringmap.num, #r.cnt
from #stringmap LEFT OUTER JOIN #r
ON #stringmap.num = #r.num
) r,
(
select #stringmap.num, #c.cnt
from #stringmap LEFT OUTER JOIN #c
ON #stringmap.num = #c.num
) c,
#stringmap
where #stringmap.num = r.num and
#stringmap.num = c.num
drop table #r
drop table #c
drop table #stringmap
end
GO
/****** Object: StoredProcedure [dbo].[sp_DTA_query_cost_helper_xml]
Script Date: 12/31/2008 10:55:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[sp_DTA_query_cost_helper_xml]
@SessionID int
as
begin
select 1 as Tag,
NULL as Parent,
'' as [StatementCostReport!1!!element],
NULL as [Statement!2!StatementID!ELEMENT],
NULL as [Statement!2!StatementString!ELEMENT] ,
NULL as [Statement!2!PercentImprovement!ELEMENT],
NULL as [Statement!2!Type!ELEMENT]
union all

select 2 as Tag,
1 as Parent,
NULL as [StatementCostReport!1!!element],
QueryID as [Statement!2!StatementID!ELEMENT],
StatementString as [Statement!2!StatementString!ELEMENT] ,
CASE
WHEN CurrentCost = 0 THEN 0.00
WHEN CurrentCost <> 0 THEN
CAST((100.0*(CurrentCost - RecommendedCost)/CurrentCost) as decimal
(20,2))
end as [Statement!2!PercentImprovement!ELEMENT],
CASE
WHEN StatementType = 0 THEN 'Select'
WHEN StatementType = 1 THEN 'Update'
WHEN StatementType = 2 THEN 'Insert'
WHEN StatementType = 3 THEN 'Delete'
end as [Statement!2!Type!ELEMENT]

from [msdb].[dbo].[DTA_reports_query]
where SessionID=@SessionID
order by Tag,[Statement!2!PercentImprovement!ELEMENT] desc
FOR XML EXPLICIT
end
GO
/****** Object: StoredProcedure [dbo].[sp_DTA_query_cost_helper_relational]
Script Date: 12/31/2008 10:55:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[sp_DTA_query_cost_helper_relational]
@SessionID int
as
begin select 'Statement Id' = QueryID, 'Statement String' =
StatementString, 'Percent Improvement' =
CASE
WHEN CurrentCost = 0 THEN 0.00
WHEN CurrentCost <> 0 THEN
CAST((100.0*(CurrentCost - RecommendedCost)/CurrentCost) as decimal
(20,2))
end , 'Statement Type' = CASE
WHEN StatementType = 0 THEN 'Select'
WHEN StatementType = 1 THEN 'Update'
WHEN StatementType = 2 THEN 'Insert'
WHEN StatementType = 3 THEN 'Delete'
end from [msdb].[dbo].[DTA_reports_query]
where SessionID=@SessionID
order by 'Percent Improvement' desc end
GO
/****** Object: StoredProcedure [dbo].[sp_DTA_event_weight_helper_xml]
Script Date: 12/31/2008 10:53:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[sp_DTA_event_weight_helper_xml]
@SessionID int
as
begin
select 1 as Tag,
NULL as Parent,
'' as [EventWeightReport!1!!element],
NULL as [EventDetails!2!EventString!ELEMENT] ,
NULL as [EventDetails!2!Weight!ELEMENT]
union all

select 2 as Tag,
1 as Parent,
NULL as [QueryCost!1!!element],
EventString as [EventDetails!2!EventString!ELEMENT] ,
CAST(EventWeight as decimal(38,2)) as [EventDetails!2!Weight!ELEMENT]
from [msdb].[dbo].[DTA_reports_query]
where SessionID=@SessionID and EventWeight>0
order by Tag,[EventDetails!2!Weight!ELEMENT] desc
FOR XML EXPLICIT
end
GO
/****** Object: StoredProcedure
[dbo].[sp_DTA_event_weight_helper_relational] Script Date: 12/31/2008
10:53:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[sp_DTA_event_weight_helper_relational]
@SessionID int
as
begin select 'Event String'= EventString, 'Weight' = CAST(EventWeight as
decimal(38,2)) from [msdb].[dbo].[DTA_reports_query]
where SessionID=@SessionID and EventWeight>0
order by EventWeight desc end
GO
/****** Object: StoredProcedure [dbo].[sp_DTA_query_detail_helper_xml]
Script Date: 12/31/2008 10:55:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[sp_DTA_query_detail_helper_xml]
@SessionID int
as
begin
select 1 as Tag,
NULL as Parent,
'' as [StatementDetailReport!1!!element],
NULL as [Statement!2!StatementID!ELEMENT] ,
NULL as [Statement!2!StatementString!ELEMENT] ,
NULL as [Statement!2!Type!ELEMENT],
NULL as [Statement!2!CurrentCost!ELEMENT],
NULL as [Statement!2!RecommendedCost!ELEMENT],
NULL as [Statement!2!EventString!ELEMENT]
union all

select 2 as Tag,
1 as Parent,
NULL as [QueryCost!1!!element],
QueryID as [Statement!2!StatementID!ELEMENT],
StatementString as [Statement!2!StatementString!ELEMENT] ,
CASE
WHEN StatementType = 0 THEN 'Select'
WHEN StatementType = 1 THEN 'Update'
WHEN StatementType = 2 THEN 'Insert'
WHEN StatementType = 3 THEN 'Delete'
end as [Statement!2!Type!ELEMENT!element],
CAST(CurrentCost as decimal(38,7)) as [Statement!2!CurrentCost!ELEMENT],
CAST(RecommendedCost as decimal(38,7)) as
[Statement!2!RecommendedCost!ELEMENT],
EventString as [Statement!2!EventString!ELEMENT]
from [msdb].[dbo].[DTA_reports_query]
where SessionID=@SessionID
FOR XML EXPLICIT
end
GO
/****** Object: StoredProcedure
[dbo].[sp_DTA_query_detail_helper_relational] Script Date: 12/31/2008
10:55:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[sp_DTA_query_detail_helper_relational]
@SessionID int
as
begin select 'Statement Id' =QueryID, 'Statement String'
=StatementString, 'Statement Type' = CASE
WHEN StatementType = 0 THEN 'Select'
WHEN StatementType = 1 THEN 'Update'
WHEN StatementType = 2 THEN 'Insert'
WHEN StatementType = 3 THEN 'Delete'
end,'Current Statement Cost' =CAST(CurrentCost as decimal(38,7)),
'Recommended Statement Cost' =CAST(RecommendedCost as decimal(38,7)), 'Event
String' =EventString from [msdb].[dbo].[DTA_reports_query]
where SessionID=@SessionID order by QueryID ASC end
GO
/****** Object: StoredProcedure [dbo].[sp_DTA_get_tuninglog] Script
Date: 12/31/2008 10:54:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[sp_DTA_get_tuninglog]
@SessionID int,
@XML int = 0,
@LastRowRetrieved int = 0,
@GetFrequencyForRowIDOnly int = 0

as
begin
set nocount on
declare @retval int
declare @LogTableName nvarchar(1280)
declare @DefaultTableName nvarchar(128)
declare @SQLString nvarchar(2048)


exec @retval = sp_DTA_check_permission @SessionID

if @retval = 1
begin
raiserror(31002,-1,-1)
return(1)
end

set @DefaultTableName = '[msdb].[dbo].[DTA_tuninglog]'
set @LogTableName = ' '
select top 1 @LogTableName = LogTableName from DTA_input where SessionID =
@SessionID

if (@LogTableName = ' ')
return (0)


if @XML = 0
begin

if @LogTableName = @DefaultTableName
begin

if (@GetFrequencyForRowIDOnly = 0)
begin
select CategoryID,Event,Statement,Frequency,Reason from
[msdb].[dbo].[DTA_tuninglog]
where SessionID = @SessionID and RowID > @LastRowRetrieved
order by RowID
end
else
begin
select Frequency from [msdb].[dbo].[DTA_tuninglog]
where SessionID = @SessionID and RowID > @LastRowRetrieved
order by RowID
end
return(0)
end

if (@GetFrequencyForRowIDOnly = 0)
begin
set @SQLString = N' select CategoryID,Event,Statement,Frequency,Reason
from '
end
else
begin
set @SQLString = N' select Frequency from '
end
set @SQLString = @SQLString + @LogTableName
set @SQLString = @SQLString + N' where SessionID = '
set @SQLString = @SQLString + CONVERT(nvarchar(10),@SessionID)
set @SQLString = @SQLString + N' and RowID > '
set @SQLString = @SQLString + CONVERT(nvarchar(10),@LastRowRetrieved)
set @SQLString = @SQLString + ' order by RowID'

exec (@SQLString)
end

else
begin
if @LogTableName = @DefaultTableName
begin
if (@GetFrequencyForRowIDOnly = 0)
begin
select CategoryID,Event,Statement,Frequency,Reason from
[msdb].[dbo].[DTA_tuninglog]
where SessionID = @SessionID and RowID > @LastRowRetrieved
FOR XML RAW
end
else
begin
select Frequency from [msdb].[dbo].[DTA_tuninglog]
where SessionID = @SessionID and RowID > @LastRowRetrieved
FOR XML RAW
end
return(0)
end

if (@GetFrequencyForRowIDOnly = 0)
begin
set @SQLString = N' select CategoryID,Event,Statement,Frequency,Reason
from '
end
else
begin
set @SQLString = N' select Frequency from '
end
set @SQLString = @SQLString + @LogTableName
set @SQLString = @SQLString + N' where SessionID = '
set @SQLString = @SQLString + CONVERT(nvarchar(10),@SessionID)
set @SQLString = @SQLString + N' and RowID > '
set @SQLString = @SQLString + CONVERT(nvarchar(10),@LastRowRetrieved)
set @SQLString = @SQLString + 'FOR XML RAW'

exec (@SQLString)

end
end
GO
/****** Object: StoredProcedure [dbo].[sp_DTA_add_session] Script Date:
12/31/2008 10:53:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[sp_DTA_add_session]
@SessionName sysname,
@TuningOptions ntext,
@SessionID int OUTPUT
as
declare @UserName as nvarchar(256)
declare @x_SessionName sysname
declare @ErrorString nvarchar(500)
declare @XmlDocumentHandle int
declare @retval int

set nocount on
begin transaction
-- Check for duplicate session name
select @x_SessionName = @SessionName
from msdb.dbo.DTA_input
where SessionName = @SessionName

if (@x_SessionName IS NOT NULL)
begin
rollback transaction
set @ErrorString = 'The session ' + '"' + LTRIM(RTRIM(@SessionName)) +
'"' +' already exists. Please use a different session name.'
raiserror (31001, -1,-1,@SessionName)
return(1)
end

-- Create new session

insert into msdb.dbo.DTA_input (SessionName,TuningOptions)
values (@SessionName,@TuningOptions)

select @SessionID = @@identity


if @@error <> 0
begin
rollback transaction
return @@error
end


if @@error <> 0
begin
rollback transaction
return @@error
end

-- Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @XmlDocumentHandle OUTPUT, @TuningOptions,
'<DTAXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:x="http://schemas.microsoft.com/sqlserver/2004/07/dta"/>'

if @@error <> 0
begin
rollback transaction
return @@error
end
-- Execute a SELECT statement using OPENXML rowset provider.

insert into DTA_reports_database
SELECT @SessionID,[x:Name],1
FROM OPENXML (@XmlDocumentHandle,
'/x:DTAXML/x:DTAInput/x:Server//x:Database',2)
WITH ([x:Name] nvarchar(128) )

if @@error <> 0
begin
rollback transaction
return @@error
end

EXEC sp_xml_removedocument @XmlDocumentHandle

if @@error <> 0
begin
rollback transaction
return @@error
end




-- Check if allowed to add session
exec @retval = sp_DTA_check_permission @SessionID

if @retval = 1
begin
raiserror(31003,-1,-1)
rollback transaction
return (1)
end


-- Insert progress record
insert into [msdb].[dbo].[DTA_progress]
(SessionID,WorkloadConsumption,EstImprovement,TuningStage,ConsumingWorkLoadMessage,PerformingAnalysisMessage,GeneratingReportsMessage)
values(@SessionID,0,0,0,N'',N'',N'')

if @@error <> 0
begin
rollback transaction
return @@error
end


-- Commit if input/progress records are updated
commit transaction
return 0
GO
/****** Object: StoredProcedure [dbo].[sp_DTA_delete_session] Script
Date: 12/31/2008 10:53:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[sp_DTA_delete_session]
@SessionID int
as
begin
declare @retval int
set nocount on

exec @retval = sp_DTA_check_permission @SessionID

if @retval = 1
begin
raiserror(31002,-1,-1)
return(1)
end

delete from msdb.dbo.DTA_input where SessionID=@SessionID
end
GO
/****** Object: StoredProcedure [dbo].[sp_DTA_help_session] Script Date:
12/31/2008 10:54:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[sp_DTA_help_session]
@SessionID int = 0,
@IncludeTuningOptions int = 0
as
begin
declare @tuning_owner nvarchar(256)
declare @retval int
declare @InteractiveStatus tinyint
declare @delta int

declare @cursessionID int
declare @dbname nvarchar(128)
declare @dbid int
declare @retcode int
declare @sql nvarchar(256)

set nocount on

-- List all Sessions mode
if @SessionID = 0
begin
-- If sysadmin role then rowset has all the rows in the table
-- Return everything
if (isnull(IS_SRVROLEMEMBER(N'sysadmin'), 0) = 1)
begin

if (@IncludeTuningOptions = 0)
begin
select I.SessionID, I.SessionName, I.InteractiveStatus,
I.CreationTime, I.ScheduledStartTime, O.StopTime,I.GlobalSessionID
from msdb.dbo.DTA_input I left outer join msdb.dbo.DTA_output O
on I.SessionID = O.SessionID
order by
I.SessionID desc
end

else if (@IncludeTuningOptions = 1)
begin
select I.SessionID, I.SessionName, I.InteractiveStatus,
I.CreationTime, I.ScheduledStartTime,
O.StopTime,I.TuningOptions,I.GlobalSessionID
from msdb.dbo.DTA_input I left outer join msdb.dbo.DTA_output as O
on I.SessionID = O.SessionID
order by
I.SessionID desc
end

end

else
begin
-- Temporary table to store sessionid and databases passed in by user
create table #allDistinctDbIds (DatabaseID int)
-- Init variables
set @dbid = 0
set @retcode = 1
-- Get all database names passed in by user (IsDatabaseSelectedToTune =1)
declare db_cursor cursor for
select distinct(DatabaseName) from DTA_reports_database
where IsDatabaseSelectedToTune = 1
-- Open cursor
open db_cursor
-- Fetch first session id and db name
fetch next from db_cursor
into @dbname

-- loop and get all the databases selected to tune
while @@fetch_status = 0
-- Loop
begin
-- set @retcode = 1 in the beginning to indicate success
set @retcode = 1
-- Get database id
select @dbid = DB_ID(@dbname)
-- In Yukon this masks the error messages.If not owner dont return
-- error message in SP
set @sql = N'begin try
dbcc autopilot(5,@dbid) WITH NO_INFOMSGS
end try
begin catch
set @dbid = 0
set @retcode = 0
end catch'
execute sp_executesql @sql
, N'@dbid int output, @retcode int OUTPUT'
, @dbid output
, @retcode output

-- dbid is 0 if user doesnt have permission to do dbcc call
insert into #allDistinctDbIds(DatabaseID) values
(@dbid)
-- fetch next
fetch from db_cursor into @dbname
-- end the cursor loop
end
-- clean up cursor
close db_cursor
deallocate db_cursor


select SessionID
into #allValidSessionIds
from DTA_input as I
where
((select count(*) from
#allDistinctDbIds ,DTA_reports_database as D
where #allDistinctDbIds.DatabaseID = DB_ID(D.DatabaseName)
and I.SessionID = D.SessionID
group by D.SessionID ) =
(select count(*) from DTA_reports_database as D
where I.SessionID = D.SessionID
and D.IsDatabaseSelectedToTune = 1
group by D.SessionID )
)
group by I.SessionID


-- Return only sessions with matching user name
-- If count of rows with DatabaseID = 0 is > 0 then permission denied
if ( @IncludeTuningOptions = 0 )
begin
select I.SessionID , I.SessionName, I.InteractiveStatus,
I.CreationTime, I.ScheduledStartTime, O.StopTime,I.GlobalSessionID
from msdb.dbo.DTA_input I left outer join msdb.dbo.DTA_output O
on I.SessionID = O.SessionID
inner join #allValidSessionIds S
on I.SessionID = S.SessionID


order by
I.SessionID desc
end

else if (@IncludeTuningOptions = 1)
begin
select I.SessionID , I.SessionName, I.InteractiveStatus,
I.CreationTime, I.ScheduledStartTime,
O.StopTime,I.TuningOptions,I.GlobalSessionID
from msdb.dbo.DTA_input I left outer join msdb.dbo.DTA_output O
on I.SessionID = O.SessionID
inner join #allValidSessionIds S
on I.SessionID = S.SessionID


order by
I.SessionID desc

end
drop table #allDistinctDbIds
drop table #allValidSessionIds
end
end

else
begin
exec @retval = sp_DTA_check_permission @SessionID
if @retval = 1
begin
raiserror(31002,-1,-1)
return(1)
end

if ( @IncludeTuningOptions = 0)
begin
select I.SessionID, I.SessionName, I.InteractiveStatus,
I.CreationTime, I.ScheduledStartTime, O.StopTime,I.GlobalSessionID
from msdb.dbo.DTA_input I left outer join msdb.dbo.DTA_output O
on I.SessionID = O.SessionID
where I.SessionID = @SessionID
end
else if (@IncludeTuningOptions = 1)
begin
select I.SessionID, I.SessionName, I.InteractiveStatus,
I.CreationTime, I.ScheduledStartTime,
O.StopTime,I.TuningOptions,I.GlobalSessionID
from msdb.dbo.DTA_input I left outer join msdb.dbo.DTA_output O
on I.SessionID = O.SessionID
where I.SessionID = @SessionID
end

-- Second rowset returned for DTA to process progress information
select ProgressEventID,TuningStage,WorkloadConsumption,EstImprovement,
ProgressEventTime
,ConsumingWorkLoadMessage,PerformingAnalysisMessage,GeneratingReportsMessage
from msdb.dbo.DTA_progress
where SessionID=@SessionID
order by ProgressEventID


-- Set interactive status to 6 if a time of 5 mins has elapsed
-- Next time help session is called DTA will exit

select @InteractiveStatus=InteractiveStatus
from msdb.dbo.DTA_input
where SessionID = @SessionID

if (@InteractiveStatus IS NOT NULL and( @InteractiveStatus <> 4 and
@InteractiveStatus <> 6))
begin
select @delta=DATEDIFF(minute ,ProgressEventTime,getdate())
from msdb.dbo.DTA_progress
where SessionID =@SessionID
order by TuningStage ASC

if(@delta > 30)
begin
update [msdb].[dbo].[DTA_input] set InteractiveStatus = 6
where SessionID = @SessionID
end
end


end
end
GO
/****** Object: StoredProcedure [dbo].[sp_DTA_set_interactivestatus]
Script Date: 12/31/2008 10:55:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[sp_DTA_set_interactivestatus]
@InterActiveStatus int,
@SessionID int

as
begin
declare @retval int
set nocount on

exec @retval = sp_DTA_check_permission @SessionID

if @retval = 1
begin
raiserror(31002,-1,-1)
return(1)
end

update [msdb].[dbo].[DTA_input] set InteractiveStatus = @InterActiveStatus
where SessionID = @SessionID

end
GO
-- END SCRIPT 4 of 6
 >> Stay informed about: Database Engine Tuning Advisor 

For anyone who is having this issue and is using a sys admin account, I
ran the following script to adding the missing objects to MSDB. It is
posted across 6 posts since there is a limit to the post size:

-- BEGIN SCRIPT 5 of 6
/****** Object: StoredProcedure [dbo].[sp_DTA_update_session] Script
Date: 12/31/2008 10:56:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[sp_DTA_update_session]
@SessionID int,
@SessionName sysname = NULL,
@InteractiveStatus tinyint = NULL
as
begin
declare @x_SessionName sysname
declare @x_InteractiveStatus tinyint
declare @retval int
declare @ErrorString nvarchar(500)


set nocount on
select @SessionName = LTRIM(RTRIM(@SessionName))


declare @dup_SessionName sysname

if @SessionName IS NOT NULL
begin
select @dup_SessionName = @SessionName
from msdb.dbo.DTA_input
where SessionName = @SessionName

if (@dup_SessionName IS NOT NULL)
begin
set @ErrorString = 'The session ' + '"' + LTRIM(RTRIM(@SessionName)) +
'"' +' already exists. Please use a different session name.'
raiserror (31001, -1,-1,@SessionName)
return(1)
end
end

exec @retval = sp_DTA_check_permission @SessionID
if @retval = 1
begin
raiserror(31002,-1,-1)
return(1)
end

if ((@SessionName IS NOT NULL) OR
(@InteractiveStatus IS NOT NULL)
)
begin
select @x_SessionName = SessionName,
@x_InteractiveStatus = InteractiveStatus
from msdb.dbo.DTA_input
where SessionID = @SessionID

if (@SessionName IS NULL) select @SessionName = @x_SessionName
if (@InteractiveStatus IS NULL) select @InteractiveStatus =
@x_InteractiveStatus

update msdb.dbo.DTA_input
set SessionName = @SessionName,
InteractiveStatus = @InteractiveStatus
where SessionID = @SessionID
end

end
GO
/****** Object: StoredProcedure [dbo].[sp_DTA_set_tuninglogtablename]
Script Date: 12/31/2008 10:56:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[sp_DTA_set_tuninglogtablename]
@LogTableName nvarchar(1280),
@SessionID int

as
begin
declare @retval int
set nocount on

exec @retval = sp_DTA_check_permission @SessionID

if @retval = 1
begin
raiserror(31002,-1,-1)
return(1)
end

update [msdb].[dbo].[DTA_input] set LogTableName = @LogTableName where
SessionID = @SessionID


end
GO
/****** Object: StoredProcedure [dbo].[sp_DTA_get_tuningoptions] Script
Date: 12/31/2008 10:54:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[sp_DTA_get_tuningoptions]
@SessionID int

as
begin
declare @retval int
set nocount on

exec @retval = sp_DTA_check_permission @SessionID

if @retval = 1
begin
raiserror(31002,-1,-1)
return(1)
end

select TuningOptions from [msdb].[dbo].[DTA_input] where SessionID =
@SessionID

end
GO
/****** Object: StoredProcedure [dbo].[sp_DTA_get_interactivestatus]
Script Date: 12/31/2008 10:53:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[sp_DTA_get_interactivestatus]
@SessionID int

as
begin
declare @retval int
set nocount on

exec @retval = sp_DTA_check_permission @SessionID

if @retval = 1
begin
raiserror(31002,-1,-1)
return(1)
end
select InteractiveStatus from [msdb].[dbo].[DTA_input] where SessionID =
@SessionID
end
GO
/****** Object: StoredProcedure [dbo].[sp_DTA_insert_progressinformation]
Script Date: 12/31/2008 10:54:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[sp_DTA_insert_progressinformation]
@SessionID int,
@TuningStage int
as
begin
declare @retval int
set nocount on

exec @retval = sp_DTA_check_permission @SessionID

if @retval = 1
begin
raiserror(31002,-1,-1)
return(1)
end

INSERT into [msdb].[dbo].[DTA_progress]
(SessionID,WorkloadConsumption,EstImprovement,TuningStage,ConsumingWorkLoadMessage,PerformingAnalysisMessage,GeneratingReportsMessage)
values(@SessionID,0,0,@TuningStage,N'',N'',N'')

end
GO
/****** Object: StoredProcedure [dbo].[sp_DTA_set_progressinformation]
Script Date: 12/31/2008 10:56:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[sp_DTA_set_progressinformation]
@SessionID int,
@WorkloadConsumption int,
@TuningStage int,
@EstImprovement int,
@ConsumingWorkLoadMessage nvarchar(256) = N'',
@PerformingAnalysisMessage nvarchar(256)= N'',
@GeneratingReportsMessage nvarchar(256)= N''


as
begin
declare @retval int
set nocount on

exec @retval = sp_DTA_check_permission @SessionID

if @retval = 1
begin
raiserror(31002,-1,-1)
return(1)
end
update [msdb].[dbo].[DTA_progress]
set WorkloadConsumption = @WorkloadConsumption,
EstImprovement = @EstImprovement,
ProgressEventTime = GetDate(),
ConsumingWorkLoadMessage = @ConsumingWorkLoadMessage ,
PerformingAnalysisMessage = @PerformingAnalysisMessage,
GeneratingReportsMessage = @GeneratingReportsMessage
where SessionID=@SessionID
and TuningStage = @TuningStage
end
GO
/****** Object: StoredProcedure [dbo].[sp_DTA_get_session_tuning_results]
Script Date: 12/31/2008 10:54:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[sp_DTA_get_session_tuning_results]
@SessionID int
as
begin
set nocount on
declare @retval int
exec @retval = sp_DTA_check_permission @SessionID

if @retval = 1
begin
raiserror(31002,-1,-1)
return(1)
end
select FinishStatus,TuningResults
from msdb.dbo.DTA_output
where SessionID=@SessionID
end
GO
/****** Object: StoredProcedure [dbo].[sp_DTA_set_outputinformation]
Script Date: 12/31/2008 10:56:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[sp_DTA_set_outputinformation]
@SessionID int,
@TuningResults ntext,
@FinishStatus tinyint
as
begin
declare @retval int
set nocount on

exec @retval = sp_DTA_check_permission @SessionID

if @retval = 1
begin
raiserror(31002,-1,-1)
return(1)
end

Insert into [msdb].[dbo].[DTA_output]([SessionID],
[TuningResults],[FinishStatus])
values(@SessionID,@TuningResults,@FinishStatus)
end
GO
/****** Object: StoredProcedure
[dbo].[sp_DTA_update_tuninglog_errorfrequency] Script Date: 12/31/2008
10:56:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[sp_DTA_update_tuninglog_errorfrequency]
@SessionID int,
@Frequency int,
@RowID int
as
begin
declare @retval int
set nocount on

exec @retval = sp_DTA_check_permission @SessionID

if @retval = 1
begin
raiserror(31002,-1,-1)
return(1)
end

update [msdb].[dbo].[DTA_tuninglog]
set [Frequency]=@Frequency
where [RowID]=@RowID and [SessionID] = @SessionID

end
GO
/****** Object: StoredProcedure [dbo].[sp_DTA_insert_DTA_tuninglog]
Script Date: 12/31/2008 10:54:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[sp_DTA_insert_DTA_tuninglog]
@SessionID int,
@RowID int,
@CategoryID char(4),
@Event ntext,
@Statement ntext,
@Frequency int,
@Reason ntext
as
begin
declare @retval int
set nocount on

exec @retval = sp_DTA_check_permission @SessionID

if @retval = 1
begin
raiserror(31002,-1,-1)
return(1)
end
insert into [msdb].[dbo].[DTA_tuninglog]([SessionID], [RowID],
[CategoryID], [Event], [Statement], [Frequency], [Reason])
values(@SessionID, @RowID, @CategoryID, @Event, @Statement, @Frequency,
@Reason)
end
GO
/****** Object: StoredProcedure [dbo].[sp_DTA_get_databasetableids]
Script Date: 12/31/2008 10:53:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[sp_DTA_get_databasetableids]
@SessionID int
as
begin
declare @retval int
set nocount on

exec @retval = sp_DTA_check_permission @SessionID

if @retval = 1
begin
raiserror(31002,-1,-1)
return(1)
end

select DatabaseID,DatabaseName
from [msdb].[dbo].[DTA_reports_database] as D
where D.SessionID = @SessionID

end
GO
/****** Object: StoredProcedure [dbo].[sp_DTA_get_pstableids] Script
Date: 12/31/2008 10:53:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[sp_DTA_get_pstableids]
@SessionID int
as
begin
declare @retval int
set nocount on

exec @retval = sp_DTA_check_permission @SessionID

if @retval = 1
begin
raiserror(31002,-1,-1)
return(1)
end

select PartitionSchemeID,DatabaseName,PartitionSchemeName
from [msdb].[dbo].[DTA_reports_partitionfunction] as PF,
[msdb].[dbo].[DTA_reports_partitionscheme] as PS,
[msdb].[dbo].[DTA_reports_database] as D
where PS.PartitionFunctionID = PF.PartitionFunctionID and
PF.DatabaseID = D.DatabaseID and D.SessionID = @SessionID

end
GO
/****** Object: StoredProcedure [dbo].[sp_DTA_get_indexableids] Script
Date: 12/31/2008 10:53:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[sp_DTA_get_indexableids]
@SessionID int
as
begin
declare @retval int
set nocount on

exec @retval = sp_DTA_check_permission @SessionID

if @retval = 1
begin
raiserror(31002,-1,-1)
return(1)
end

select IndexID,DatabaseName,SchemaName,TableName,IndexName,SessionUniquefier
from [msdb].[dbo].[DTA_reports_index] as I,[msdb].[dbo].[DTA_reports_table]
as T,
[msdb].[dbo].[DTA_reports_database] as D
where I.TableID = T.TableID and T.DatabaseID = D.DatabaseID and D.SessionID
= @SessionID

end
GO
/****** Object: StoredProcedure [dbo].[sp_DTA_get_pftableids] Script
Date: 12/31/2008 10:53:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[sp_DTA_get_pftableids]
@SessionID int
as
begin
declare @retval int
set nocount on

exec @retval = sp_DTA_check_permission @SessionID

if @retval = 1
begin
raiserror(31002,-1,-1)
return(1)
end

select PartitionFunctionID ,DatabaseName ,PartitionFunctionName
from [msdb].[dbo].[DTA_reports_partitionfunction] as PF,
[msdb].[dbo].[DTA_reports_database] as D
where PF.DatabaseID = D.DatabaseID
and D.SessionID = @SessionID

end
GO
/****** Object: StoredProcedure [dbo].[sp_DTA_get_tableids] Script Date:
12/31/2008 10:54:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[sp_DTA_get_tableids]
@SessionID int
as
begin
declare @retval int
set nocount on

exec @retval = sp_DTA_check_permission @SessionID

if @retval = 1
begin
raiserror(31002,-1,-1)
return(1)
end

select TableID,DatabaseName,SchemaName,TableName
from [msdb].[dbo].[DTA_reports_table] as
T,[msdb].[dbo].[DTA_reports_database] as D
where T.DatabaseID = D.DatabaseID and D.SessionID = @SessionID


end
GO
/****** Object: StoredProcedure [dbo].[sp_DTA_get_columntableids] Script
Date: 12/31/2008 10:53:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[sp_DTA_get_columntableids]
@SessionID int
as
begin
declare @retval int
set nocount on

exec @retval = sp_DTA_check_permission @SessionID

if @retval = 1
begin
raiserror(31002,-1,-1)
return(1)
end

select ColumnID,DatabaseName,SchemaName,TableName,ColumnName
from [msdb].[dbo].[DTA_reports_column] as C,
[msdb].[dbo].[DTA_reports_table] as T,[msdb].[dbo].[DTA_reports_database]
as D
where C.TableID = T.TableID and T.DatabaseID = D.DatabaseID and D.SessionID
= @SessionID


end
GO
/****** Object: StoredProcedure [dbo].[sp_DTA_insert_reports_database]
Script Date: 12/31/2008 10:54:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[sp_DTA_insert_reports_database]
@SessionID int,
@DatabaseName sysname,
@IsDatabaseSelectedToTune int
as
begin
declare @retval int
set nocount on

exec @retval = sp_DTA_check_permission @SessionID

if @retval = 1
begin
raiserror(31002,-1,-1)
return(1)
end

Insert into
[msdb].[dbo].[DTA_reports_database]([SessionID],[DatabaseName],[IsDatabaseSelectedToTune]) values(@SessionID,@DatabaseName,@IsDatabaseSelectedToTune)
end
GO
/****** Object: StoredProcedure
[dbo].[sp_DTA_insert_reports_partitionfunction] Script Date: 12/31/2008
10:54:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[sp_DTA_insert_reports_partitionfunction]
@SessionID int,
@DatabaseID int,
@PartitionFunctionName sysname,
@PartitionFunctionDefinition ntext
as
begin
declare @retval int
set nocount on

exec @retval = sp_DTA_check_permission @SessionID

if @retval = 1
begin
raiserror(31002,-1,-1)
return(1)
end

Insert into
[msdb].[dbo].[DTA_reports_partitionfunction]([DatabaseID],[PartitionFunctionName],[PartitionFunctionDefinition])
values(@DatabaseID,@PartitionFunctionName,@PartitionFunctionDefinition)
end
GO
/****** Object: StoredProcedure
[dbo].[sp_DTA_insert_reports_partitionscheme] Script Date: 12/31/2008
10:55:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[sp_DTA_insert_reports_partitionscheme]
@SessionID int,
@PartitionFunctionID int,
@PartitionSchemeName sysname,
@PartitionSchemeDefinition ntext
as
begin
declare @retval int
set nocount on

exec @retval = sp_DTA_check_permission @SessionID

if @retval = 1
begin
raiserror(31002,-1,-1)
return(1)
end

Insert into [msdb].[dbo].[DTA_reports_partitionscheme](
[PartitionFunctionID],[PartitionSchemeName],[PartitionSchemeDefinition])
values(@PartitionFunctionID,@PartitionSchemeName,@PartitionSchemeDefinition)
end
GO
/****** Object: StoredProcedure [dbo].[sp_DTA_insert_reports_table]
Script Date: 12/31/2008 10:55:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[sp_DTA_insert_reports_table]
@SessionID int,
@DatabaseID int,
@SchemaName sysname,
@TableName sysname,
@IsView bit
as
begin
declare @retval int
set nocount on

exec @retval = sp_DTA_check_permission @SessionID

if @retval = 1
begin
raiserror(31002,-1,-1)
return(1)
end
insert into [msdb].[dbo].[DTA_reports_table]([DatabaseID], [SchemaName],
[TableName], [IsView])
values(@DatabaseID,@SchemaName,@TableName,@IsView)
end
GO
/****** Object: StoredProcedure [dbo].[sp_DTA_insert_reports_tableview]
Script Date: 12/31/2008 10:55:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[sp_DTA_insert_reports_tableview]
@SessionID int,
@TableID int,
@ViewID int
as
begin
declare @retval int
set nocount on

exec @retval = sp_DTA_check_permission @SessionID

if @retval = 1
begin
raiserror(31002,-1,-1)
return(1)
end

insert into [msdb].[dbo].[DTA_reports_tableview]([TableID], [ViewID])
values(@TableID,@ViewID)


end
GO
/****** Object: StoredProcedure [dbo].[sp_DTA_insert_reports_query]
Script Date: 12/31/2008 10:55:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[sp_DTA_insert_reports_query]
@SessionID int,
@QueryID int,
@StatementType smallint,
@StatementString ntext,
@CurrentCost float,
@RecommendedCost float,
@Weight float,
@EventString ntext,
@EventWeight float
as
begin
declare @retval int
set nocount on

exec @retval = sp_DTA_check_permission @SessionID

if @retval = 1
begin
raiserror(31002,-1,-1)
return(1)
end

insert into [msdb].[dbo].[DTA_reports_query]([SessionID],[QueryID],
[StatementType], [StatementString], [CurrentCost], [RecommendedCost],
[Weight], [EventString], [EventWeight])
values(@SessionID,@QueryID,@StatementType,@StatementString,@CurrentCost,@RecommendedCost,@Weight,@EventString,@EventWeight)


end
GO
/****** Object: StoredProcedure [dbo].[sp_DTA_insert_reports_querytable]
Script Date: 12/31/2008 10:55:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[sp_DTA_insert_reports_querytable]
@SessionID int,
@QueryID int,
@TableID int
as
begin
declare @retval int
set nocount on

exec @retval = sp_DTA_check_permission @SessionID

if @retval = 1
begin
raiserror(31002,-1,-1)
return(1)
end
insert into [msdb].[dbo].[DTA_reports_querytable]([SessionID],
[QueryID],[TableID])
values(@SessionID,@QueryID,@TableID)
end
GO
/****** Object: StoredProcedure [dbo].[sp_DTA_insert_reports_querydatabase]
Script Date: 12/31/2008 10:55:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[sp_DTA_insert_reports_querydatabase]
@SessionID int,
@QueryID int,
@DatabaseID int
as
begin
declare @retval int
set nocount on

exec @retval = sp_DTA_check_permission @SessionID

if @retval = 1
begin
raiserror(31002,-1,-1)
return(1)
end
insert into [msdb].[dbo].[DTA_reports_querydatabase]([SessionID],
[QueryID],[DatabaseID])
values(@SessionID,@QueryID,@DatabaseID)
end
GO
/****** Object: StoredProcedure [dbo].[sp_DTA_insert_reports_index]
Script Date: 12/31/2008 10:54:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[sp_DTA_insert_reports_index]
@SessionID int,
@TableID int,
@IndexName sysname,
@IsClustered bit,
@IsUnique bit,
@IsHeap bit,
@IsExisting bit,
@Storage int,
@NumRows int,
@IsRecommended bit,
@RecommendedStorage int,
@PartitionSchemeID int,
@SessionUniquefier int
as
begin
declare @retval int
set nocount on

exec @retval = sp_DTA_check_permission @SessionID

if @retval = 1
begin
raiserror(31002,-1,-1)
return(1)
end
insert into [msdb].[dbo].[DTA_reports_index]([TableID], [IndexName],
[IsClustered], [IsUnique], [IsHeap],[IsExisting], [Storage], [NumRows],
[IsRecommended], [RecommendedStorage],
[PartitionSchemeID],[SessionUniquefier])
values(@TableID,@IndexName,@IsClustered,@IsUnique,@IsHeap,@IsExisting,@Storage,@NumRows,@IsRecommended,@RecommendedStorage,@PartitionSchemeID,@SessionUniquefier)
end
GO
-- END SCRIPT 5 of 6
 >> Stay informed about: Database Engine Tuning Advisor 

Hello James,

Thank you for public the script to create the stored procedure
'msdb..sp_DTA_help_session'. I'm glad that the issue is reslved by yourself.

If you have any concerns or questions on the issue, please tell me.

Have a nice day.

Hope our next cooperation.

Best regards,
Mark Han
Microsoft Online Community Support
=========================================================
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg RemoveThis @microsoft.com.
=========================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================
 >> Stay informed about: Database Engine Tuning Advisor