This script does not run
DECLARE [Email address protected] varchar(25)
DECLARE [Email address protected] varchar(255)
DECLARE [Email address protected] varchar(1000)
DECLARE TableCursor CURSOR FAST_FORWARD FOR
SELECT DISTINCT t.TABLE_SCHEMA, t.TABLE_NAME
FROM information_schema.Tables t INNER JOIN
information_schema.Columns c on t.TABLE_NAME =
c.TABLE_NAME
WHERE t.TABLE_TYPE = 'base table' and
( c.DATA_TYPE not IN ('text','ntext','image','xml') OR
CHARACTER_MAXIMUM_LENGTH = -1 ) ORDER BY TABLE_NAME
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO [Email address protected] [Email
address protected]
WHILE [Email address protected] = 0
BEGIN
SET [Email address protected] = N'ALTER INDEX ALL ON ' + [Email
address protected] + '.' + [Email address protected] + ' REBUILD WITH
(FILLFACTOR = 80, STATISTICS_NORECOMPUTE = OFF, ONLINE = ON)';
EXEC [Email address protected]
FETCH NEXT FROM TableCursor INTO [Email address protected] [Email
address protected] END
CLOSE TableCursor
DEALLOCATE TableCursor
Try this.
-----Original Message-----
From: Dwarakanathan Kavitha
[mailto:mssqldba-ezmlmshield-x12016866.[Email address protected]
Sent: Wednesday, December 26, 2007 4:00 PM
To: LazyDBA Discussion
Subject: RE: Syntax error - 2nd attach
comment the exec and do a select @command and let us know the output of
"select @command". This should give a lead on where the error is
occurring.
-Kavitha
-----Original Message-----
From: Mario Martinez
[mailto:mssqldba-ezmlmshield-x10706888.[Email address protected]
Sent: Wednesday, December 26, 2007 3:44 PM
To: LazyDBA Discussion
Subject: RE: Syntax error - 2nd attach
I liked this route. I fixed an ambiguity But once I run it I get the
same beautiful error Msg 156, Level 15, State 1, Line 1 Incorrect syntax
near the keyword 'INDEX'.
Repeated each time EXEC (@command); is called
DECLARE @TableSchema varchar(25)
DECLARE @TableName varchar(255)
DECLARE @command varchar(1000)
DECLARE TableCursor CURSOR FAST_FORWARD FOR
SELECT DISTINCT t.TABLE_SCHEMA, t.TABLE_NAME
FROM information_schema.Tables t INNER JOIN
information_schema.Columns c on t.TABLE_NAME =
c.TABLE_NAME
WHERE t.TABLE_TYPE = 'base table' and
( c.DATA_TYPE not IN ('text','ntext','image','xml') OR
CHARACTER_MAXIMUM_LENGTH = -1 ) ORDER BY t.TABLE_NAME
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableSchema, @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @command = N'ALTER INDEX ALL ON ' + @TableSchema + '.' +
@TableName + ' REBUILD WITH (FILLFACTOR = 80, STATISTICS_NORECOMPUTE =
OFF, ONLINE = ON)';
EXEC (@command);
FETCH NEXT FROM TableCursor INTO @TableSchema, @TableName END
CLOSE TableCursor
DEALLOCATE TableCursor
Thanks for your support
-----Original Message-----
From: Bellizzi Christopher Joseph
[mailto:mssqldba-ezmlmshield-x4246306.[Email address protected]
Sent: Wednesday, December 26, 2007 3:32 PM
To: LazyDBA Discussion
Subject: RE: Syntax error - 2nd attach
You can't use an attachment to the list.
PAYCHEX
Christopher Bellizzi
Information Technology * Database Administration
675 Basket Road * Webster, New York 14580
(585) 216-0670 * [Email address protected]
-----Original Message-----
From: Dwarakanathan Kavitha
[mailto:mssqldba-ezmlmshield-x33652031.[Email address protected]
Sent: Wednesday, December 26, 2007 2:53 PM
To: LazyDBA Discussion
Subject: RE: Syntax error - 2nd attach
the second attachment
- Kavitha
-----Original Message-----
From: Mario Martinez
[mailto:mssqldba-ezmlmshield-x34828484.[Email address protected]
Sent: Wednesday, December 26, 2007 2:14 PM
To: LazyDBA Discussion
Subject: RE: Syntax error
I'm using SQL 2005 and I the idea is to run a script for maintenance of
my indices that is going to run at the end of the day Something like
this:
declare @tablename char(255)
DECLARE t_cursor CURSOR for
select 'ALTER INDEX ALL ON ' + name + ' REBUILD WITH (ONLINE =
ON)'
from
sysobjects
where xtype = 'U'
set NOCOUNT on
open t_cursor
FETCH NEXT FROM t_cursor INTO @tablename
while (@@fetch_status <> -1)
begin
if (@@fetch_status <> -2)
begin
exec (@tablename)
end
FETCH NEXT FROM t_cursor into @tablename
end
DEALLOCATE t_cursor
Regards
Mario
-----Original Message-----
From: Pete
[mailto:mssqldba-ezmlmshield-x51185639.[Email address protected]
Sent: Wednesday, December 26, 2007 12:30 PM
To: LazyDBA Discussion
Subject: Re: Syntax error
Good catch..
btw, I mis-typed earlier..must be the excess eggnog in my system. The
prefix convention is [Tablename].IndexName, not DatabaseName.TableName
Happy New Year everyone.
Bellizzi Christopher Joseph wrote:
> Yeah good catch guys didn't get the pubs reference until you said
>
> something.
>
> Alter will work on Developer edition as it's essentially enterprise
>
> anyways.
>
>
>
> PAYCHEX
>
> Christopher Bellizzi
>
> Information Technology * Database Administration
>
> 675 Basket Road * Webster, New York 14580
>
> (585) 216-0670 * [Email address protected]
>
>
>
>
>
> -----Original Message-----
>
> From: Dwarakanathan Kavitha
>
> [mailto:mssqldba-ezmlmshield-x34993759.[Email address protected]
>
> Sent: Wednesday, December 26, 2007 12:20 PM
>
> To: LazyDBA Discussion
>
> Subject: RE: Syntax error
>
>
>
> Make sure of few things before you use the following ALTER INDEX
>
> command.
>
>
>
> 1) Primarily, I believe you are using only SQL 2005. Since the example
>
> you have mentioned here is using pubs db (which is not available by
>
> default in SQL 2005), I had this suspicion of whether you are
executing
>
> the cmd in SQL 2005 or SQL 2000.
>
>
>
> 2) If you are using sql 2005, then ONLINE = ON will work only on
>
> Enterprise Edition
>
>
>
> 3)REBUILD with ONLINE = ON will not succeed if the table has
>
> 1) XML index
>
> 2) Large object data type columns: image, text, ntext,
>
> varchar(max), nvarchar(max), varbinary(max), and xml.
>
>
>
>
>
> Kavitha Dwarakanathan
>
> 908-563-2420
>
> Sr.Database Administrator/AVP
>
> 24x7 hotline: 908-563-4364
>
> CMB Equities DBA Group
>
> Citi Markets & Banking (CMB) | Technology
>
>
>
> -----Original Message-----
>
> From: Mario Martinez
>
> [mailto:mssqldba-ezmlmshield-x54947819.[Email address protected]
>
> Sent: Wednesday, December 26, 2007 11:10 AM
>
> To: LazyDBA Discussion
>
> Subject: RE: Syntax error
>
>
>
> I Still stuck with the same error:
>
> Incorrect syntax
>
> near the keyword 'INDEX'.
>
>
>
>
>
> ALTER INDEX ALL ON pubs.dbo.titleauthor REBUILD WITH (FILLFACTOR =
>
> 80,SORT_IN_TEMPDB = ON,STATISTICS_NORECOMPUTE = OFF);
>
>
>
> Regards
>
>
>
>
>
>
>
> -----Original Message-----
>
> From: Pete
>
> [mailto:mssqldba-ezmlmshield-x90379321.[Email address protected]
>
> Sent: Wednesday, December 26, 2007 10:57 AM
>
> To: LazyDBA Discussion
>
> Subject: Re: Syntax error
>
>
>
> When changing, rebuilding, or dropping an index the database name has
to
>
>
>
> prefix the index such as
>
> pubs.titleauthor
>
>
>
> Try adding the database as a prefix to the table and it should work
>
> fine.
>
>
>
> Mario Martinez wrote:
>
>
>> Hi people:
>>
>
>
>> Can any body find the syntax error I hace in this sentence:
>>
>
>
>
>
>> ALTER INDEX ALL ON titleauthor REBUILD WITH (ONLINE = ON,FILLFACTOR =
>>
>
>
>> 80,SORT_IN_TEMPDB = ON,STATISTICS_NORECOMPUTE = ON);
>>
>
>
>
>
>> Regards
>>
>
>
>> Mark
>>
>
>
>
>
>
>
>
>
>
>
>> ---------------------------------------------------------------------
>>
>
>
>> TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To
post
>>
>
>
>
>
>> a dba job: http://jobs.lazydba.com To subscribe :
>>
>
>
>> http://www.LazyDBA.com To unsubscribe:
>>
>
>
>> http://www.lazydba.com/unsubscribe.html
>>
>
>
>
>
>
>
>>
>>
>
>
>
>
>
>
>
> ---------------------------------------------------------------------
>
> TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To post
a
>
> dba job: http://jobs.lazydba.com To subscribe : http://www.LazyDBA.com
>
> To unsubscribe: http://www.lazydba.com/unsubscribe.html
>
>
>
>
>
>
>
> ---------------------------------------------------------------------
>
> TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To post
a
>
> dba job: http://jobs.lazydba.com To subscribe : http://www.LazyDBA.com
>
> To unsubscribe: http://www.lazydba.com/unsubscribe.html
>
>
>
>
>
>
>
> ---------------------------------------------------------------------
>
> TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
>
> To post a dba job: http://jobs.lazydba.com
>
> To subscribe : http://www.LazyDBA.com
>
> To unsubscribe: http://www.lazydba.com/unsubscribe.html
>
>
>
>
>
>
>
>
>
> -----------------------------------------
>
> The information contained in this message may be privileged,
> confidential, and protected from disclosure. If the reader of this
> message is not the intended recipient, or any employee or agent
> responsible for delivering this message to the intended recipient, you
> are hereby notified that any dissemination, distribution, or copying
> of this communication is strictly prohibited. If you have received
> this communication in error, please notify us immediately by replying
> to the message and deleting it from your computer.
>
>
>
> Thank you. Paychex, Inc.
>
>
> ---------------------------------------------------------------------
> TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To post
> a dba job: http://jobs.lazydba.com To subscribe :
> http://www.LazyDBA.com To unsubscribe:
> http://www.lazydba.com/unsubscribe.html
>
>
>
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To post a
dba job: http://jobs.lazydba.com To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To post a
dba job: http://jobs.lazydba.com To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
MS Sql Server LazyDBA home page