CODEWITHSUNDEEP
c#sqlsql-serverstored-procedures
julian bechtold
julian bechtold

Reputation: 2251

executing stored procedure and receiving "Results to Text" in c#

Currently we have the following manual process which should be automated:

  1. A Database Administrator executes a Stored Procedure "StudyCloning" with "Result to Text" in SSMS.
  2. The Database Administrator Copies the Results to a text file and saves them for documentation Purposes

The result looks similar to this:

@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           EMEA\muege.******                                  0                                                                                                                                                                                                                                                                *****, Müge                                                            Müge                 *****                                              0           99          0           100001721   0            0                                                                                                                                                                                                                             muege.******@************.com                                                                                 0.0000                                  EMEA\m********                                     2017-06-06 14:36:44.380 EMEA\m********                                     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           EMEA\julian.********                               0                                                                                                                                                                                                                                                                ********, Julian                                                       Julian               ********                                           1           0           0           191742      0            0                                                                                                                                                                                                                             julian.********@************.com                                                                              0.0000                                  EMEA\muege.******                                  2018-05-08 10:54:44.957 EMEA\muege.********                                2018-05-08 10:54:44.957 0x0000000027DCD8FA NULL           191742      371CB873-9D52-E811-80CE-005056B01C75 julian.********

(1 row affected)

I am currently executing the Stored procedure with the following code:

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();

While this is working to read out the result tables, I also need the outputs of PRINT as well as the (rows affected). This is critical for the documentation of the Study transfer.

Also, the order is very important. While this example has a simple output only with two prints and tables the real stored procedure puts a lot of data in the results.

Do you have any way of achieving this?

Upvotes: 0

Views: 331

Answers (2)

Marc Gravell
Marc Gravell

Reputation: 1062770

Output from PRINT statements aren't part of the results as such - they happen at the connection level. To observe them, you need to handle the InfoMessage event on the SqlConnection.

Upvotes: 3

Ashok Rayal
Ashok Rayal

Reputation: 405

You can use SSRS Reports with default formatting which will do your work additionally you also have control over the printout formatting.

It will take just a couple of minutes to complete.

Thank you.

Upvotes: -1

Related Questions