Looks like there was a typo in Step 2. Here's the new script.

/****** Object: StoredProcedure [dbo].[SelectAllServersFragmentedTables] Script Date: 09/12/2011 09:07:05 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- ==========================================================================

-- Author: Kimberly Killian

-- Create date: 5/19/11

-- Description:Get the fragmented index's in each server/database (laptops)

-- ==========================================================================

CREATE procedure [dbo].[SelectAllServersFragmentedTables]

@p_FragmentedLimit decimal = 5.0

as

declare @err int,

@rc int,

@cmd varchar(8000),

@cmd2 varchar(8000),

@ServerName sysname,

@cmdmailvarchar(50),

@cmdSprocDefrag varchar (50)

set nocount on

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ServerList_frag]') AND type in (N'U'))

DROP TABLE [dbo].[ServerList_frag]

CREATE TABLE [dbo].[ServerList_frag](

[Server] [varchar](128) NOT NULL,

CONSTRAINT [PK_SSIS_ServerList_frag] PRIMARY KEY CLUSTERED

([Server] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 99) ON [PRIMARY]

) ON [PRIMARY]

-- Load table with server names

-- This will only work if XP_CMDSHELL is Enabled

Create table #Server ( [Server] [varchar](128) )

Insert Into #Server

Exec xp_cmdshell 'sqlcmd /Lc'

Insert Into SSIS_ServerList_frag ([Server])

select [Server] from #Server where SERVER in (select [SERVER] from dbo.ServerList where Skip_SQL_Overview = 'false')

DROP Table #Server

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

-- Truncate the central table with the fragmentation information

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

TRUNCATE TABLE Fragmented_reorg_queries

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

-- Loop on Server list and find fragmented tables where they haven't cleaned up for at least 14 days

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

declare SrvCursB cursor for

select Server from dbo.ServerList_frag where SERVER not in

(select distinct ServerName from dbo.Fragmented_reorg_queries_history group by servername having max(LastChecked) >= getdate()-14)

open SrvCursB

fetch SrvCursB into @ServerName

while @@FETCH_STATUS = 0

BEGIN

--send warning email to remote servers

select @cmdmail = 'sendemail_do_not_disconnect ' + @ServerName

exec (@cmdmail)

-- Create a table in tempdb that will be populated at the remote

-- server and later be accessed from the central database

-- (Command passed to osql should be in one line, no <CR>):

--Check DB Consistency

select @cmd = 'DBCC CHECKDB WITH NO_INFOMSGS;' +

'if exists (select 1 from ' + @servername + '.tempdb.dbo.sysobjects ' +

'where type = ''U'' AND NAME = ''fraglist'') ' +

'drop table tempdb.dbo.fraglist;' +

'CREATE TABLE tempdb.dbo.fraglist (' +

'ServerName sysname,' +

'DatabaseName sysname,' +

'ObjectName CHAR (255),' +

'ObjectId INT,' +

'IndexName CHAR (255),' +

'IndexId INT,' +

'Lvl INT,' +

'CountPages INT,' +

'CountRows INT, ' +

'MinRecSize INT,' +

'MaxRecSize INT,' +

'AvgRecSize INT,' +

'ForRecCount INT,' +

'Extents INT,' +

'ExtentSwitches INT,' +

'AvgFreeBytes INT,' +

'AvgPageDensity INT,' +

'ScanDensity DECIMAL,' +

'BestCount INT,' +

'ActualCount INT,' +

'LogicalFrag DECIMAL,' +

'ExtentFrag DECIMAL) '

select @cmd2 = 'sqlcmd -E -S"' + @ServerName + '" -Q "' +

@cmd + '"'

exec master..xp_cmdshell @cmd2

select @err = @@error

IF @err <> 0

begin

select 'ERROR Creating the table in tempdb for SERVER - '+

@ServerName

return -1

end

/*Query the fragmenation data into a table on tempdb for each remote server

Insert fragmentation information into a temporary table.

Insert fragmentation information including server name and database name into

the remote table created in tempdb.

Create defrag/reindex queries and write to a table to be used later.

Write defrag/reindex queries to a history table to be used later*/

select @cmd =

'exec [' + @ServerName + '].master.dbo.sp_msforeachdb ' +

