private void AttachProfilerTraceDB()
{
splitProfilerAnalysis.Invoke(new System.Action(() => splitProfilerAnalysis.Visible = splitProfilerAnalysis.Enabled = btnAnalysisFolder.Enabled = false));
tcAnalysis.Invoke(new System.Action(() => ProfilerTraceStatusTextBox.Text += (ProfilerTraceStatusTextBox.Text.EndsWith("\r\n") || ProfilerTraceStatusTextBox.Text == "" ? "" : "\r\n") + "Attaching profiler trace database..."));
ValidateProfilerTraceDBConnectionStatus();
if (connSqlDb.State == ConnectionState.Open)
{
connSqlDb.ChangeDatabase("master");
SqlCommand cmd = new SqlCommand("IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'" + AnalysisTraceID + "') ALTER DATABASE [" + AnalysisTraceID + "] SET MULTI_USER", connSqlDb);
cmd.ExecuteNonQuery();
try { ExecuteProfilerAttachSQL(cmd); }
catch (SqlException ex)
{
LogException(ex);
Invoke(new System.Action(() => cmbProfilerAnalyses.Enabled = txtProfilerAnalysisQuery.Enabled = false));
if (ex.Message.Contains("cannot be opened because it is version"))
{
MessageBox.Show("Unable to attach to database since it was created with a later version of SQL than the selected server.", "Select another instance", MessageBoxButtons.OK, MessageBoxIcon.Error);
frmSimpleSQLServerPrompt sqlprompt = new frmSimpleSQLServerPrompt();
while (true)
{
if (sqlprompt.ShowDialog(this) == DialogResult.OK)
{
Properties.Settings.Default["SqlForProfilerTraceAnalysis"] = sqlprompt.cmbServer.Text;
Properties.Settings.Default.Save();
connSqlDb = new SqlConnection("Data Source=" + sqlprompt.cmbServer.Text + ";Integrated Security=true;Persist Security Info=false;");
connSqlDb.Open();
cmd.Connection = connSqlDb;
try
{
ExecuteProfilerAttachSQL(cmd);
break;
}
catch (Exception ex2)
{
LogException(ex2);
MessageBox.Show("Unable to attach to database since it was created with a later version of SQL than the selected server.", "Select another instance", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
else
{
Invoke(new System.Action(() => ProfilerTraceStatusTextBox.AppendText((ProfilerTraceStatusTextBox.Text.EndsWith("\r\n") ? "" : "\r\n") + "Unable to load trace data to SQL table. No local instance able to host the data is available.\r\nTry opening again on a machine with a local SQL instance running.")));
return;
}
}
}
else if (ex.Message.Contains("Unable to open the physical file") || ex.Message.Contains("The path specified by"))
{
ProfilerTraceStatusTextBox.Invoke(new System.Action(() => ProfilerTraceStatusTextBox.Text = (ProfilerTraceStatusTextBox.Text.EndsWith("\r\n") ? "" : "\r\n") + "Trace file is not yet imported to database table for analysis. Import to perform analysis.\r\n"));
return;
}
else
{
ProfilerTraceStatusTextBox.Invoke(new System.Action(() => ProfilerTraceStatusTextBox.AppendText((ProfilerTraceStatusTextBox.Text.EndsWith("\r\n") ? "" : "\r\n") + "Unable to attach to database due to exception:\r\n" + ex.Message)));
}
}
}
btnAnalysisFolder.Invoke(new System.Action(() => btnAnalysisFolder.Enabled = splitProfilerAnalysis.Enabled = true));
}
private bool ValidateProfilerTraceDBConnectionStatus()
{
if (connSqlDb.State != ConnectionState.Open)
{
string sqlForTraces = Registry.CurrentUser.CreateSubKey(@"Software\SSASDiag").GetValue("SqlForProfilerTraceAnalysis") as string;
string exMsg = "";
if (sqlForTraces != "")
{
connSqlDb = new SqlConnection("Data Source=" + sqlForTraces + ";Integrated Security=true;Connection Timeout=2;");
try
{
connSqlDb.Open();
}
catch (Exception ex)
{
if (!ex.Message.StartsWith("A network-related or instance-specific error occurred while establishing a connection to SQL Server."))
{
LogException(ex);
}
sqlForTraces = "";
exMsg = ex.Message;
}
}
if (sqlForTraces == "")
{
DialogResult r = DialogResult.Abort;
Invoke(new System.Action(() =>
{
frmSimpleSQLServerPrompt sqlprompt = new frmSimpleSQLServerPrompt();
sqlprompt.cmbServer.Text = sqlForTraces;
r = sqlprompt.ShowDialog(this);
if (r == DialogResult.OK)
{
Registry.CurrentUser.CreateSubKey(@"Software\SSASDiag").SetValue("SqlForProfilerTraceAnalysis", sqlprompt.cmbServer.Text);
sqlForTraces = sqlprompt.cmbServer.Text;
}
}));
if (r == DialogResult.OK)
{
connSqlDb = new SqlConnection("Data Source=" + Registry.CurrentUser.CreateSubKey(@"Software\SSASDiag").GetValue("SqlForProfilerTraceAnalysis") as string + ";Integrated Security=true;Persist Security Info=false;");
try { connSqlDb.Open(); }
catch (Exception ex) { LogException(ex); }
}
else
{
Invoke(new System.Action(() =>
{
ProfilerTraceStatusTextBox.AppendText((ProfilerTraceStatusTextBox.Text.EndsWith("\r\n") ? "" : "\r\n") + "Failure attaching to trace database: " + exMsg + "\r\n");
btnImportProfilerTrace.Visible = true;
}));
return(false);
}
}
}
return(true);
}
private bool ValidateProfilerTraceDBConnectionStatus()
{
if (connSqlDb.State != ConnectionState.Open)
{
string sqlForTraces = Properties.Settings.Default["SqlForProfilerTraceAnalysis"] as string;
string exMsg = "";
if (sqlForTraces != "")
{
connSqlDb = new SqlConnection("Data Source=" + sqlForTraces + ";Integrated Security=true;Connection Timeout=2;");
try
{
connSqlDb.Open();
}
catch (Exception ex)
{
LogException(ex);
sqlForTraces = "";
exMsg = ex.Message;
}
}
if (sqlForTraces == "")
{
DialogResult r = DialogResult.Abort;
Invoke(new System.Action(() =>
{
frmSimpleSQLServerPrompt sqlprompt = new frmSimpleSQLServerPrompt();
sqlprompt.cmbServer.Text = sqlForTraces;
r = sqlprompt.ShowDialog(this);
if (r == DialogResult.OK)
{
Properties.Settings.Default["SqlForProfilerTraceAnalysis"] = sqlForTraces = sqlprompt.cmbServer.Text;
Properties.Settings.Default.Save();
}
}));
if (r == DialogResult.OK)
{
connSqlDb = new SqlConnection("Data Source=" + Properties.Settings.Default["SqlForProfilerTraceAnalysis"] + ";Integrated Security=true;Persist Security Info=false;");
try { connSqlDb.Open(); }
catch (Exception ex) { LogException(ex); }
}
else
{
Invoke(new System.Action(() => ProfilerTraceStatusTextBox.AppendText((ProfilerTraceStatusTextBox.Text.EndsWith("\r\n") ? "" : "\r\n") + "Failure attaching to trace database: " + exMsg + "\r\n")));
return(false);
}
}
}
return(true);
}
private void AttachProfilerTraceDB()
{
CurrentProfilerTraceColumnList = new List <string>();
splitProfilerAnalysis.Invoke(new System.Action(() => splitProfilerAnalysis.Visible = splitProfilerAnalysis.Enabled = btnAnalysisFolder.Enabled = false));
tcAnalysis.Invoke(new System.Action(() => ProfilerTraceStatusTextBox.Text += (ProfilerTraceStatusTextBox.Text.EndsWith("\r\n") || ProfilerTraceStatusTextBox.Text == "" ? "" : "\r\n") + "Attaching profiler trace database..."));
ValidateProfilerTraceDBConnectionStatus();
if (connSqlDb.State == ConnectionState.Open)
{
connSqlDb.ChangeDatabase("master");
SqlCommand cmd = new SqlCommand("IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'" + AnalysisTraceID + "') ALTER DATABASE [" + AnalysisTraceID + "] SET MULTI_USER", connSqlDb);
cmd.ExecuteNonQuery();
try {
ExecuteProfilerAttachSQL(cmd);
cmd.CommandText = "select column_name from [" + AnalysisTraceID + "].INFORMATION_SCHEMA.COLUMNS where table_name = N'" + AnalysisTraceID + "'";
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
CurrentProfilerTraceColumnList.Add((dr["column_name"] as string).ToLower());
}
dr.Close();
}
catch (SqlException ex)
{
LogException(ex);
Invoke(new System.Action(() => cmbProfilerAnalyses.Enabled = txtProfilerAnalysisQuery.Enabled = false));
if (ex.Message.Contains("cannot be opened because it is version"))
{
MessageBox.Show("Unable to attach to database since it was created with a later version of SQL than the selected server.", "Select another instance", MessageBoxButtons.OK, MessageBoxIcon.Error);
frmSimpleSQLServerPrompt sqlprompt = new frmSimpleSQLServerPrompt();
while (true)
{
if (sqlprompt.ShowDialog(this) == DialogResult.OK)
{
Registry.CurrentUser.CreateSubKey(@"Software\SSASDiag").SetValue("SqlForProfilerTraceAnalysis", sqlprompt.cmbServer.Text);
connSqlDb = new SqlConnection("Data Source=" + sqlprompt.cmbServer.Text + ";Integrated Security=true;Persist Security Info=false;");
connSqlDb.Open();
cmd.Connection = connSqlDb;
try
{
ExecuteProfilerAttachSQL(cmd);
cmd.CommandText = "select column_name from [" + AnalysisTraceID + "].INFORMATION_SCHEMA.COLUMNS where table_name = N'" + AnalysisTraceID + "'";
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
CurrentProfilerTraceColumnList.Add((dr["column_name"] as string).ToLower());
}
dr.Close();
break;
}
catch (Exception ex2)
{
LogException(ex2);
MessageBox.Show("Unable to attach to database since it was created with a later version of SQL than the selected server.", "Select another instance", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
else
{
Invoke(new System.Action(() => ProfilerTraceStatusTextBox.AppendText((ProfilerTraceStatusTextBox.Text.EndsWith("\r\n") ? "" : "\r\n") + "Unable to load trace data to SQL table. No local instance able to host the data is available.\r\nTry opening again on a machine with a local SQL instance running.")));
return;
}
}
}
else if (ex.Message.Contains("Unable to open the physical file") || ex.Message.Contains("The path specified by"))
{
ProfilerTraceStatusTextBox.Invoke(new System.Action(() =>
{
ProfilerTraceStatusTextBox.Text = (ProfilerTraceStatusTextBox.Text.EndsWith("\r\n") && !String.IsNullOrWhiteSpace(ProfilerTraceStatusTextBox.Text) ? "" : "\r\n") + "Trace file is not yet imported to database table for analysis. Import to perform analysis.\r\n";
btnImportProfilerTrace.Visible = true;
}));
return;
}
else
{
ProfilerTraceStatusTextBox.Invoke(new System.Action(() => ProfilerTraceStatusTextBox.AppendText((ProfilerTraceStatusTextBox.Text.EndsWith("\r\n") ? "" : "\r\n") + "Unable to attach to database due to exception:\r\n" + ex.Message)));
ProfilerTraceStatusTextBox.Invoke(new System.Action(() =>
{
ProfilerTraceStatusTextBox.Text = (ProfilerTraceStatusTextBox.Text.EndsWith("\r\n") && !String.IsNullOrWhiteSpace(ProfilerTraceStatusTextBox.Text) ? "" : "\r\n") + "Trace file is not yet imported to database table for analysis. Import to perform analysis.\r\n";
btnImportProfilerTrace.Visible = true;
}));
}
}
}
btnAnalysisFolder.Invoke(new System.Action(() => btnAnalysisFolder.Enabled = splitProfilerAnalysis.Enabled = true));
}