public static bool CheckDependencies(ref string message)
{
// Create an instance of dependent classes in a new AppDomain
try
{
AppDomain newAppDomain = AppDomain.CreateDomain("DependencyChecker");
foreach (Dependency depOn in Dependencies)
{
newAppDomain.CreateInstance(depOn.Assembly, depOn.Type);
}
}
catch (Exception ex)
{
Assembly assem = Assembly.GetEntryAssembly();
AssemblyName assemName = assem.GetName();
message = CommonFunc.FormatString(Properties.Resources.ErrorMissingDependencies, assemName.Name, ex.Message);
return(false);
}
return(true);
}
public static bool IsTransient(Exception ex)
{
if (ex != null)
{
if (_sqlErrorCodes == null)
{
string temp = CommonFunc.GetAppSettingsStringValue("BCPSQLAzureErrorCodesRetry");
int start = temp.IndexOf('(');
int end = temp.IndexOf(')');
_sqlErrorCodes = temp.Substring(start + 1, end - start - 1).Split('|');
if (_sqlErrorCodes == null || _sqlErrorCodes.Length < 1)
{
_sqlErrorCodes = "64|233|08001|08S01|10053|10054|10060|40001|40143|40174|40197|40501|40544|40549|40550|40551|40552|40553|40613|40615".Split('|');
}
}
SqlException sqlException;
if ((sqlException = ex as SqlException) != null)
{
// Enumerate through all errors found in the exception.
foreach (SqlError err in sqlException.Errors)
{
foreach (string errorCode in _sqlErrorCodes)
{
if (err.Number.ToString().Equals(errorCode))
{
return(true);
}
}
}
}
else if (ex is TimeoutException)
{
return(true);
}
}
return(false);
}
public static SMOScriptOptions CreateFromConfig()
{
var smoScriptOpts = new SMOScriptOptions();
LocalizedDefaultValueAttribute target = new LocalizedDefaultValueAttribute(CommonFunc.GetAppSettingsStringValue("TargetServerType"));
if (target != null && target.Value.ToString().Length > 0)
{
smoScriptOpts.TargetServer = (string)target.Value;
}
smoScriptOpts.ScriptDefaults = CommonFunc.GetAppSettingsBoolValue("ScriptDefaults");
smoScriptOpts.ScriptHeaders = CommonFunc.GetAppSettingsBoolValue("ScriptHeaders");
smoScriptOpts.IncludeIfNotExists = CommonFunc.GetAppSettingsBoolValue("IncludeIfNotExists");
LocalizedDefaultValueAttribute sdc = new LocalizedDefaultValueAttribute(ConfigurationManager.AppSettings["ScriptDropCreate"]);
smoScriptOpts.ScriptDropCreate = (string)sdc.Value;
smoScriptOpts.ScriptCheckConstraints = CommonFunc.GetAppSettingsBoolValue("ScriptCheckConstraints");
smoScriptOpts.ScriptCollation = CommonFunc.GetAppSettingsBoolValue("ScriptCollation");
smoScriptOpts.ScriptForeignKeys = CommonFunc.GetAppSettingsBoolValue("ScriptForeignKeys");
smoScriptOpts.ScriptPrimaryKeys = CommonFunc.GetAppSettingsBoolValue("ScriptPrimaryKeys");
smoScriptOpts.ScriptUniqueKeys = CommonFunc.GetAppSettingsBoolValue("ScriptUniqueKeys");
smoScriptOpts.ScriptIndexes = CommonFunc.GetAppSettingsBoolValue("ScriptIndexes");
LocalizedDefaultValueAttribute stad = new LocalizedDefaultValueAttribute(ConfigurationManager.AppSettings["ScriptTableAndOrData"]);
smoScriptOpts.ScriptTableAndOrData = (string)stad.Value;
if (smoScriptOpts.ScriptTableAndOrData.Length == 0)
{
if (CommonFunc.GetAppSettingsBoolValue("ScriptData"))
{
smoScriptOpts.ScriptTableAndOrData = Properties.Resources.ScriptOptionsTableSchemaData;
}
else
{
smoScriptOpts.ScriptTableAndOrData = Properties.Resources.ScriptOptionsTableSchema;
}
}
smoScriptOpts.ScriptTableTriggers = CommonFunc.GetAppSettingsBoolValue("ScriptTableTriggers");
smoScriptOpts.ActiveDirectorySP = CommonFunc.GetAppSettingsBoolValue("ActiveDirectorySP");
smoScriptOpts.BackupandRestoreTable = CommonFunc.GetAppSettingsBoolValue("BackupandRestoreTable");
smoScriptOpts.ChangeDataCapture = CommonFunc.GetAppSettingsBoolValue("ChangeDataCapture");
smoScriptOpts.ChangeDataCaptureTable = CommonFunc.GetAppSettingsBoolValue("ChangeDataCaptureTable");
LocalizedDefaultValueAttribute cc = new LocalizedDefaultValueAttribute(ConfigurationManager.AppSettings["CompatibilityChecks"]);
smoScriptOpts.CompatibilityChecks = (string)cc.Value;
smoScriptOpts.DatabaseEngineSP = CommonFunc.GetAppSettingsBoolValue("DatabaseEngineSP");
smoScriptOpts.DatabaseMailSP = CommonFunc.GetAppSettingsBoolValue("DatabaseMailSP");
smoScriptOpts.DatabaseMaintenancePlan = CommonFunc.GetAppSettingsBoolValue("DatabaseMaintenancePlan");
smoScriptOpts.DataControl = CommonFunc.GetAppSettingsBoolValue("DataControl");
smoScriptOpts.DistributedQueriesSP = CommonFunc.GetAppSettingsBoolValue("DistributedQueriesSP");
smoScriptOpts.FullTextSearchSP = CommonFunc.GetAppSettingsBoolValue("FullTextSearchSP");
smoScriptOpts.GeneralExtendedSPs = CommonFunc.GetAppSettingsBoolValue("GeneralExtendedSPs");
smoScriptOpts.GeneralTSQL = CommonFunc.GetAppSettingsBoolValue("GeneralTSQL");
smoScriptOpts.IntegrationServicesTable = CommonFunc.GetAppSettingsBoolValue("IntegrationServicesTable");
smoScriptOpts.LogShipping = CommonFunc.GetAppSettingsBoolValue("LogShipping");
smoScriptOpts.MetadataFunction = CommonFunc.GetAppSettingsBoolValue("MetadataFunction");
smoScriptOpts.OLEAutomationSP = CommonFunc.GetAppSettingsBoolValue("OLEAutomationSP");
smoScriptOpts.OLEDBTable = CommonFunc.GetAppSettingsBoolValue("OLEDBTable");
smoScriptOpts.ProfilerSP = CommonFunc.GetAppSettingsBoolValue("ProfilerSP");
smoScriptOpts.ReplicationSP = CommonFunc.GetAppSettingsBoolValue("ReplicationSP");
smoScriptOpts.ReplicationTable = CommonFunc.GetAppSettingsBoolValue("ReplicationTable");
smoScriptOpts.RowsetFunction = CommonFunc.GetAppSettingsBoolValue("RowsetFunction");
smoScriptOpts.SecurityFunction = CommonFunc.GetAppSettingsBoolValue("SecurityFunction");
smoScriptOpts.SecuritySP = CommonFunc.GetAppSettingsBoolValue("SecuritySP");
smoScriptOpts.SQLMailSP = CommonFunc.GetAppSettingsBoolValue("SQLMailSP");
smoScriptOpts.SQLServerAgentSP = CommonFunc.GetAppSettingsBoolValue("SQLServerAgentSP");
smoScriptOpts.SQLServerAgentTable = CommonFunc.GetAppSettingsBoolValue("SQLServerAgentTable");
smoScriptOpts.SystemCatalogView = CommonFunc.GetAppSettingsBoolValue("SystemCatalogView");
smoScriptOpts.SystemFunction = CommonFunc.GetAppSettingsBoolValue("SystemFunction");
smoScriptOpts.SystemStatisticalFunction = CommonFunc.GetAppSettingsBoolValue("SystemStatisticalFunction");
smoScriptOpts.Unclassified = CommonFunc.GetAppSettingsBoolValue("Unclassified");
return(smoScriptOpts);
}
public bool ParseFile(SMOScriptOptions options, bool _ParseFile, AsyncNotificationEventArgs e)
{
DateTime startTime = DateTime.Now;
ScriptDatabase sdb = new ScriptDatabase();
if (e.FunctionCode == NotificationEventFunctionCode.ParseFolder)
{
FileInfo fi = new FileInfo(_FileToProcess);
_SwTsql = new StreamWriter(_TargetFile, false);
sdb.Initialize(_Output.StatusUpdateHandler, options, false, _FileToProcess, _SwTsql);
}
else
{
sdb.Initialize(_Output.StatusUpdateHandler, options, false);
}
/****************************************************************/
string sqlText = CommonFunc.GetTextFromFile(_FileToProcess);
CommentAreaHelper cah = new CommentAreaHelper();
long totalCharacterOffset = 0;
bool bCommentedLine = false;
List <string> sqlCmds = new List <string>();
if (_ParseFile)
{
StringBuilder sb = new StringBuilder();
cah.FindCommentAreas(sqlText);
foreach (string line in cah.Lines)
{
if (line.Equals(Properties.Resources.Go, StringComparison.OrdinalIgnoreCase))
{
if (!cah.IsIndexInComments(totalCharacterOffset))
{
sqlCmds.Add(sb.ToString());
sb.Length = 0;
}
else
{
sb.Append(line + Environment.NewLine);
}
}
else
{
sb.Append(line + Environment.NewLine);
}
totalCharacterOffset += line.Length + cah.CrLf;
}
if (sb.Length > 0)
{
sqlCmds.Add(sb.ToString());
sb.Length = 0;
}
}
else
{
sqlCmds.Add(sqlText);
}
int numCmds = sqlCmds.Count();
int loopCtr = 0;
if (numCmds == 0)
{
e.DisplayText = CommonFunc.FormatString(Properties.Resources.MessageNoDataToProcess + Environment.NewLine, _FileToProcess);
if (e.FunctionCode == NotificationEventFunctionCode.ParseFile)
{
e.PercentComplete = 100;
}
else if (e.FunctionCode == NotificationEventFunctionCode.ParseFolder)
{
e.FilesProcessed++;
}
_Output.StatusUpdateHandler(e);
return(false);
}
e.DisplayColor = Color.DarkBlue;
e.DisplayText = "";
if (e.FunctionCode == NotificationEventFunctionCode.ParseFile)
{
e.StatusMsg = CommonFunc.FormatString(Properties.Resources.MessageProcessingStatus, loopCtr.ToString(), numCmds.ToString());
e.PercentComplete = 0;
}
else if (e.FunctionCode == NotificationEventFunctionCode.ParseFolder)
{
e.TotalDenominator = e.TotalDenominator + numCmds;
}
_Output.StatusUpdateHandler(e);
totalCharacterOffset = 0;
foreach (string cmd in sqlCmds)
{
++loopCtr;
if (AsyncProcessingStatus.CancelProcessing)
{
break;
}
if (cmd.Length == 0 || cmd.Equals(Environment.NewLine))
{
continue;
}
foreach (CommentArea ca in cah.CommentAreas)
{
if (ca.Start == totalCharacterOffset && ca.End == totalCharacterOffset + cmd.Length - cah.CrLf - 1) // note that the -1 is to put you at zero based counting
{
bCommentedLine = true;
break;
}
bCommentedLine = false;
}
totalCharacterOffset += cmd.Length + cah.CrLf;
if (_ParseFile && !bCommentedLine && !(cmd.StartsWith("/*~") || cmd.StartsWith("~*/")))
{
if (Regex.IsMatch(cmd, "(CREATE|ALTER)\\sPROCEDURE", RegexOptions.IgnoreCase))
{
sdb.ParseFileTSQLGo(cmd);
}
else if (Regex.IsMatch(cmd, "(CREATE|ALTER)\\sTABLE", RegexOptions.IgnoreCase))
{
sdb.ParseFileTable(cmd);
}
else if (Regex.IsMatch(cmd, "CREATE\\sXML\\sSCHEMA\\sCOLLECTION", RegexOptions.IgnoreCase))
{
sdb.ParseFileXMLSchemaCollections(cmd);
}
else if (Regex.IsMatch(cmd, "CREATE\\sTYPE", RegexOptions.IgnoreCase))
{
sdb.ParseFileUDT(cmd);
}
else if (Regex.IsMatch(cmd, "CREATE\\s[a-z\\s]*\\sINDEX", RegexOptions.IgnoreCase))
{
sdb.ParseFileIndex(cmd);
}
else if (Regex.IsMatch(cmd, "(CREATE|ALTER)\\sROLE", RegexOptions.IgnoreCase))
{
sdb.ParseFileRole(cmd);
}
else if (Regex.IsMatch(cmd, "(CREATE|ALTER)\\sSYNONYM", RegexOptions.IgnoreCase))
{
sdb.ParseFileSynonyms(cmd);
}
else if (Regex.IsMatch(cmd, "(CREATE|ALTER)\\sSCHEMA", RegexOptions.IgnoreCase))
{
sdb.ParseFileSchemas(cmd);
}
else if (Regex.IsMatch(cmd, "(CREATE|ALTER)\\sASSEMBLY", RegexOptions.IgnoreCase))
{
sdb.ParseFileAssemblies(cmd);
}
else if (Regex.IsMatch(cmd, "(CREATE|ALTER)\\sPARTITIONFUNCTIONS", RegexOptions.IgnoreCase))
{
sdb.ParseFilePartitionFunctions(cmd);
}
else if (Regex.IsMatch(cmd, "(CREATE|ALTER)\\sPARTITIONSCHEMES", RegexOptions.IgnoreCase))
{
sdb.ParseFilePartitionSchemes(cmd);
}
else
{
sdb.ParseFileTSQLGo(cmd);
}
if (e.FunctionCode == NotificationEventFunctionCode.ParseFolder)
{
e.NumeratorComplete = e.NumeratorComplete + 1;
}
}
else
{
sdb.OutputSQLString(cmd, Color.Black);
}
if (loopCtr % 20 == 0)
{
e.DisplayColor = Color.DarkBlue;
e.DisplayText = "";
if (e.FunctionCode == NotificationEventFunctionCode.ParseFile)
{
e.PercentComplete = (int)(((float)loopCtr / (float)numCmds) * 100.0);
e.StatusMsg = CommonFunc.FormatString(Properties.Resources.MessageProcessingStatus, loopCtr.ToString(), numCmds.ToString());
}
else if (e.FunctionCode == NotificationEventFunctionCode.ParseFolder)
{
e.PercentComplete = (int)(((float)e.NumeratorComplete / (float)e.TotalDenominator) * 100);
}
_Output.StatusUpdateHandler(e);
}
}
DateTime endTime = DateTime.Now;
if (e.FunctionCode == NotificationEventFunctionCode.ParseFile)
{
if (AsyncProcessingStatus.CancelProcessing)
{
e.DisplayText = CommonFunc.FormatString(Properties.Resources.MessageCanceledProcessing, DateTime.Now.ToString()) + Environment.NewLine;
e.StatusMsg = Properties.Resources.MessageCanceled;
}
else
{
e.DisplayColor = Color.DarkCyan;
if (_ParseFile)
{
e.DisplayText = Properties.Resources.AnalysisComplete + Environment.NewLine;
}
else
{
e.DisplayText = Properties.Resources.MessageFileReadAndReady;
}
e.StatusMsg = Properties.Resources.Done;
}
e.PercentComplete = 100;
e.DisplayText = CommonFunc.FormatString(Properties.Resources.MessageTotalFileProcessingTime + Environment.NewLine,
endTime.Subtract(startTime).ToString(),
Properties.Resources.RemoveComment,
_FileToProcess.ToString());
_Output.StatusUpdateHandler(e);
}
else if (e.FunctionCode == NotificationEventFunctionCode.ParseFolder)
{
_SwTsql.Flush();
_SwTsql.Close();
if (sdb.IssuesFound == true)
{
e.DisplayText = Properties.Resources.MessageFileChangedState +
CommonFunc.FormatString(Properties.Resources.MessageTotalFileProcessingTime,
endTime.Subtract(startTime).ToString(), _FileToProcess.ToString()) + Environment.NewLine;
e.DisplayColor = Color.Brown;
}
else
{
e.DisplayText = Properties.Resources.MessageFileNoChangeState +
CommonFunc.FormatString(Properties.Resources.MessageTotalFileProcessingTime,
endTime.Subtract(startTime).ToString(), _FileToProcess.ToString()) + Environment.NewLine;
e.DisplayColor = Color.DarkBlue;
}
_Output.StatusUpdateHandler(e);
e.FilesProcessed++;
}
return(sdb.IssuesFound);
}
private void ConnectCommandButton_Click(object sender, EventArgs e)
{
Cursor csr = null;
try
{
csr = this.Cursor; // Save the old cursor
this.Cursor = Cursors.WaitCursor; // Display the waiting cursor
_ErrorFlag = false; // Assume no error
if (rbSpecifiedDB.Checked && tbDatabase.Text.Length == 0)
{
MessageBox.Show(Properties.Resources.MessageSpecifyDatabase);
tbDatabase.Focus();
return;
}
if (ServerTypeComboBox.SelectedIndex == 0)
{
_serverInfo.ServerType = ServerTypes.SQLAzure;
}
else if (ServerTypeComboBox.SelectedIndex == 1)
{
_serverInfo.ServerType = ServerTypes.SQLAzureFed;
}
else
{
_serverInfo.ServerType = ServerTypes.SQLServer;
}
_serverInfo.ServerInstance = ServerNamesComboBox.Text;
_serverInfo.RootDatabase = rbSpecifiedDB.Checked ? tbDatabase.Text : "";
_serverInfo.LoginSecure = WindowsAuthenticationRadioButton.Checked;
if (WindowsAuthenticationRadioButton.Checked == false)
{
// Use SQL Server authentication
if (UserNameTextBox.Text.Length == 0)
{
MessageBox.Show(Properties.Resources.MessageUserName);
UserNameTextBox.Focus();
return;
}
_serverInfo.Password = PasswordTextBox.Text;
if (Regex.IsMatch(ServerNamesComboBox.Text, Properties.Resources.SQLAzureCloudName, RegexOptions.IgnoreCase))
{
string svrName = ServerNamesComboBox.Text.Substring(0, ServerNamesComboBox.Text.IndexOf('.'));
string svrExt = "@" + svrName;
string usrSvrName = "";
int idx = UserNameTextBox.Text.IndexOf('@');
if (idx > 0)
{
usrSvrName = UserNameTextBox.Text.Substring(idx);
}
if (!usrSvrName.Equals(svrExt))
{
if (idx < 1)
{
// Ok, the user forgot to put "@server" at the end of the user name. See if they want
// us to add it for them.
DialogResult dr = MessageBox.Show(CommonFunc.FormatString(Properties.Resources.AzureUserName, svrExt), Properties.Resources.AzureUserNameWarning, MessageBoxButtons.YesNo, MessageBoxIcon.Warning, MessageBoxDefaultButton.Button1);
if (dr == DialogResult.Yes)
{
UserNameTextBox.Text = UserNameTextBox.Text + svrExt;
}
}
else
{
// Ok, to get there, the user added @server to the end of the username, but the @server does not match @server in Server name.
// Check to see if the user wants us to fix for them.
DialogResult dr = MessageBox.Show(CommonFunc.FormatString(Properties.Resources.ServerNamesDoNotMatch, usrSvrName, svrExt), Properties.Resources.AzureUserNameWarning, MessageBoxButtons.YesNo, MessageBoxIcon.Warning, MessageBoxDefaultButton.Button1);
if (dr == DialogResult.Yes)
{
UserNameTextBox.Text = UserNameTextBox.Text.Substring(0, idx) + svrExt;
}
}
}
}
_serverInfo.Login = UserNameTextBox.Text;
}
// Go ahead and connect
if (SqlConn != null)
{
ConnectSqlConnection();
}
else if (ServerConn != null)
{
ConnectServerConnection();
}
else
{
if (_serverInfo.ServerType == ServerTypes.SQLAzureFed)
{
SqlConn = new SqlConnection();
ConnectSqlConnection();
}
else
{
ServerConn = new ServerConnection();
ConnectServerConnection();
}
}
this.DialogResult = DialogResult.OK;
}
catch (ConnectionFailureException ex)
{
string message = ex.InnerException != null ? ex.InnerException.Message : ex.Message;
MessageBox.Show(message, Properties.Resources.Error2, MessageBoxButtons.OK, MessageBoxIcon.Error);
_ErrorFlag = true;
}
catch (SmoException ex)
{
string message = ex.InnerException != null ? ex.InnerException.Message : ex.Message;
MessageBox.Show(message, Properties.Resources.Error2, MessageBoxButtons.OK, MessageBoxIcon.Error);
_ErrorFlag = true;
}
catch (Exception ex)
{
string message = ex.InnerException != null ? ex.InnerException.Message : ex.Message;
MessageBox.Show(message, Properties.Resources.Error2, MessageBoxButtons.OK, MessageBoxIcon.Error);
_ErrorFlag = true;
}
finally
{
this.Cursor = csr; // Restore the original cursor
}
}
public void ExecuteBCPCommand()
{
long rowsCopied = 0;
long totalUploaded = 0;
long chunkSize = Convert.ToInt32(ConfigurationManager.AppSettings["ChunkSize"], CultureInfo.InvariantCulture);
int sleep = Convert.ToInt32(ConfigurationManager.AppSettings["TimeBetweenChunks"], CultureInfo.InvariantCulture);
int cnt = 0;
lock (_stringBuilderLock)
{
_bcpOutput.Remove(0, _bcpOutput.Length);
}
AsyncBCPJobEventArgs args = new AsyncBCPJobEventArgs(null, CurrentJobInfo.JobStatus, null, "", Color.DarkSlateBlue);
CurrentJobInfo.JobStatus = CommandStatus.InProcess;
args.ClearRTB = true;
NameValueCollection englishLanguage = (NameValueCollection)ConfigurationManager.GetSection("en-US");
NameValueCollection defaultLanguage = (NameValueCollection)ConfigurationManager.GetSection(Thread.CurrentThread.CurrentCulture.Name);
if (defaultLanguage == null)
{
defaultLanguage = englishLanguage;
if (defaultLanguage == null)
{
args.DisplayText = CommonFunc.FormatString(Properties.Resources.ErrorLanguageSectionNotFound, Application.ExecutablePath);
args.DisplayColor = Color.Red;
args.Status = CurrentJobInfo.JobStatus = CommandStatus.Failed;
UpdateBCPJobStatus(args);
return;
}
}
string uploadTo = CurrentJobInfo.TableName;
if (CurrentJobInfo.FederationInfo != null)
{
Match db = Regex.Match(CurrentJobInfo.BCPUploadCommand, "system[\\-a-z0-9]*");
foreach (FederationMemberDistribution member in CurrentJobInfo.FederationInfo.Members)
{
if (member.DatabaseName.Equals(db.Value))
{
uploadTo = CommonFunc.FormatString(Properties.Resources.FederationMemberInfo, CurrentJobInfo.TableName, CurrentJobInfo.FederationInfo.FederationName, member.Member_ID, member.ToString());
break;
}
}
}
args.DisplayText = DateTime.Now.ToString(CultureInfo.CurrentCulture) + Environment.NewLine + CommonFunc.FormatString(Properties.Resources.BCPUploadingData, uploadTo, CurrentJobInfo.BCPUploadCommand) + Environment.NewLine;
args.DisplayColor = Color.DarkSlateBlue;
UpdateBCPJobStatus(args);
args.ClearRTB = false;
for (long i = 0; i < (CurrentJobInfo.NumberOfRows / chunkSize) + 1; i++)
{
string errorMsg = "";
bool retry = true;
int retryCnt = 0;
long start = i * chunkSize + 1;
long end = Math.Min((i + 1) * chunkSize, CurrentJobInfo.NumberOfRows);
// Don't sleep on first chunk.
if (i > 0)
{
Thread.Sleep(sleep);
}
int endOfBCPCmd = CurrentJobInfo.BCPUploadCommand.IndexOf(ConfigurationManager.AppSettings["BCPExe"] + " ");
if (endOfBCPCmd > -1)
{
endOfBCPCmd = ConfigurationManager.AppSettings["BCPExe"].Length;
}
else
{
System.Text.RegularExpressions.Match bcpExe = Regex.Match(CurrentJobInfo.BCPUploadCommand, @"\s[""[]");
if (bcpExe.Success)
{
endOfBCPCmd = bcpExe.Index;
}
else
{
endOfBCPCmd = CurrentJobInfo.BCPUploadCommand.IndexOf(" ");
}
}
while (retry && start <= end)
{
using (Process p = new Process())
{
p.StartInfo.FileName = CurrentJobInfo.BCPUploadCommand.Substring(0, endOfBCPCmd);
if (CurrentJobInfo.NumberOfRows < chunkSize)
{
p.StartInfo.Arguments = CurrentJobInfo.BCPUploadCommand.Substring(endOfBCPCmd + 1);
}
else
{
p.StartInfo.Arguments = CurrentJobInfo.BCPUploadCommand.Substring(endOfBCPCmd + 1) + @" -F" + start.ToString(CultureInfo.InvariantCulture) + @" -L" + end.ToString(CultureInfo.InvariantCulture);
}
p.StartInfo.CreateNoWindow = true;
p.StartInfo.UseShellExecute = false;
p.StartInfo.RedirectStandardOutput = true;
p.StartInfo.StandardOutputEncoding = Encoding.GetEncoding(defaultLanguage["BCPCodePage"]);
p.SynchronizingObject = this;
p.OutputDataReceived += new DataReceivedEventHandler(ProcessOutputHandler);
p.Start();
p.BeginOutputReadLine();
while (!p.HasExited)
{
if (AsyncProcessingStatus.CancelProcessing)
{
p.Kill();
p.Close();
CurrentJobInfo.JobStatus = CommandStatus.Failed;
return;
}
// This is the keep alive for SQL Azure connection. SQL Azure times out after 5 min if it sees no use.
if (cnt++ > 60)
{
args.DisplayText = "*" + Environment.NewLine;
UpdateBCPJobStatus(args);
cnt = 0;
}
else
{
args.DisplayColor = args.DisplayColor == Color.Red ? Color.DarkSlateBlue : Color.Red;
args.DisplayText = "*";
UpdateBCPJobStatus(args);
}
p.WaitForExit(1000);
}
p.Close();
}
args.DisplayColor = Color.DarkSlateBlue;
// Look for error.
Match error = Regex.Match(GetThreadSafeBCPOutputString(), defaultLanguage["BCPSQLState"]);
if (!error.Success)
{
error = Regex.Match(GetThreadSafeBCPOutputString(), englishLanguage["BCPSQLState"]);
}
if (error.Success)
{
// Check for Connection-Loss Errors. If it is not a connection loss error, then don't bother to retry
if (!Regex.IsMatch(GetThreadSafeBCPOutputString(), CommonFunc.GetAppSettingsStringValue("BCPSQLAzureErrorCodesRetry")))
{
retry = false;
args.DisplayColor = Color.Red;
args.DisplayText = Environment.NewLine + DateTime.Now.ToString(CultureInfo.CurrentCulture) + Properties.Resources.Error1 + Environment.NewLine + Environment.NewLine + GetThreadSafeBCPOutputString() + Environment.NewLine;
args.Status = CurrentJobInfo.JobStatus = CommandStatus.Failed;
UpdateBCPJobStatus(args);
continue;
}
if (retryCnt == 0)
{
// Save off origial error message
errorMsg = GetThreadSafeBCPOutputString();
}
// Ok, found error. Lets see if any data was sent
// Example Error
// 5000 rows sent to SQL Server. Total sent: 155000
// SQLState = S1000, NativeError = 21
// Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]Warning: Fatal error 40501 occurred at Oct 30 2009 4:15PM. Note the error and time, and contact your system administrator.
// BCP copy in failed
Match sent = Regex.Match(GetThreadSafeBCPOutputString(), defaultLanguage["BCPTotalSent"]);
if (sent.Success)
{
Match recordsSent = Regex.Match(sent.Value, defaultLanguage["BCPNumber"]);
if (recordsSent.Success)
{
int totalSent = Convert.ToInt32(recordsSent.Value, CultureInfo.InvariantCulture); // Ok, we had data sent. Get the number of rows sent.
start += totalSent; // Now, we need to ofset start to start after last successful batch
rowsCopied += totalSent; // Add totalSent this batch to rows copied so far.
}
}
else
{
sent = Regex.Match(GetThreadSafeBCPOutputString(), englishLanguage["BCPTotalSent"]);
if (sent.Success)
{
Match recordsSent = Regex.Match(sent.Value, englishLanguage["BCPNumber"]);
if (recordsSent.Success)
{
int totalSent = Convert.ToInt32(recordsSent.Value, CultureInfo.InvariantCulture); // Ok, we had data sent. Get the number of rows sent.
start += totalSent; // Now, we need to ofset start to start after last successful batch
rowsCopied += totalSent; // Add totalSent this batch to rows copied so far.
}
}
}
if (retryCnt++ > 2)
{
// Give up
retry = false;
args.DisplayColor = Color.Red;
args.Status = CurrentJobInfo.JobStatus = CommandStatus.Failed;
args.DisplayText = Environment.NewLine + DateTime.Now.ToString(CultureInfo.CurrentCulture) + Properties.Resources.BCPProcessFailed + Environment.NewLine + Environment.NewLine + errorMsg + Environment.NewLine;
UpdateBCPJobStatus(args);
}
else
{
Thread.Sleep(sleep);
}
}
else
{
string bcpSum = Environment.NewLine;
Match summary = Regex.Match(GetThreadSafeBCPOutputString(), defaultLanguage["BCPSummary"]);
if (summary.Success)
{
bcpSum = Environment.NewLine + summary.Value + Environment.NewLine;
}
else
{
summary = Regex.Match(GetThreadSafeBCPOutputString(), englishLanguage["BCPSummary"]);
if (summary.Success)
{
bcpSum = Environment.NewLine + summary.Value + Environment.NewLine;
}
}
retry = false;
totalUploaded = end;
rowsCopied += end - start + 1;
args.DisplayText = Environment.NewLine + DateTime.Now.ToString(CultureInfo.CurrentCulture) + " --> " +
string.Format(Properties.Resources.BCPProcessingUpdate + bcpSum,
rowsCopied, CurrentJobInfo.NumberOfRows, decimal.Round((decimal)((float)rowsCopied / (float)CurrentJobInfo.NumberOfRows * 100.0), 2));
UpdateBCPJobStatus(args);
}
lock (_stringBuilderLock)
{
_bcpOutput.Length = 0;
}
}
}
lock (_stringBuilderLock)
{
_bcpOutput.Length = 0;
}
args.DisplayText = Environment.NewLine;
if (CurrentJobInfo.JobStatus == CommandStatus.InProcess)
{
args.Status = CurrentJobInfo.JobStatus = CommandStatus.Success;
NotifyJobIsFinished();
}
UpdateBCPJobStatus(args);
}
public bool Process(SqlSmoObject[] objects, int start)
{
if (objects == null)
{
return(AsyncProcessingStatus.CancelProcessing);
}
SqlSmoObject[] sso = new SqlSmoObject[1];
StringCollection tableForeignKey = new StringCollection();
StringCollection extendedProperties = new StringCollection();
StringCollection bcpOutputCommands = new StringCollection();
StringCollection bcpTargetCommands = new StringCollection();
double step = ((100 - start) / (double)objects.Count());
int loopCnt = 0;
bool oldSQLdb = _smoScriptOpts.TargetServer.Equals(Properties.Resources.ServerType_AzureSQLDatabase);
List <string> listAssemblyUDF = new List <string>();
List <string> listEncryptedUDF = new List <string>();
List <string> listEncryptedView = new List <string>();
List <string> listAssemblySP = new List <string>();
List <string> listEncryptedSP = new List <string>();
List <string> listAssemblyDDLT = new List <string>();
List <string> listEncryptedDDLT = new List <string>();
foreach (SqlSmoObject obj in objects)
{
loopCnt++;
_eventArgs.StatusMsg = CommonFunc.FormatString(Properties.Resources.ScriptingObj, obj.ToString());
_eventArgs.PercentComplete = (int)(loopCnt * step);
_updateStatus(_eventArgs);
sso[0] = obj;
switch (obj.Urn.Type)
{
case "Table":
_sdb.ScriptTables(sso, ref tableForeignKey, ref extendedProperties, ref bcpOutputCommands, ref bcpTargetCommands, ref _outputDir);
break;
case "UserDefinedFunction":
UserDefinedFunction udf = (UserDefinedFunction)obj;
if (udf.IsEncrypted)
{
if (udf.AssemblyName.Length > 0)
{
if (oldSQLdb)
{
listAssemblyUDF.Add(udf.TextBody);
}
else
{
_sdb.ScriptUDF(sso);
}
}
else
{
listEncryptedUDF.Add(udf.ToString());
}
}
else
{
_sdb.ScriptUDF(sso);
}
break;
case "View":
View vw = (View)obj;
if (vw.IsEncrypted)
{
listEncryptedView.Add(vw.ToString());
}
else
{
_sdb.ScriptViews(sso);
}
break;
case "StoredProcedure":
StoredProcedure proc = (StoredProcedure)obj;
if (proc.IsEncrypted)
{
if (proc.AssemblyName.Length > 0)
{
if (oldSQLdb)
{
listAssemblySP.Add(proc.TextBody);
}
else
{
_sdb.ScriptProcedures(sso);
}
}
else
{
listEncryptedSP.Add(proc.ToString());
}
}
else
{
_sdb.ScriptProcedures(sso);
}
break;
case "DdlTrigger":
DatabaseDdlTrigger ddlt = (DatabaseDdlTrigger)obj;
if (ddlt.IsEncrypted)
{
if (ddlt.AssemblyName.Length > 0)
{
if (oldSQLdb)
{
listAssemblyDDLT.Add(ddlt.TextBody);
}
else
{
_sdb.ScriptTriggers(sso);
}
}
else
{
listEncryptedDDLT.Add(ddlt.ToString());
}
}
else
{
_sdb.ScriptTriggers(sso);
}
break;
}
if (AsyncProcessingStatus.CancelProcessing)
{
_eventArgs.StatusMsg = Properties.Resources.MessageCanceled;
_eventArgs.DisplayColor = Color.DarkCyan;
_eventArgs.DisplayText = CommonFunc.FormatString(Properties.Resources.MessageCanceledProcessing, DateTime.Now.ToString(CultureInfo.CurrentCulture)) + Environment.NewLine;
_eventArgs.PercentComplete = 100;
_updateStatus(_eventArgs);
return(AsyncProcessingStatus.CancelProcessing);
}
}
OutputNotSupported(Properties.Resources.ObjectTypeUDF, listAssemblyUDF);
OutputIsEncrypted(Properties.Resources.ObjectTypeUDF, listEncryptedUDF);
OutputIsEncrypted(Properties.Resources.ObjectTypeViews, listEncryptedView);
OutputNotSupported(Properties.Resources.ObjectTypeStoredProcedures, listAssemblySP);
OutputIsEncrypted(Properties.Resources.ObjectTypeStoredProcedures, listEncryptedSP);
OutputNotSupported(Properties.Resources.ObjectTypeTriggers, listAssemblyDDLT);
OutputIsEncrypted(Properties.Resources.ObjectTypeTriggers, listEncryptedDDLT);
_eventArgs.StatusMsg = CommonFunc.FormatString(Properties.Resources.ForiegnKeys);
_updateStatus(_eventArgs);
// Ok, due to dependency issues, we need to move the foreign key constraints to the end.
// But there is some small problem in that SQL Server allows for a DBA to have circular dependencies. So
// in order to solve this problem, we need to upload the data to the circular dependent tables first
// with no foreign keys, add foreign key constraints, and then upload the rest of the data. The
// reason that the data it not all uploaded before the foreign key constraints is to avoid performance
// issues with adding foreign key constraints after the fact.
if (_smoScriptOpts.ScriptForeignKeys == true)
{
_sdb.ScriptStringCollection(ref tableForeignKey, Color.Black);
}
if (_smoScriptOpts.ScriptExtendedProperties == true)
{
_sdb.ScriptStringCollection(ref extendedProperties, Color.Black);
}
if (bcpTargetCommands.Count > 0)
{
_sdb.ScriptStringCollection(ref bcpTargetCommands, Color.Green);
}
if (bcpOutputCommands.Count > 0)
{
_sdb.OutputAnalysisLine(Environment.NewLine + Properties.Resources.BCPOutputSummary, Color.Green);
foreach (string bcpOutputCommand in bcpOutputCommands)
{
_sdb.OutputAnalysisLine(bcpOutputCommand, Color.Green);
}
}
return(AsyncProcessingStatus.CancelProcessing);
}
public bool Process(DatabaseObjectsTypes objectType, SqlSmoObject[] objectList, int percent)
{
if (objectList != null && objectList.Count <SqlSmoObject>() > 0)
{
_eventArgs.StatusMsg = CommonFunc.FormatString(Properties.Resources.ScriptingObj, objectType);
_eventArgs.PercentComplete = percent;
_updateStatus(_eventArgs);
List <Trigger> listSupported = new List <Trigger>();
List <string> listNotSupported = new List <string>();
List <string> listEncrypted = new List <string>();
switch (objectType)
{
case DatabaseObjectsTypes.Assemblies:
if (_smoScriptOpts.TargetServer == Properties.Resources.ServerType_AzureSQLDatabase)
{
foreach (SqlAssembly asm in objectList)
{
listNotSupported.Add(" " + asm.ToString());
}
OutputNotSupported(Properties.Resources.ObjectTypeSQLAssemblies, listNotSupported);
_sdb.OutputSQLString(CommonFunc.FormatString(Properties.Resources.RemoveCommentStart), Color.DarkGreen);
_sdb.OutputSQLString(Properties.Resources.RemoveComment + " " + string.Format(Properties.Resources.NotSupported, Properties.Resources.ObjectTypeSQLAssemblies), Color.Red);
_sdb.ScriptAssemblies(objectList);
_sdb.OutputSQLString(CommonFunc.FormatString(Properties.Resources.RemoveCommentEnd), Color.DarkGreen);
}
else
{
_sdb.ScriptAssemblies(objectList);
}
break;
case DatabaseObjectsTypes.PartitionFunctions:
if (_smoScriptOpts.TargetServer == Properties.Resources.ServerType_AzureSQLDatabase)
{
foreach (PartitionFunction pf in objectList)
{
listNotSupported.Add(" " + pf.ToString());
}
OutputNotSupported(Properties.Resources.ObjectTypePartitionFunctions, listNotSupported);
_sdb.OutputSQLString(CommonFunc.FormatString(Properties.Resources.RemoveCommentStart), Color.DarkGreen);
_sdb.OutputSQLString(Properties.Resources.RemoveComment + " " + string.Format(Properties.Resources.NotSupported, Properties.Resources.ObjectTypePartitionFunctions), Color.Red);
_sdb.ScriptPartitionFunctions(objectList);
_sdb.OutputSQLString(CommonFunc.FormatString(Properties.Resources.RemoveCommentEnd), Color.DarkGreen);
}
else
{
_sdb.ScriptPartitionFunctions(objectList);
}
break;
case DatabaseObjectsTypes.PartitionSchemes:
if (_smoScriptOpts.TargetServer == Properties.Resources.ServerType_AzureSQLDatabase)
{
foreach (PartitionScheme ps in objectList)
{
listNotSupported.Add(" " + ps.ToString());
}
OutputNotSupported(Properties.Resources.ObjectTypePartitionSchemes, listNotSupported);
_sdb.OutputSQLString(CommonFunc.FormatString(Properties.Resources.RemoveCommentStart), Color.DarkGreen);
_sdb.OutputSQLString(Properties.Resources.RemoveComment + " " + string.Format(Properties.Resources.NotSupported, Properties.Resources.ObjectTypePartitionSchemes), Color.Red);
_sdb.ScriptPartitionSchemes(objectList);
_sdb.OutputSQLString(CommonFunc.FormatString(Properties.Resources.RemoveCommentEnd), Color.DarkGreen);
}
else
{
_sdb.ScriptPartitionSchemes(objectList);
}
break;
case DatabaseObjectsTypes.Roles:
_sdb.ScriptRoles(objectList);
break;
case DatabaseObjectsTypes.Schemas:
_sdb.ScriptSchemas(objectList);
break;
case DatabaseObjectsTypes.Synonyms:
_sdb.ScriptSynonyms(objectList);
break;
case DatabaseObjectsTypes.Triggers:
foreach (Trigger tig in objectList)
{
if (tig.IsEncrypted)
{
if (tig.AssemblyName.Length > 0)
{
if (_smoScriptOpts.TargetServer == Properties.Resources.ServerType_AzureSQLDatabase)
{
listNotSupported.Add(" " + tig.ToString());
break;
}
}
else
{
listEncrypted.Add(" " + tig.ToString());
break;
}
}
listSupported.Add(tig);
}
OutputNotSupported(Properties.Resources.ObjectTypeTriggers, listNotSupported);
OutputIsEncrypted(Properties.Resources.ObjectTypeTriggers, listEncrypted);
if (listSupported.Count > 0)
{
_sdb.ScriptTriggers(listSupported.ToArray());
}
break;
case DatabaseObjectsTypes.UserDefinedDataTypes:
_sdb.ScriptUDT(objectList);
break;
case DatabaseObjectsTypes.UserDefinedTableTypes:
_sdb.ScriptUDTT(objectList);
break;
case DatabaseObjectsTypes.XMLSchemaCollections:
_sdb.ScriptXMLSchemaCollections(objectList);
break;
}
}
if (AsyncProcessingStatus.CancelProcessing)
{
_eventArgs.DisplayColor = Color.DarkCyan;
_eventArgs.StatusMsg = Properties.Resources.MessageCanceled;
_eventArgs.DisplayText = CommonFunc.FormatString(Properties.Resources.MessageCanceledProcessing, DateTime.Now.ToString(CultureInfo.CurrentCulture)) + Environment.NewLine;
_eventArgs.PercentComplete = 100;
_updateStatus(_eventArgs);
}
return(AsyncProcessingStatus.CancelProcessing);
}
public void ParseFile(string _FileToProcess, bool _ParseFile)
{
DateTime startTime = DateTime.Now;
AsyncNotificationEventArgs e = new AsyncNotificationEventArgs(NotificationEventFunctionCode.ParseFile, 0, "", "", Color.Black);
ScriptDatabase sdb = new ScriptDatabase();
sdb.Initialize(_Output.StatusUpdateHandler, SMOScriptOptions.CreateFromConfig(), false);
/****************************************************************/
string sqlText = CommonFunc.GetTextFromFile(_FileToProcess);
CommentAreaHelper cah = new CommentAreaHelper();
long totalCharacterOffset = 0;
bool bCommentedLine = false;
List <string> sqlCmds = new List <string>();
if (_ParseFile)
{
StringBuilder sb = new StringBuilder();
cah.FindCommentAreas(sqlText);
foreach (string line in cah.Lines)
{
if (line.Equals(Properties.Resources.Go, StringComparison.OrdinalIgnoreCase))
{
if (!cah.IsIndexInComments(totalCharacterOffset))
{
sqlCmds.Add(sb.ToString());
sb.Length = 0;
}
else
{
sb.Append(line + Environment.NewLine);
}
}
else
{
sb.Append(line + Environment.NewLine);
}
totalCharacterOffset += line.Length + cah.CrLf;
}
}
else
{
sqlCmds.Add(sqlText);
}
int numCmds = sqlCmds.Count();
if (numCmds == 0)
{
e.PercentComplete = 100;
e.DisplayText = "No data to process";
_Output.StatusUpdateHandler(e);
return;
}
int loopCtr = 0;
e.DisplayColor = Color.DarkBlue;
e.DisplayText = "";
e.StatusMsg = "Processing " + loopCtr.ToString() + " out of " + numCmds.ToString();
e.PercentComplete = 0;
_Output.StatusUpdateHandler(e);
totalCharacterOffset = 0;
foreach (string cmd in sqlCmds)
{
++loopCtr;
if (AsyncProcessingStatus.CancelProcessing)
{
break;
}
if (cmd.Length == 0 || cmd.Equals(Environment.NewLine))
{
continue;
}
foreach (CommentArea ca in cah.CommentAreas)
{
if (ca.Start == totalCharacterOffset && ca.End == totalCharacterOffset + cmd.Length - cah.CrLf - 1) // note that the -1 is to put you at zero based counting
{
bCommentedLine = true;
break;
}
bCommentedLine = false;
}
totalCharacterOffset += cmd.Length + cah.CrLf;
if (_ParseFile && !bCommentedLine && !(cmd.StartsWith("/*~") || cmd.StartsWith("~*/")))
{
if (Regex.IsMatch(cmd, "(CREATE|ALTER)\\sPROCEDURE", RegexOptions.IgnoreCase))
{
sdb.ParseFileTSQLGo(cmd);
}
else if (Regex.IsMatch(cmd, "(CREATE|ALTER)\\sTABLE", RegexOptions.IgnoreCase))
{
sdb.ParseFileTable(cmd);
}
else if (Regex.IsMatch(cmd, "CREATE\\sXML\\sSCHEMA\\sCOLLECTION", RegexOptions.IgnoreCase))
{
sdb.ParseFileXMLSchemaCollections(cmd);
}
else if (Regex.IsMatch(cmd, "CREATE\\sTYPE", RegexOptions.IgnoreCase))
{
sdb.ParseFileUDT(cmd);
}
else if (Regex.IsMatch(cmd, "CREATE\\s[a-z\\s]*\\sINDEX", RegexOptions.IgnoreCase))
{
sdb.ParseFileIndex(cmd);
}
else if (Regex.IsMatch(cmd, "CREATE ROLE", RegexOptions.IgnoreCase))
{
sdb.ParseFileRole(cmd);
}
else
{
sdb.ParseFileTSQLGo(cmd);
}
}
else
{
sdb.OutputSQLString(cmd, Color.Black);
}
if (loopCtr % 20 == 0)
{
e.PercentComplete = (int)(((float)loopCtr / (float)numCmds) * 100.0);
e.DisplayColor = Color.DarkBlue;
e.DisplayText = "";
e.StatusMsg = "Processing " + loopCtr.ToString() + " out of " + numCmds.ToString();
_Output.StatusUpdateHandler(e);
}
}
if (AsyncProcessingStatus.CancelProcessing)
{
e.DisplayText = "Canceled processing ..." + Environment.NewLine;
e.StatusMsg = "Canceled!";
}
else
{
e.DisplayColor = Color.DarkCyan;
if (_ParseFile)
{
e.DisplayText = Properties.Resources.AnalysisComplete + Environment.NewLine;
}
else
{
e.DisplayText = "File read and ready for processing.";
}
e.StatusMsg = "Done!";
}
e.PercentComplete = 100;
_Output.StatusUpdateHandler(e);
DateTime endTime = DateTime.Now;
e.DisplayText = string.Format(
"{1}Total processing time --> {0}",
endTime.Subtract(startTime).ToString(),
Properties.Resources.RemoveComment);
_Output.StatusUpdateHandler(e);
}