sp_MSforeachdb error

Question

sp_MSforeachdb error

tt-615680

SSCertifiable

Points: 7835
More actions
December 16, 2015 at 11:02 am

#323086

SET NOCOUNT ON;
Exec sp_MSforeachdb 'USE ?
DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @partitioncount bigint;
DECLARE @schemaname nvarchar(130);
DECLARE @objectname nvarchar(130);
DECLARE @indexname nvarchar(130);
DECLARE @partitionnum bigint;
DECLARE @partitions bigint;
DECLARE @frag float;
DECLARE @command nvarchar(4000);
DECLARE @dbid smallint;
SET @dbid = DB_ID();
SELECT
[object_id] AS objectid,
index_id AS indexid,
partition_number AS partitionnum,
avg_fragmentation_in_percent AS frag, page_count
INTO #work_to_do
FROM sys.dm_db_index_physical_stats (@dbid, NULL, NULL , NULL, N''LIMITED'')
WHERE avg_fragmentation_in_percent > 10.0
AND index_id > 0
AND page_count > 25
AND @dbid NOT IN (1,2,3,4)
DECLARE partitions CURSOR FOR SELECT objectid,indexid, partitionnum,frag FROM #work_to_do;
OPEN partitions;
WHILE (1=1)
BEGIN
FETCH NEXT
FROM partitions
INTO @objectid, @indexid, @partitionnum, @frag;
IF @@FETCH_STATUS < 0 BREAK;
SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)
FROM sys.objects AS o
JOIN sys.schemas as s ON s.schema_id = o.schema_id
WHERE o.object_id = @objectid;
SELECT @indexname = QUOTENAME(name)
FROM sys.indexes
WHERE object_id = @objectid AND index_id = @indexid;
SELECT @partitioncount = count (*)
FROM sys.partitions
WHERE object_id = @objectid AND index_id = @indexid;
IF @frag < 30.0
SET @command = N''ALTER INDEX '' + @indexname + N'' ON ''+ @schemaname + N''.'' + @objectname + N''REORGANIZE'';
IF @frag >= 30.0
SET @command = N''ALTER INDEX '' + @indexname + N'' ON ''+ @schemaname + N''.'' + @objectname + N'' REBUILD'';
IF @partitioncount > 1
SET @command = @command + N'' PARTITION=''+ CAST(@partitionnum AS nvarchar(10));
EXEC (@command);
PRINT N''Executed: '' + @command;
END
CLOSE partitions;
DEALLOCATE partitions;
'
Msg 1934, Level 16, State 1, Line 1
ALTER INDEX failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. Verify that SET options are correct for use with spatial index operations.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

x SSC-Insane Points: 23660 More actions · Answer 1

I'm making a guess about a conflict between "quotename()" function and quoted_name setting. maybe on a test database, flip your quoted name setting (I'm guessing "SET QUOTED_IDENTIFIER OFF") and try again?

Obviously I could be full of it, so test away!

Alan Burstein SSC Guru Points: 61141 More actions · Answer 2

tt-615680 (12/16/2015)
SET NOCOUNT ON;
Exec sp_MSforeachdb 'USE ?
DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @partitioncount bigint;
DECLARE @schemaname nvarchar(130);
DECLARE @objectname nvarchar(130);
DECLARE @indexname nvarchar(130);
DECLARE @partitionnum bigint;
DECLARE @partitions bigint;
DECLARE @frag float;
DECLARE @command nvarchar(4000);
DECLARE @dbid smallint;
SET @dbid = DB_ID();
SELECT
[object_id] AS objectid,
index_id AS indexid,
partition_number AS partitionnum,
avg_fragmentation_in_percent AS frag, page_count
INTO #work_to_do
FROM sys.dm_db_index_physical_stats (@dbid, NULL, NULL , NULL, N''LIMITED'')
WHERE avg_fragmentation_in_percent > 10.0
AND index_id > 0
AND page_count > 25
AND @dbid NOT IN (1,2,3,4)
DECLARE partitions CURSOR FOR SELECT objectid,indexid, partitionnum,frag FROM #work_to_do;
OPEN partitions;
WHILE (1=1)
BEGIN
FETCH NEXT
FROM partitions
INTO @objectid, @indexid, @partitionnum, @frag;
IF @@FETCH_STATUS < 0 BREAK;
SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)
FROM sys.objects AS o
JOIN sys.schemas as s ON s.schema_id = o.schema_id
WHERE o.object_id = @objectid;
SELECT @indexname = QUOTENAME(name)
FROM sys.indexes
WHERE object_id = @objectid AND index_id = @indexid;
SELECT @partitioncount = count (*)
FROM sys.partitions
WHERE object_id = @objectid AND index_id = @indexid;
IF @frag < 30.0
SET @command = N''ALTER INDEX '' + @indexname + N'' ON ''+ @schemaname + N''.'' + @objectname + N''REORGANIZE'';
IF @frag >= 30.0
SET @command = N''ALTER INDEX '' + @indexname + N'' ON ''+ @schemaname + N''.'' + @objectname + N'' REBUILD'';
IF @partitioncount > 1
SET @command = @command + N'' PARTITION=''+ CAST(@partitionnum AS nvarchar(10));
EXEC (@command);
PRINT N''Executed: '' + @command;
END
CLOSE partitions;
DEALLOCATE partitions;
'
Msg 1934, Level 16, State 1, Line 1
ALTER INDEX failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. Verify that SET options are correct for use with spatial index operations.

It's worth noting that sp_msforeachdb is undocumented which is why it shouldn't be used in production. You can accomplish what you are doing using plain ol' dynamic SQL.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001