无法通过EF ExecuteSqlCommand重建索引

I have the following script to rebuild 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 run in the same manner as this one.

我有其他脚本以与此相同的方式运行。

When I execute this in the following manner:

当我以下列方式执行此操作时：

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

I get the following error:

我收到以下错误：

Incorrect syntax near 'TableCursor'.

'TableCursor'附近的语法不正确。

The implementation details of ReadResourceContent are irrelevant - I am running other arbitrary SQL with this and it works fine.

ReadResourceContent的实现细节是无关紧要的 - 我正在运行其他任意SQL，它运行正常。

Why is this happening and what needs to change?

为什么会发生这种情况以及需要改变什么？

1 个解决方案

#1

You should at least try terminating all lines with a semicolon. While rarely required (only two instances I know of are statements prior to THROW statements, which were introduced in SQL Server 2012, and prior to CTEs), it was officially noted as a best practice as of the release of SQL Server 2005.

你应该至少尝试用分号终止所有行。虽然很少需要（我知道的只有两个实例是THROW语句之前的语句，这些语句是在SQL Server 2012和CTE之前引入的），但它在SQL Server 2005发布时被正式称为最佳实践。

One of the benefits of terminating statements / queries with semicolons is that SQL Server will have an easier time parsing the query batch when there are issues such as inconsistent line-endings, which is likely the root cause of the issue here. I am guessing that the root cause is inconsistent line-ending because you are able to run the script via SSMS against your Azure database. If the semicolons were required on Azure SQL Database, then it would have produced an error even when running via SSMS. Most likely SSMS made the line-endings consistent before submitting the batch, something that running via .NET code would not have done automatically for you.

使用分号终止语句/查询的好处之一是，当存在诸如不一致的行结尾之类的问题时，SQL Server将更容易解析查询批处理，这可能是此处问题的根本原因。我猜测根本原因是不一致的行结束，因为您可以通过SSMS对Azure数据库运行脚本。如果Azure SQL数据库上需要分号，那么即使通过SSMS运行也会产生错误。最有可能SSMS在提交批处理之前使得行结束一致，通过.NET代码运行的东西不会自动为您完成。

Other notes:

其他说明：

It would be best to not mix VARCHAR and NVARCHAR (even though Datatype Precedence will convert it all to NVARCHAR in the end) . Since you are dealing with identifiers (i.e. table names, which are sysname type in the Database, which is an alias for NVARCHAR(128) ), ideally all should be NVARCHAR and all string literals prefixed with N.

最好不要混合使用VARCHAR和NVARCHAR（即使数据类型优先级最终会将它全部转换为NVARCHAR）。由于您正在处理标识符（即表名，它是数据库中的sysname类型，它是NVARCHAR（128）的别名），理想情况下所有标识符应为NVARCHAR，并且所有字符串文字都以N为前缀。
In most cases, especially for tables with an Identity column, a FILLFACTOR of 80 is horrible, and you should be using 100. Whn using NEWID() then start with 90 and lower only if necessary. For NEWSEQUENTIALID() use 100.

在大多数情况下，特别是对于具有Identity列的表，FILLFACTOR为80是非常糟糕的，您应该使用100.当使用NEWID（）然后从90开始并且仅在必要时降低。对于NEWSEQUENTIALID（），请使用100。
When declaring cursors, if the query is referencing real tables instead of temporary tables, then use the STATIC keyword so as to not lock the base table(s). And usually not a bad idea to also use the following keywords: LOCAL READ_ONLY FORWARD_ONLY.

声明游标时，如果查询引用实际表而不是临时表，则使用STATIC关键字以便不锁定基表。并且通常使用以下关键字也是一个坏主意：LOCAL READ_ONLY FORWARD_ONLY。

End result should look as follows:

最终结果应如下所示：

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;

#1