URL= http://blogs.msdn.com/mattm/Default.aspx?p=4Capture PRINT messages from a stored procedure I recently helped with a customer issue where they had a long running stored procedure which output status messages periodically using PRINT statements. They wanted to capture these statements and output them into the SSIS log. Unfortunately, the Execute SQL Task doesn't support this (it's something we're considering for the future), but it's fairly easy to do through a script task. Our stored procedure: 1: CREATE PROCEDURE SPWithPrint 2: AS 3: BEGIN 4: print 'very important status information...' 5: END 6: GOOur script: 1: Public Sub Main() 2: Dim conn As New SqlConnection("server=(local);Integrated Security=SSPI;database=Test") 3: 4: AddHandler conn.InfoMessage, New SqlInfoMessageEventHandler(AddressOf OnInfoMessage) 5: 6: conn.Open() 7: 8: Dim cmd As New SqlCommand() 9: cmd.Connection = conn 10: cmd.CommandType = CommandType.StoredProcedure 11: cmd.CommandText = "[SPWithPrint]" 12: 13: cmd.ExecuteNonQuery() 14: 15: conn.Close() 16: 17: Dts.TaskResult = Dts.Results.Success 18: 19: End Sub 20: 21: Private Sub OnInfoMessage(ByVal sender As Object, ByVal args As System.Data.SqlClient.SqlInfoMessageEventArgs) 22: Dim sqlEvent As System.Data.SqlClient.SqlError 23: For Each sqlEvent In args.Errors 24: Dts.Events.FireInformation(sqlEvent.Number, sqlEvent.Procedure, sqlEvent.Message, "", 0, False) 25: Next 26: End SubThe print statements return the messages as InfoMessage events, which we catch with our handler and turn into SSIS information events.When we run the package, we can see the message from the stored procedure in our progress window... We're looking into adding this functionality to the Execute SQL Task as well, but hopefully this is an acceptable alternative until then.
/* 测试存储过程中的Print打出的错误信息 create proc testproc as print 'this is test textproc'; */private void button1_Click(object sender, EventArgs e) { cn.InfoMessage += new SqlInfoMessageEventHandler(info); cn.Open(); SqlCommand cmd = new SqlCommand("testproc", cn); cmd.CommandType = CommandType.StoredProcedure; cmd.ExecuteNonQuery(); cn.Close(); //执行完后就有 MessageBox.Show(r.Message);的结果 for (int i = 0; i < lis.Count; i++) { listBox1.Items.Add(lis[i].ToString()); } }
Connection对象的InfoMessage事件中,pError参数包含SQL中Print语句的输出内容,可以用pError.Description获得.
不过要用WithEvents定义连接对象才能获得InfoMessage事件.
List<string> lis = new List<string>();
void info(object o, SqlInfoMessageEventArgs ar)
{
foreach (SqlError r in ar.Errors)
{
//MessageBox.Show(r.Message);
lis.Add("从 SQL Server 中获取一个数值错误代码,它表示错误、警告或“未找到数据”消息。"+r.State.ToString());
lis.Add("获取生成错误的提供程序的名称。"+r.Source.ToString());
lis.Add("获取生成错误的 SQL Server 实例的名称。"+r.Server.ToString());
lis.Add("获取生成错误的存储过程或远程过程调用 (RPC) 的名称。"+r.Procedure.ToString());
lis.Add("获取一个标识错误类型的数字。"+r.Number.ToString());
lis.Add("获取对错误进行描述的文本。"+r.Message.ToString());
lis.Add("从包含错误的 Transact-SQL 批命令或存储过程中获取行号。"+r.LineNumber.ToString());
lis.Add("获取从 SQL Server 返回的错误的严重程度。"+r.Class.ToString());
}
}
/* 测试存储过程中的Print打出的错误信息
create proc testproc
as
print 'this is test textproc';
*/ private void button1_Click(object sender, EventArgs e)
{
cn.InfoMessage += new SqlInfoMessageEventHandler(info);
cn.Open();
SqlCommand cmd = new SqlCommand("testproc", cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.ExecuteNonQuery();
cn.Close();
//执行完后就有 MessageBox.Show(r.Message);的结果
for (int i = 0; i < lis.Count; i++)
{
listBox1.Items.Add(lis[i].ToString());
}
}
SqlConnection connection = ConnectionManager.DefaultSqlConnection; connection.FireInfoMessageEventOnUserErrors = true;
if (_request["Statistics"] == "true") connection.StatisticsEnabled = true;
connection.InfoMessage += new SqlInfoMessageEventHandler(Connection_InfoMessage);....
protected void Connection_InfoMessage(object sender, SqlInfoMessageEventArgs e)
{
messageCount++;
AppendMessage(e.Message);
}
I recently helped with a customer issue where they had a long running stored procedure which output status messages periodically using PRINT statements. They wanted to capture these statements and output them into the SSIS log. Unfortunately, the Execute SQL Task doesn't support this (it's something we're considering for the future), but it's fairly easy to do through a script task. Our stored procedure: 1: CREATE PROCEDURE SPWithPrint
2: AS
3: BEGIN
4: print 'very important status information...'
5: END
6: GOOur script: 1: Public Sub Main()
2: Dim conn As New SqlConnection("server=(local);Integrated Security=SSPI;database=Test")
3:
4: AddHandler conn.InfoMessage, New SqlInfoMessageEventHandler(AddressOf OnInfoMessage)
5:
6: conn.Open()
7:
8: Dim cmd As New SqlCommand()
9: cmd.Connection = conn
10: cmd.CommandType = CommandType.StoredProcedure
11: cmd.CommandText = "[SPWithPrint]"
12:
13: cmd.ExecuteNonQuery()
14:
15: conn.Close()
16:
17: Dts.TaskResult = Dts.Results.Success
18:
19: End Sub
20:
21: Private Sub OnInfoMessage(ByVal sender As Object, ByVal args As System.Data.SqlClient.SqlInfoMessageEventArgs)
22: Dim sqlEvent As System.Data.SqlClient.SqlError
23: For Each sqlEvent In args.Errors
24: Dts.Events.FireInformation(sqlEvent.Number, sqlEvent.Procedure, sqlEvent.Message, "", 0, False)
25: Next
26: End SubThe print statements return the messages as InfoMessage events, which we catch with our handler and turn into SSIS information events.When we run the package, we can see the message from the stored procedure in our progress window... We're looking into adding this functionality to the Execute SQL Task as well, but hopefully this is an acceptable alternative until then.
create proc testproc
as
print 'this is test textproc';
*/private void button1_Click(object sender, EventArgs e)
{
cn.InfoMessage += new SqlInfoMessageEventHandler(info);
cn.Open();
SqlCommand cmd = new SqlCommand("testproc", cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.ExecuteNonQuery();
cn.Close();
//执行完后就有 MessageBox.Show(r.Message);的结果
for (int i = 0; i < lis.Count; i++)
{
listBox1.Items.Add(lis[i].ToString());
}
}