通过CLR的SQLDatareader无法以正确的方式返回SQL消息 [英] SQLDatareader via CLR not returning SQL Message right way

问题描述

我通过CLR执行以下代码，是否有原因导致消息未打印到SQL Server，它是否需要等待，直到存储过程返回所有行（有大约70亿行要返回）

I execute the following code via CLR, is there a reason why the message is not printed to the SQL Server, does it need to wait until the Stored Procedure returns all the rows (there is about 7 Billion rows to return)

    SqlCommand cmd = new SqlCommand();
        cmd.Connection = conn;
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "spCommand_Select_Rows_For_Delete";
        cmd.CommandTimeout = 41600;

        SqlDataReader reader = cmd.ExecuteReader();
        try
        {
            string strSQL = "";
            SqlContext.Pipe.Send(DateTime.Now.ToString() + " - Started working with ProductTable");

            while (reader.Read())
            {
                strSQL = "DELETE FROM ProductTable WHERE ProductId = " + reader["ProductId"].ToString();

                SqlCommand cmdDelete = new SqlCommand(strSQL, conn);

                cmdDelete.Connection = conn;
                cmdDelete.CommandTimeout = 20800;
                cmdDelete.ExecuteNonQuery();
            }

            SqlContext.Pipe.Send(DateTime.Now.ToString() + " - Completed working with ProductTable");
        }
        finally
        {
            // Always call Close when done reading.
            reader.Close();
        }

我的存储过程：

SELECT ProductId FROM ProductTable
    WHERE ProductInfoId IN
    (
    SELECT ProductInfoId from DeletedProducts
    )

推荐答案

在这里，您将使用基于好的集合的操作删除70亿行。您不会通过CLR中的数据读取器来<删除>滥用。

Here's how you delete 7 billion rows using a nice set based operation. You don't abuse iterate through a datareader in CLR.

 SELECT 'Starting'
 WHILE ROWCOUNT <> 0
    DELETE TOP (1000000) P
    FROM  ProductTable P
    WHERE EXISTS (
        SELECT * from DeletedProducts DP WHERE P.ProductInfoId = DP.ProductInfoId
    )

有关更多信息，请参见以下问题在SQL Server 2008上批量删除

For more, see this question Bulk DELETE on SQL Server 2008

但是要回答您的问题，是的，SQL Server不会打印（您正在执行的操作）

But to answer your question, yes, SQL Server will not PRINT (which is what you're doing) immediately

这篇关于通过CLR的SQLDatareader无法以正确的方式返回SQL消息的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！