前台捕获SQL存储过程执行进度问题？(100分求教)

Question

存储过程需要统计N年来的所有数据，耗时长久。我结合SqlConnection的InfoMessage事件捕获进度，却不能实时捕获！请问是什么原因？
存储过程部份代码如下：
-- 存储过程参数：@p_StartRQ：起始日期，@p_EndRQ：终止日期
DECLARE @intDIFF int, @intAddValue int, @dtNewStartRQ datetime
SET @intAddValue = 0    -- 递增变量
SET @intDIFF = DATEDIFF(day,@p_StartRQ,@p_EndRQ)+1    -- 两日期天数之差，即总循环次数
SET @dtNewStartRQ = @p_StartRQ
WHILE @dtNewStartRQ <= @p_EndRQ
BEGIN
    -- 执行N行统计代码......
    
    SET @intAddValue = @intAddValue + 1    -- 变量递增1
    -- 向前台传送当前进度消息
    PRINT CONVERT(VARCHAR(20),@intAddValue)+'/'+CONVERT(VARCHAR(20),@intDIFF)
    -- 日期递增1天，准备统计下一天
    SET @dtNewStartRQ = DATEADD(day,@intAddValue,@p_StartRQ)
ENDC#前台SqlConnection的InfoMessage事件代码如下：
private void btnRun_Click(object sender, EventArgs e)
{
    using (SqlConnection sqlConn = SqlConnInfo.CreateSqlConn())
    {
        sqlConn.InfoMessage += new SqlInfoMessageEventHandler(sqlConn_InfoMessage);
        using (SqlCommand sqlCmd = sqlConn.CreateCommand())
        {
            try
            {
                sqlCmd.CommandText = "sp_MySpName";
                sqlCmd.CommandType = CommandType.StoredProcedure;
                // 参数赋值,省略......
                sqlConn.Open();
                sqlCmd.ExecuteNonQuery();
              }
            finally
            {
                sqlCmd.Dispose();
                sqlConn.Close();
                sqlConn.Dispose();
            }
        }
    }
}
void sqlConn_InfoMessage(object sender, SqlInfoMessageEventArgs e)
{
    //foreach (SqlError err in e.Errors)
    //{
    //    MessageBox.Show(string.Format(
    //  "The “{0}” has received a severity “{1}”, state “{2}” error number “{3}”\n" +
    //  "on line “{4}” of procedure “{5}” on server “{6}”:\n“{7}”",
    //   err.Source, err.Class, err.State, err.Number, err.LineNumber,
    //   err.Procedure, err.Server, err.Message));
    //}
    MessageBox.Show(e.Message);
}
从MessageBox对话框看出InfoMessage事件只被触发了一次，存储过程所有PRINT内容被累加在一起返回？这样就不能实现实时显示存储过程执行进度情况了！不知是否我有什么地方没有设置？请大家指教。给分。

Answer 1

InfoMessage没用过，不确定PRINT前台是不是抓得住。是不是应该是raiserror？
DECLARE @intDIFF int, @intAddValue int, @dtNewStartRQ datetime 
SET @intAddValue = 0    -- 递增变量 
SET @intDIFF = DATEDIFF(day,@p_StartRQ,@p_EndRQ)+1    -- 两日期天数之差，即总循环次数 
SET @dtNewStartRQ = @p_StartRQ 
WHILE @dtNewStartRQ <= @p_EndRQ 
BEGIN 
    -- 执行N行统计代码...... 
    
    SET @intAddValue = @intAddValue + 1    -- 变量递增1 
    -- 向前台传送当前进度消息 
    raiserror( CONVERT(VARCHAR(20),@intAddValue)+'/'+CONVERT(VARCHAR(20),@intDIFF),16,1)
    -- 日期递增1天，准备统计下一天 
    SET @dtNewStartRQ = DATEADD(day,@intAddValue,@p_StartRQ) 
END 
raiserror('产品不存在',16,1)

Answer 2

好像是print的原因吧~~试下raiserror

Answer 3

应该是从存储过程里Raiserror。

Answer 4

http://www.cnblogs.com/hackzai/archive/2005/04/07/133635.html

Answer 5

if @@error>0 
begin
 return -1
end
在前台获取返回参数，然后rensponse.write

