Cannot rebuild indexes via EF ExecuteSqlCommand

Question

I have the following script to restore indexes:

DECLARE @TableName VARCHAR(255)
DECLARE @sql NVARCHAR(500)
DECLARE @fillfactor INT

SET @fillfactor = 80

DECLARE TableCursor CURSOR FOR
    SELECT OBJECT_SCHEMA_NAME([object_id])+'.['+name +']' AS TableName
    FROM sys.tables

OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @sql = 'ALTER INDEX ALL ON ' + @TableName + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'
    EXEC (@sql)

    FETCH NEXT FROM TableCursor INTO @TableName
END

CLOSE TableCursor
DEALLOCATE TableCursor

I have other scripts that work just like this one.

When I do it as follows:

var sql = ResourceUtilities.ReadResourceContent("rebuild_indexes.sql");
db.Database.ExecuteSqlCommand(sql);

I get the following error:

Incorrect syntax next to "TableCursor".

Implementation details ReadResourceContentdon't matter - I'm running another arbitrary SQL with this, and it works fine.

Why is this happening and what needs to be changed?

Answer 1

. ( , , THROW, SQL Server 2012 CTE), SQL Server 2005.

/ , SQL Server , , , , , . , , script SSMS Azure. Azure SQL, SSMS. , SSMS , -, .NET-, .

:

• VARCHAR NVARCHAR ( , Preatence Datatype NVARCHAR). (, , sysname , NVARCHAR(128)), NVARCHAR N.

• , Identity, a FILLFACTOR of 80 , 100. NEWID(), 90 , . NEWSEQUENTIALID() 100.

• , , STATIC, (). : LOCAL READ_ONLY FORWARD_ONLY.

:

DECLARE @TableName sysname, -- system alias for NVARCHAR(128)
        @SQL NVARCHAR(500),
        @FillFactor TINYINT; -- value cannot be < 0 or > 100 anyway

SET @FillFactor = 100; -- or 90 if using NEWID() for Clustered Index

DECLARE TableCursor CURSOR STATIC LOCAL READ_ONLY FORWARD_ONLY
FOR
    SELECT OBJECT_SCHEMA_NAME(st.[object_id]) + N'.' + QUOTENAME(st.[name]) AS [TableName]
    FROM   sys.tables st;

OPEN TableCursor;

FETCH NEXT
FROM  TableCursor
INTO  @TableName;

WHILE (@@FETCH_STATUS = 0)
BEGIN
    SET @SQL = N'ALTER INDEX ALL ON '
               + @TableName
               + N' REBUILD WITH (FILLFACTOR = '
               + CONVERT(NVARCHAR(3), @FillFactor)
               + N')';

    EXEC (@SQL);

    FETCH NEXT
    FROM  TableCursor
    INTO  @TableName;
END;

CLOSE TableCursor;
DEALLOCATE TableCursor;