//실행중인 쿼리 수집 (느린쿼리 얼랏 기능 추가해야함)
void InsertRunningQueries(DataTable dtRunningQueries)
{
int iServerNumber = g_SharedData.WSP_AGENT_SETTING.iServerNumber;
try
{
WSP.Console.WS_QUERY.AddRunningQueries wsInsert = new WSP.Console.WS_QUERY.AddRunningQueries();
wsInsert.Url = g_SharedData.WSP_AGENT_SETTING.strWS_URL + "/AddRunningQueries.asmx";
wsInsert.Timeout = 15000;
wsInsert.InsertRunningQueries(DataTableToBytes(dtRunningQueries), iServerNumber, g_dtTimeIn.ToString("yyyy-MM-dd HH:mm:ss"), g_dtTimeIn_UTC.ToString("yyyy-MM-dd HH:mm:ss"));
}
catch (Exception ex)
{
WSPEvent.WriteEvent("Checking running quries has failed. - " + ex.Message, "E", 1183);
}
}
void ExecuteQueryRecordCommand(string strQuery, string strDestTable, int iQueryID, string strSPName)
{
DataTable dtQueryResult = new DataTable();
DateTime dtTimeIn = DateTime.Now;
DateTime dtTimeIn_UTC = DateTime.UtcNow;
using (SqlConnection con1 = new SqlConnection(strLocalConnectionString))
{
try
{
WSP.Console.WS_QUERY.AddRunningQueries wsInsert = new WSP.Console.WS_QUERY.AddRunningQueries();
wsInsert.Url = g_SharedData.WSP_AGENT_SETTING.strWS_URL + "/AddRunningQueries.asmx";
wsInsert.Timeout = 15000;
int iServerNumber = g_SharedData.WSP_AGENT_SETTING.iServerNumber;
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = new SqlCommand("sp_executesql", con1);
da.SelectCommand.CommandType = CommandType.StoredProcedure;
da.SelectCommand.Parameters.AddWithValue("@statement", strQuery);
da.Fill(dtQueryResult);
dtSavedResult = dtQueryResult.Clone();
dtSavedResult = dtQueryResult.Copy();
//컬럼매핑때문에 주석 처리해봄
DataColumnCollection columns = dtQueryResult.Columns;
if (columns.Contains("ServerNum") && columns.Contains("RegDate") && columns.Contains("TimeIn"))
{
for (int i = 0; i < dtQueryResult.Rows.Count; i++)
{
dtQueryResult.Rows[i].BeginEdit();
dtQueryResult.Rows[i]["ServerNum"] = g_SharedData.WSP_AGENT_SETTING.iServerNumber;
if (iQueryID > 3) // Queries for bulk insert.
{
dtQueryResult.Rows[i]["RegDate"] = DBNull.Value;
}
dtQueryResult.Rows[i]["TimeIn"] = dtTimeIn.ToString("yyyy-MM-dd HH:mm:ss");
dtQueryResult.Rows[i].EndEdit();
dtQueryResult.AcceptChanges();
}
}
if (dtQueryResult.Rows.Count > 0)
{
if (iQueryID == 2 || iQueryID == 3)
{
dtSavedResult.Clear();
dtSavedResult = dtQueryResult.Clone();
if (IsSQLAgentStatusOK(dtQueryResult, iQueryID))
{
dtQueryResult.Clear();
return;
}
}
//if (dtSavedResult.Rows.Count > 0)
//{
// InsertQueryResult(dtSavedResult, strDestTable, strSPName);
// dtQueryResult.Clear();
//}
//if (iQueryID >= 4) // Custom Queries to insert BULK.
//{
// InsertQueryResult(dtQueryResult, strDestTable, strSPName);
// dtQueryResult.Clear();
//}
if (iQueryID == 2) // Custom Queries to insert BULK.
{
wsInsert.SQLJobAgentFailCheck(DataTableToBytes(dtSavedResult), iServerNumber, g_TimeIn.ToString("yyyy-MM-dd HH:mm:ss"), g_TimeIn_UTC.ToString("yyyy-MM-dd HH:mm:ss"));
dtQueryResult.Clear();
}
if (iQueryID == 3) // Custom Queries to insert BULK.
{
wsInsert.SQLJobStatusCheck(DataTableToBytes(dtSavedResult), iServerNumber, g_TimeIn.ToString("yyyy-MM-dd HH:mm:ss"), g_TimeIn_UTC.ToString("yyyy-MM-dd HH:mm:ss"));
dtQueryResult.Clear();
}
if (iQueryID == 4) // Custom Queries to insert BULK.
{
wsInsert.SQLLinkedCheck(DataTableToBytes(dtSavedResult), iServerNumber, g_TimeIn.ToString("yyyy-MM-dd HH:mm:ss"), g_TimeIn_UTC.ToString("yyyy-MM-dd HH:mm:ss"));
dtQueryResult.Clear();
}
if (iQueryID == 5) // Custom Queries to insert BULK.
{
wsInsert.SQLErrorlog(DataTableToBytes(dtSavedResult), iServerNumber, g_TimeIn.ToString("yyyy-MM-dd HH:mm:ss"), g_TimeIn_UTC.ToString("yyyy-MM-dd HH:mm:ss"));
dtQueryResult.Clear();
}
if (iQueryID == 6) // Custom Queries to insert BULK.
{
wsInsert.SQLTableSize(DataTableToBytes(dtSavedResult), iServerNumber, g_TimeIn.ToString("yyyy-MM-dd HH:mm:ss"), g_TimeIn_UTC.ToString("yyyy-MM-dd HH:mm:ss"));
dtQueryResult.Clear();
}
if (iQueryID == 7) // Custom Queries to insert BULK.
{
wsInsert.SQLBlock(DataTableToBytes(dtSavedResult), iServerNumber, g_TimeIn.ToString("yyyy-MM-dd HH:mm:ss"), g_TimeIn_UTC.ToString("yyyy-MM-dd HH:mm:ss"));
dtQueryResult.Clear();
}
if (iQueryID == 8) // Custom Queries to insert BULK.
{
wsInsert.SQLObjectCheck(DataTableToBytes(dtSavedResult), iServerNumber, g_TimeIn.ToString("yyyy-MM-dd HH:mm:ss"), g_TimeIn_UTC.ToString("yyyy-MM-dd HH:mm:ss"));
dtQueryResult.Clear();
}
if (iQueryID == 9) // Custom Queries to insert BULK.
{
wsInsert.SQLDatabase(DataTableToBytes(dtSavedResult), iServerNumber, g_TimeIn.ToString("yyyy-MM-dd HH:mm:ss"), g_TimeIn_UTC.ToString("yyyy-MM-dd HH:mm:ss"));
dtQueryResult.Clear();
}
if (iQueryID == 11) // Custom Queries to insert BULK.
{
wsInsert.SQLIndexDuplication(DataTableToBytes(dtSavedResult), iServerNumber, g_TimeIn.ToString("yyyy-MM-dd HH:mm:ss"), g_TimeIn_UTC.ToString("yyyy-MM-dd HH:mm:ss"));
dtQueryResult.Clear();
}
if (iQueryID == 12) // Custom Queries to insert BULK.
{
wsInsert.SQLServerInfo(DataTableToBytes(dtSavedResult), iServerNumber, g_TimeIn.ToString("yyyy-MM-dd HH:mm:ss"), g_TimeIn_UTC.ToString("yyyy-MM-dd HH:mm:ss"));
dtQueryResult.Clear();
}
if (iQueryID == 13) // Custom Queries to insert BULK.
{
wsInsert.SQLServiceStatus(DataTableToBytes(dtSavedResult), iServerNumber, g_TimeIn.ToString("yyyy-MM-dd HH:mm:ss"), g_TimeIn_UTC.ToString("yyyy-MM-dd HH:mm:ss"));
dtQueryResult.Clear();
}
if (iQueryID == 14) // Custom Queries to insert BULK.
{
wsInsert.SQLConfiguration(DataTableToBytes(dtSavedResult), iServerNumber, g_TimeIn.ToString("yyyy-MM-dd HH:mm:ss"));
dtQueryResult.Clear();
}
if (iQueryID == 15) // Custom Queries to insert BULK.
{
wsInsert.SQLDataBaseFileSize(DataTableToBytes(dtSavedResult), iServerNumber, g_TimeIn.ToString("yyyy-MM-dd HH:mm:ss"), g_TimeIn_UTC.ToString("yyyy-MM-dd HH:mm:ss"));
dtQueryResult.Clear();
}
if (iQueryID == 16) // Custom Queries to insert BULK.
{
wsInsert.SQLSession(DataTableToBytes(dtSavedResult), iServerNumber, g_TimeIn.ToString("yyyy-MM-dd HH:mm:ss"), g_TimeIn_UTC.ToString("yyyy-MM-dd HH:mm:ss"));
dtQueryResult.Clear();
}
if (iQueryID == 17) // Custom Queries to insert BULK.
{
wsInsert.SQLIndexFlagment(DataTableToBytes(dtSavedResult), iServerNumber, g_TimeIn.ToString("yyyy-MM-dd HH:mm:ss"), g_TimeIn_UTC.ToString("yyyy-MM-dd HH:mm:ss"));
dtQueryResult.Clear();
}
if (iQueryID == 18) // Custom Queries to insert BULK.
{
wsInsert.SQLAgentErrorlog(DataTableToBytes(dtSavedResult), iServerNumber, g_TimeIn.ToString("yyyy-MM-dd HH:mm:ss"), g_TimeIn_UTC.ToString("yyyy-MM-dd HH:mm:ss"));
dtQueryResult.Clear();
}
}
}
catch (Exception e)
{
WSPEvent.WriteEvent("Running SQL Monitoring queries has failed. in this local database. - " + iQueryID.ToString() + ":" + e.Message, "W", 1131);
}
finally
{
con1.Close();
}
}
}