Answer 6

raiserror( CONVERT(VARCHAR(20),@intAddValue)+'/'+CONVERT(VARCHAR(20),@intDIFF),16,1)
一样不行，我是从raiserror改为print的。

Answer 7

如果同时有5个人在统计，服务器会受不了的吧，不过看你这个需求，估计也就月结的时候一个人在操作，问题也不大，我只是纯技术角度讨论这个问题
我记得李维的csdn演讲录音里在讨论项目的运算是放在前端还是后端时说过一句话：对于统计这种项目，运算应该放在客户端，因为运算不是数据库的强项，他做过一个实验，一个项目决定听取那些oracle的人的建议，统计运算都放在数据库端，几个人一起上线，数据库卡死在那里跑不动了

Answer 8

他说如果加一颗cpu，如果你的项目血本无归，你可以这么干

Answer 9

用sql server profiler，抓st_completed事件

Answer 10

ilikeff8,讲的有道理。
根据楼主的情况，建议楼主能够把返回的MSG类型变为表类型，可能会好些。
每执行一步COMMIT一下。
在外面访问该表。

Answer 11

之前也有考虑这种方法，但要不停的循环访问此表，资源消耗较大，这样不如做个动画提示用户等待。
有人了解SqlConnection的InfoMessage事件原理吗？是不是真的只会触发一次？

Answer 12

无解.N年前我研究过了.在存储过程的执行过程中，SQL Server是不会返回消息给客户端的,不过你是printf还是raiserror,都要等存储过程执行完毕才会返回.之前在微软ADO.NET的TeamBlog上看到说下一个ADO.NET版本准备支持这个特性，不知道是不是真的。

Answer 13

在VS 2005下测试通过,con_InfoMessage被多次调用.
跟你的区别在于设置了con.FireInfoMessageEventOnUserErrors = true;
如果不设置FireInfoMessageEventOnUserErrors,抛出的错误给SqlConnection捕获了.存储过程create procedure sp_InfoMsgTest
@times int
as
begin 
  declare @msg NVARCHAR(100)
  
  while(@times>0)
  begin
set @msg = 'Error' + cast(@times as VARCHAR(5))
raiserror(@msg,16,1)
set @times =@times - 1
  end
end
C# WinForm前台代码 private const string conString = "Server=SZSOFT-SZZB\\SQLEXPRESS;database=office;uid=sa;pwd=abc_!123456;";
        private void button1_Click(object sender, EventArgs e)
        {
            SqlConnection con = new SqlConnection();
            con.ConnectionString = conString;
            
            con.FireInfoMessageEventOnUserErrors = true;
            con.InfoMessage += new SqlInfoMessageEventHandler(con_InfoMessage);
            con.Open();            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "sp_InfoMsgTest";
            cmd.Connection = con;
            cmd.Parameters.Add(new SqlParameter("@times", 10));
            cmd.ExecuteNonQuery();
        }        void con_InfoMessage(object sender, SqlInfoMessageEventArgs e)
        {
            Console.WriteLine(e.Message);
        }

Answer 14

我个人认为，你只要设置一个wait动画gif就可以了，没有必要这样浪费 sql的资源。

Answer 15

非常感谢iStarSoft的详解，我已测试过了，确实可行。再次感谢！加100分。

Answer 16

无语.在存储过程执行过程中能返回进度吗?
con_InfoMessage被多次调用是在存储过程执行完之後才多次调用的
在执行过程中是绝对不会调用你的con_InfoMessage。
你试这个存储过程
create procedure sp_InfoMsgTest
@times int
as
begin 
  declare @msg NVARCHAR(100)
  
  while(@times>0)
  begin
    set @msg = 'Error' + cast(@times as VARCHAR(5))
    raiserror(@msg,16,1)
    set @times =@times - 1
    WAITFOR DELAY '00:00:10' --模拟一个长时间的执行过程
  end
end

Answer 17

Garnett_KG说得没错，确实是存储过程执行完之後才多次调用的。
我开始只是简单测试了一下，发现是被多次调用了InfoMessage事件。后来我就用延时函数进行详细测试，才发现在存储过程执行时并没有及时触发InfoMessage事件，只是在执行完之后才被多次调用。被微软忽悠了。