'''use [?]; print ''''?'''';

if ''''?'''' in (''''tempdb'''',''''msdb'''',''''master'''',

''''model'''',''''Northwind5'''',''''pubs'''',''''ReportServerTempDB'''',''''ReportServer'''')

return

create table #t (cmd varchar(max));

insert into #t select

''''SET ARITHABORT ON

SET QUOTED_IDENTIFIER ON;

dbcc showcontig() with tableresults, all_indexes;''''

declare @cmd varchar(max)

declare curs_tmp cursor for select cmd from #t

open curs_tmp

fetch curs_tmp into @cmd

while @@fetch_status = 0

begin

select ObjectName, ObjectId, IndexName, IndexId, Lvl,

CountPages, CountRows, MinRecSize, MaxRecSize,

AvgRecSize, ForRecCount, Extents, ExtentSwitches,

AvgFreeBytes, AvgPageDensity, ScanDensity,

BestCount, ActualCount, LogicalFrag, ExtentFrag

into #fraglist from tempdb.dbo.fraglist where 1=2

insert into #fraglist exec (@cmd)

insert into tempdb.dbo.fraglist

select ''''' + @ServerName + ''''',

db_name(),* from #fraglist

WHERE IndexID not in (0,255)

And LogicalFrag > ''''' +

convert(varchar(10),@p_FragmentedLimit) +

'''''

drop table #fraglist

fetch curs_tmp into @cmd

end

close curs_tmp

deallocate curs_tmp

drop table #t;

SET ARITHABORT OFF

SET QUOTED_IDENTIFIER OFF;'''

exec (@cmd)

select @err = @@error

IF @err <> 0

begin

select 'ERROR Collecting fragmentation information for ' +

'SERVER - ' + @ServerName

return -1

end

-- Insert the fragmentation details to the central (local) table

select @cmd = 'insert into FragmentedTables select * from ['+

@ServerName + '].tempdb.dbo.fraglist'

exec (@cmd)

select @err = @@error

IF @err <> 0

begin

select 'ERROR selecting data from remote table for ' +

'SERVER - ' + @ServerName

return -1

end

--get the fragmented tables and generate scripts

--save the history

select @cmd = 'INSERT INTO [SQL_Overview].[dbo].[Fragmented_reorg_queries]

([ServerName] ,[DatabaseName] ,[query])

SELECT ServerName, DatabaseName,

''ALTER INDEX'' + '' '' + RTRIM(IndexName) + '' '' + ''ON '' + RTRIM([ObjectName]) + '' '' + ''REORGANIZE'' as query

FROM [SQL_Overview].[dbo].[FragmentedTables]

where LogicalFrag between 5 and 30

INSERT INTO [SQL_Overview].[dbo].[Fragmented_reorg_queries]

([ServerName] ,[DatabaseName] ,[query])

SELECT ServerName, DatabaseName,

''ALTER INDEX'' + '' '' + RTRIM(IndexName) + '' '' + ''ON '' + RTRIM([ObjectName]) + '' '' + ''REBUILD'' as query

FROM [SQL_Overview].[dbo].[FragmentedTables]

where LogicalFrag >= 31

INSERT INTO [SQL_Overview].[dbo].[Fragmented_reorg_queries_history]

([ServerName] ,[DatabaseName] , [Query], [LastChecked])

SELECT [ServerName], [DatabaseName], [query], GETDATE() as LastChecked

FROM [SQL_Overview].[dbo].[Fragmented_reorg_queries]'

exec (@cmd)

--Run the queries generated for each server on each server

select @cmdSprocDefrag = 'defrag_Fragmented_tables ' + @ServerName

exec(@cmdSprocDefrag)

-- Drop the remote table in tempdb

select @cmd =

'if exists (select 1 from tempdb.dbo.sysobjects ' +

'where type = ''U'' AND NAME = ''fraglist'') ' +

'drop table tempdb.dbo.fraglist'

select @cmd2 = 'sqlcmd -E -S"' + @ServerName + '" -Q "' + @cmd +

'"'

exec master..xp_cmdshell @cmd2

select @err = @@error

IF @err <> 0

begin

select 'ERROR dropping remote table for SERVER - ' +

@ServerName

return -1

end

--run query to check level of fragmentation after defrag

--same as above

select @cmd = 'if exists (select 1 from tempdb.dbo.sysobjects ' +

'where type = ''U'' AND NAME = ''fraglistafter'') ' +

'drop table tempdb.dbo.fraglistafter;' +

