February 6, 2007 at 11:22 am
Hello!
As a novice with regard to SQL, I am doing what I can to make my Maintenance Plans and backups as trouble-free as possible. I have been using an excellent article and scripts to selectively defragment indexes on my SQL 2000 Servers.
See here:
http://www.sql-server-performance.com/tp_automatic_reindexing.asp
I am having issue with using it in SQL 2005, though.
There is a particular database that throws the following error when I run the script against it:
"Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon."
All other databases are fine when I run it against them.
Another issue: I usually run it using spMsForEachDb and package it in a job. In this case, when the ? in the sp_MSforEachdb statement is surrounded by quotes, I get an error as well. "Incorrect syntax near 'master'. " As follows:
sp_MSForEachDB 'Use "?" exec sp_defragment_indexes 95.00'
Otherwise, if I take the quotes away, it runs OK except for the aforementioned db. I know, though, that if I do not use quotes, it will choke on any dbs with a space in the name.
February 9, 2007 at 8:00 am
This was removed by the editor as SPAM
October 8, 2007 at 1:58 pm
Essentially, it does not seem to like any index with a
-
in the name, nor does it like any database with a
-
in the name.
Such as
database-name
or
IX_name-name
If I run the procedure on individual databases with that character in the name, and enclose them in quotes, that works. Of course, if any index in that db uses that character in its name, then that index will bomb.
If I package the process into a job using SP_MSforEachdb, as follows:
sp_MSForEachDB 'Use ? exec sp_defragment_indexes 95.00'
Then it will bomb on any db with that character in the database name. If I enclose the ? in quotes, as follows:
sp_MSForEachDB 'Use "?" exec sp_defragment_indexes 95.00'
I get
incorrect syntax near 'master'
and the job will not run.
Very confused.
October 9, 2007 at 7:09 am
Typically you run defragment on only certain databases, like user databases, so you'd want to run a loop or use a list. That way you aren't messing with system databases.
I'd suggest two things. One is you build a loop of your user databases using sys.databases. Then I'd pass the database name into the script using the QUOTENAME function. A lot of times this solves issues with names that have strange characters or spaces in there.
Also, moving this post to SQL Server 2005. It was in Notification Services, which might be why you didn't get an answer.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
October 9, 2007 at 7:39 am
I would try using brackets to quote database, table, and index names instead of quotes. The sp_defragment_indexes procedure you reference uses RTRIM(@objectowner) + '.' + RTRIM(@tablename), which will fail if the owner or table names need to be quoted. It should use QUOTENAME instead of RTRIM.
The biggest problem with using this procedure in SQL 2005 is that the table owner is not necessarily the same as the schema that the table belongs to. You can't use the sysobjects uid value like this anymore. You can replace "ObjectOwner = user_name(so.uid)" with "ObjectOwner = object_schema_name(id)"
October 9, 2007 at 8:09 am
Just posted a script you can use (just waiting for it to be published 🙂 ). Title = '2005 Online Index Rebuild using DMV'. This might help you.
As soon as its published I'll post the link to it.
Tommy
Follow @sqlscribeOctober 9, 2007 at 11:21 am
October 9, 2007 at 11:25 am
Can you post the name of the database? The reason is, I suspect that the issue has to do with the USE and the database name.
Try rather than "" use []
EXEC sp_MSforeachdb 'use [?] SELECT DB_NAME()'
October 9, 2007 at 11:49 am
Bob, it seems that the method I was using will not work for SQL 2005 anyway. Thanks. I appreciate all of your efforts so far.
I would like to build this into a job that runs through all user databases automatically. I am very new to this though. Mostly being a "script kiddie" with things that others have given/showed me.
October 9, 2007 at 2:14 pm
Use dynamic SQL? i.e.
declare @SQL nvarchar(MAX),@DBName varchar(50),@myCount int
declare @myTable table (DBName varchar(50))
insert into @myTable (DBName) values ('master')
select @myCount = count(*) from @myTable
while @myCount > 0
begin
select top 1 @DBName = DBName from @myTable
set @SQL =
'EXEC ' + @DBName + '.dbo.sp_who2'
exec sp_executesql @SQL
delete from @myTable where DBName = @DBName
select @myCount = count(*) from @myTable
end
Tommy
Follow @sqlscribeViewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply