目前我们有以下手动过程，应该自动化：
- 数据库管理员执行存储过程"研究克隆" 在 SSMS 中使用"结果到文本"。
- 数据库管理员复制 结果到文本文件并保存用于记录目的
结果如下所示：
@GPMUserID: 100001721
SiteID UserID Active Status ProfileName Password UserName FirstName LastName LanguageID TitleID PayrollID EmployeeID DepartmentID DefaultFunctionID WorkPhone WorkFax WorkPager WorkCell WorkEMail Initials UDF1 CreatedBy CreatedByDate LastUpdatedBy LastUpdatedByDate RowVersion RowVersionSave CDS_User_ID GlobalUserGUID LocalUserName
----------- ----------- ------ ----------- -------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------- -------------------- -------------------------------------------------- ----------- ----------- ----------- ----------- ------------ ----------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- ---------------------------------------------------------------------------------------------------- -------- --------------------------------------- -------------------------------------------------- ----------------------- -------------------------------------------------- ----------------------- ------------------ -------------- ----------- ------------------------------------ --------------------------------------------------
1 100001721 1 1 EMEAmuege.****** 0 *****, Müge Müge ***** 0 99 0 100001721 0 0 muege.******@************.com 0.0000 EMEAm******** 2017-06-06 14:36:44.380 EMEAm******** 2017-06-06 14:36:44.380 0x0000000027DD32A2 NULL 100001721 DD9DEACB-B44A-E711-80C5-005056B01C75 muege.******
(1 row affected)
@GPMUserID: 191742
SiteID UserID Active Status ProfileName Password UserName FirstName LastName LanguageID TitleID PayrollID EmployeeID DepartmentID DefaultFunctionID WorkPhone WorkFax WorkPager WorkCell WorkEMail Initials UDF1 CreatedBy CreatedByDate LastUpdatedBy LastUpdatedByDate RowVersion RowVersionSave CDS_User_ID GlobalUserGUID LocalUserName
----------- ----------- ------ ----------- -------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------- -------------------- -------------------------------------------------- ----------- ----------- ----------- ----------- ------------ ----------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- ---------------------------------------------------------------------------------------------------- -------- --------------------------------------- -------------------------------------------------- ----------------------- -------------------------------------------------- ----------------------- ------------------ -------------- ----------- ------------------------------------ --------------------------------------------------
1 191742 1 1 EMEAjulian.******** 0 ********, Julian Julian ******** 1 0 0 191742 0 0 julian.********@************.com 0.0000 EMEAmuege.****** 2018-05-08 10:54:44.957 EMEAmuege.******** 2018-05-08 10:54:44.957 0x0000000027DCD8FA NULL 191742 371CB873-9D52-E811-80CE-005056B01C75 julian.********
(1 row affected)
我目前正在使用以下代码执行存储过程：
SqlCommand command = new SqlCommand(@"CDS.dbo.StudyCloningTest", con);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("@FromSiteID", SqlDbType.Int).Value = GetSiteID(fromSite);
command.Parameters.Add("@FromStudyID", SqlDbType.Int).Value = studyID;
command.Parameters.Add("@ToSiteID", SqlDbType.Int).Value = GetSiteID(destSite);
command.Parameters.Add("@ToCustomerID", SqlDbType.VarChar, 20).Value = customerID;
command.Parameters.Add("@ToJobID", SqlDbType.Int).Value = jobID;
command.Parameters.Add("@ToUserProfile", SqlDbType.VarChar, 50).Value = assignee;
command.Parameters.Add("@CopyStudyItems", SqlDbType.Bit).Value = copyItems;
var transaction = con.BeginTransaction("InsertLeadRecord");
command.Transaction = transaction;
List<string> gpmUsers = new List<string>();
using (SqlDataReader resultQuery = command.ExecuteReader())
{
while (resultQuery.NextResult())
{
while (resultQuery.Read())
{
/* Read Tables */
}
}
}
transaction.Commit();
虽然这是读出结果表的工作，但我还需要 PRINT 的输出以及(受影响的行(。这对于研究转移的记录至关重要。
此外，顺序非常重要。虽然此示例只有包含两个打印和表的简单输出，但实际存储过程在结果中放置了大量数据。
你有什么办法实现这一目标吗？
PRINT语句的输出本身不是结果的一部分 - 它们发生在连接级别。要观察它们，您需要处理SqlConnection上的InfoMessage事件。
您可以使用具有默认格式的 SSRS 报告，这将完成您的工作，此外您还可以控制打印输出格式。
只需几分钟即可完成。
谢谢。