'CREATE TABLE tempdb.dbo.fraglistafter (' +

'ServerName sysname,' +

'DatabaseName sysname,' +

'ObjectName CHAR (255),' +

'ObjectId INT,' +

'IndexName CHAR (255),' +

'IndexId INT,' +

'Lvl INT,' +

'CountPages INT,' +

'CountRows INT, ' +

'MinRecSize INT,' +

'MaxRecSize INT,' +

'AvgRecSize INT,' +

'ForRecCount INT,' +

'Extents INT,' +

'ExtentSwitches INT,' +

'AvgFreeBytes INT,' +

'AvgPageDensity INT,' +

'ScanDensity DECIMAL,' +

'BestCount INT,' +

'ActualCount INT,' +

'LogicalFrag DECIMAL,' +

'ExtentFrag DECIMAL) '

select @cmd2 = 'sqlcmd -E -S"' + @ServerName + '" -Q "' +

@cmd + '"'

exec master..xp_cmdshell @cmd2

select @err = @@error

IF @err <> 0

begin

select 'ERROR Creating the table in tempdb for SERVER - '+

@ServerName

return -1

end

/*Query the fragmenation data into a table on tempdb for each remote server

Insert fragmentation information into a temporary table.

Insert fragmentation information including server name and database name into

the remote table created in tempdb. */

select @cmd =

'exec [' + @ServerName + '].master.dbo.sp_msforeachdb ' +

'''use [?]; print ''''?'''';

if ''''?'''' in (''''tempdb'''',''''msdb'''',''''master'''',

''''model'''',''''Northwind5'''',''''pubs'''')

return

create table #a (cmd varchar(max));

insert into #a select

''''SET ARITHABORT ON

SET QUOTED_IDENTIFIER ON;

dbcc showcontig() with tableresults, all_indexes;''''

declare @cmd varchar(max)

declare curs_tmpafter cursor for select cmd from #a

open curs_tmpafter

fetch curs_tmpafter into @cmd

while @@fetch_status = 0

begin

select ObjectName, ObjectId, IndexName, IndexId, Lvl,

CountPages, CountRows, MinRecSize, MaxRecSize,

AvgRecSize, ForRecCount, Extents, ExtentSwitches,

AvgFreeBytes, AvgPageDensity, ScanDensity,

BestCount, ActualCount, LogicalFrag, ExtentFrag

into #fraglistafter from tempdb.dbo.fraglistafter where 1=2

insert into #fraglistafter exec (@cmd)

insert into tempdb.dbo.fraglistafter

select ''''' + @ServerName + ''''',

db_name(),* from #fraglistafter

WHERE IndexID not in (0,255)

And LogicalFrag > ''''' +

convert(varchar(10),@p_FragmentedLimit) +

'''''

drop table #fraglistafter

fetch curs_tmpafter into @cmd

end

close curs_tmpafter

deallocate curs_tmpafter

drop table #a;

SET ARITHABORT OFF

SET QUOTED_IDENTIFIER OFF'''

exec (@cmd)

select @err = @@error

IF @err <> 0

begin

select 'ERROR Collecting fragmentation information for ' +

'SERVER - ' + @ServerName

return -1

end

-- Insert the fragmentation details to the central (local) table

select @cmd = 'insert into FragmentedTablesAfterDefragRun select * from ['+

@ServerName + '].tempdb.dbo.fraglistafter'

exec (@cmd)

select @err = @@error

IF @err <> 0

begin

select 'ERROR selecting data from remote table for ' +

'SERVER - ' + @ServerName

return -1

end

-- Drop the remote table in tempdb, update system index's if needed then run update stats

select @cmd =

'if exists (select 1 from tempdb.dbo.sysobjects ' +

'where type = ''U'' AND NAME = ''fraglistafter'') ' +

'drop table tempdb.dbo.fraglistafter; EXEC sp_updatestats;'

select @cmd2 = 'sqlcmd -E -S"' + @ServerName + '" -Q "' + @cmd +

'"'

exec master..xp_cmdshell @cmd2

select @err = @@error

IF @err <> 0

begin

select 'ERROR dropping remote table for SERVER - ' +

@ServerName

return -1

end

--send completion email to remote servers

select @cmdmail = 'sendemail_All_Clear ' + @ServerName

exec (@cmdmail)

fetch SrvCursB into @ServerName

END

close SrvCursB

deallocate SrvCursB

GO