public static bool CheckDependencies(ref string message) { // Create an instance of dependent classes in a new AppDomain try { string rulesEngineConfigFile = CommonFunc.GetAppSettingsStringValue("NotSupportedByAzureSQLDb"); if (rulesEngineConfigFile.Length > 0 && !File.Exists(rulesEngineConfigFile)) { message = CommonFunc.FormatString(Properties.Resources.ErrorFileNotFound, rulesEngineConfigFile); return(false); } 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 void NotifyJobIsFinished() { ++AsyncProcessingStatus.NumberOfCommandsExecuted; AsyncNotificationEventArgs eventArgs = new AsyncNotificationEventArgs(NotificationEventFunctionCode.ExecuteSqlOnAzure, 0, "", "", Color.DarkSlateBlue); eventArgs.PercentComplete = (int)(((float)AsyncProcessingStatus.NumberOfCommandsExecuted / (float)AsyncProcessingStatus.NumberOfCommands) * 100.0); eventArgs.StatusMsg = CommonFunc.FormatString(Properties.Resources.BCPProcessingStatus, AsyncProcessingStatus.NumberOfCommandsExecuted.ToString(), AsyncProcessingStatus.NumberOfCommands.ToString()); CurrentJobInfo.UpdateStatus(eventArgs); }
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); switch (objectType) { case DatabaseObjectsTypes.Triggers: _sdb.ScriptTriggers(objectList); break; case DatabaseObjectsTypes.Roles: _sdb.ScriptRoles(objectList); break; case DatabaseObjectsTypes.Schemas: _sdb.ScriptSchemas(objectList); break; case DatabaseObjectsTypes.XMLSchemaCollections: _sdb.ScriptXMLSchemaCollections(objectList); break; case DatabaseObjectsTypes.UserDefinedDataTypes: _sdb.ScriptUDT(objectList); break; case DatabaseObjectsTypes.UserDefinedTableTypes: _sdb.ScriptUDTT(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 BCPUploadData(TargetServerInfo targetServer, AsyncUpdateStatus updateStatus, AsyncQueueBCPJob queueBCPJob, string bcpArgs, ref StringCollection bcpUploadCommands) { AsyncNotificationEventArgs eventArgs = new AsyncNotificationEventArgs(0, 0, "", "", Color.DarkSlateBlue); NameValueCollection englishLanguage = (NameValueCollection)ConfigurationManager.GetSection("en-US"); NameValueCollection defaultLanguage = (NameValueCollection)ConfigurationManager.GetSection(Thread.CurrentThread.CurrentCulture.Name); if (defaultLanguage == null) { defaultLanguage = englishLanguage; if (defaultLanguage == null) { eventArgs.DisplayText = CommonFunc.FormatString(Properties.Resources.ErrorLanguageSectionNotFound, Application.ExecutablePath); eventArgs.DisplayColor = Color.Red; eventArgs.StatusMsg = Properties.Resources.ErrorApplicationConfiguration; updateStatus(eventArgs); return; } } try { int totalRowsCount = Convert.ToInt32(Regex.Match(bcpArgs, defaultLanguage["BCPNumber"]).Value, CultureInfo.InvariantCulture); // bcpArgs example: ":128425:[dbo].[Categories] in "C:\Users\ghuey\AppData\Local\Temp\tmp1CD8.tmp" -n -E // schema_table[0] == schema // schema_table[1] == table string[] schema_table = CommonFunc.GetSchemaAndTableFromBCPArgs(bcpArgs); string args = CommonFunc.BuildBCPUploadCommand(targetServer, bcpArgs); QueueBCPJobs(updateStatus, queueBCPJob, schema_table[0], schema_table[1], totalRowsCount, args, targetServer.ConnectionStringTargetDatabase); } catch (Exception ex) { eventArgs.DisplayText = ex.ToString(); eventArgs.DisplayColor = Color.Red; eventArgs.StatusMsg = Properties.Resources.ErrorParsingBCPArgs; updateStatus(eventArgs); } }
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 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); }
public void ExecuteSQLonTarget(TargetServerInfo targetServer, AsyncUpdateStatus updateStatus, AsyncQueueBCPJob queueBCPJob, string sqlToExecute) { DateTime dtStart = DateTime.Now; AsyncNotificationEventArgs eventArgs = new AsyncNotificationEventArgs(NotificationEventFunctionCode.ExecuteSqlOnAzure, 0, "", CommonFunc.FormatString(Properties.Resources.ProcessStarted, dtStart.ToString(), dtStart.ToUniversalTime().ToString()) + Environment.NewLine, Color.DarkSlateBlue); StringCollection strColl = new StringCollection(); StringCollection bcpUploadCommands = new StringCollection(); bool inBCPCommand = false; int idx = 0; AsyncProcessingStatus.FinishedProcessingJobs = true; updateStatus(eventArgs); string connectionStr = targetServer.ConnectionStringTargetDatabase; CommentAreaHelper cah = new CommentAreaHelper(); try { cah.FindCommentAreas(sqlToExecute); } catch (Exception ex) { Debug.WriteLine(ex.ToString()); } long lCharCnt = 0; int sqlCmdLoopCtr = 0; AsyncProcessingStatus.NumberOfCommands = 0; AsyncProcessingStatus.NumberOfCommandsExecuted = 0; if (cah.Lines.Count() > 0) { foreach (string line in cah.Lines) { if (line.Trim().Equals(Properties.Resources.Go) && !cah.IsIndexInComments(lCharCnt)) { AsyncProcessingStatus.NumberOfCommands++; } lCharCnt += line.Length + cah.CrLf; } } using (SqlConnection con = new SqlConnection(connectionStr)) { StringBuilder sql = new StringBuilder(5000); StringCollection sqlDepends = new StringCollection(); string currentObject = ""; bool comment = false; try { Retry.ExecuteRetryAction(() => { con.Open(); }, () => { con.Close(); }); } catch (Exception ex) { eventArgs.DisplayColor = Color.Red; eventArgs.PercentComplete = 100; if (ex is SqlException) { eventArgs.DisplayText = CommonFunc.FormatString(Properties.Resources.ProcessAborting, ((SqlException)ex).Number.ToString(), ex.Message, sql.ToString(), DateTime.Now.ToString(CultureInfo.CurrentCulture)) + Environment.NewLine; } else { eventArgs.DisplayText = CommonFunc.FormatString(Properties.Resources.ProcessAborting, "", ex.Message, sql.ToString(), DateTime.Now.ToString(CultureInfo.CurrentCulture)) + Environment.NewLine; } updateStatus(eventArgs); return; } lCharCnt = 0; foreach (string sqlCmd in cah.Lines) { ++sqlCmdLoopCtr; if (CancelProcessing) { con.Close(); eventArgs.StatusMsg = Properties.Resources.Canceled; eventArgs.DisplayColor = Color.DarkCyan; eventArgs.DisplayText = Environment.NewLine + CommonFunc.FormatString(Properties.Resources.ProcessCanceledAt, DateTime.Now.ToString(CultureInfo.CurrentCulture)) + Environment.NewLine; eventArgs.PercentComplete = 100; updateStatus(eventArgs); return; } if (inBCPCommand) { if (sqlCmd.Length == 0) { ++lCharCnt; continue; // Get rid of blank line when in BCP Command } if (sqlCmd.Trim().Equals(Properties.Resources.Go) && !cah.IsIndexInComments(lCharCnt)) { sql.Remove(0, sql.Length); inBCPCommand = false; lCharCnt += sqlCmd.Length + cah.CrLf; continue; } } if (sqlCmd.StartsWith(CommonFunc.FormatString(Properties.Resources.RemoveComment))) { lCharCnt += sqlCmd.Length + cah.CrLf; continue; // Get rid of program generated comments } if (!comment) { idx = sqlCmd.IndexOf(CommonFunc.FormatString(Properties.Resources.RemoveCommentStart)); if (idx > -1) { comment = true; lCharCnt += sqlCmd.Length + cah.CrLf; continue; } } else { idx = sqlCmd.IndexOf(CommonFunc.FormatString(Properties.Resources.RemoveCommentEnd)); if (idx > -1) { comment = false; lCharCnt += sqlCmd.Length + cah.CrLf; continue; } lCharCnt += sqlCmd.Length + cah.CrLf; continue; } // Look for BCP string. I.E. "-- BCPArgs:2345:.dbo.Categories" in "C:\Users\ghuey\AppData\Local\Temp\tmp1CD8.tmp" -n -E if (sqlCmd.StartsWith("-- BCPArgs:", StringComparison.Ordinal)) { BCPUploadData(targetServer, updateStatus, queueBCPJob, sqlCmd.Substring(11), ref bcpUploadCommands); // if queueBCPJob is null, then BCP upload is not queued up for a parallel batch process (basically, it is finished by now). if (queueBCPJob == null) { ++AsyncProcessingStatus.NumberOfCommandsExecuted; eventArgs.DisplayText = ""; eventArgs.PercentComplete = (int)(((float)AsyncProcessingStatus.NumberOfCommandsExecuted / (float)AsyncProcessingStatus.NumberOfCommands) * 100.0); eventArgs.StatusMsg = CommonFunc.FormatString(Properties.Resources.BCPProcessingStatus, AsyncProcessingStatus.NumberOfCommandsExecuted.ToString(), AsyncProcessingStatus.NumberOfCommands.ToString()); updateStatus(eventArgs); } inBCPCommand = true; lCharCnt += sqlCmd.Length + cah.CrLf; continue; } if (sqlCmd.Trim().Equals(Properties.Resources.Go) && !cah.IsIndexInComments(lCharCnt) || sqlCmdLoopCtr == cah.Lines.Count()) { if (sql.Length == 0) { lCharCnt += sqlCmd.Length + cah.CrLf; continue; } try { Retry.ExecuteRetryAction(() => { NonQueryResults nqr = SqlHelper.ExecuteNonQuery(con, CommandType.Text, sql.ToString()); }, () => { ResetConnection(con); }); Match cmdStr = Regex.Match(sql.ToString(), "(CREATE|ALTER)[\\w\\W]+(INDEX|TABLE|VIEW|PROCEDURE|ROLE|SCHEMA|TRIGGER|TYPE).*", RegexOptions.IgnoreCase); if (cmdStr.Success) { int cr = cmdStr.Value.IndexOf("\r"); if (cr > 0) { currentObject = cmdStr.Value.Substring(0, cr > 70 ? 70 : cr); } else { currentObject = cmdStr.Value.Substring(0, cmdStr.Value.Length > 70 ? 70 : cmdStr.Value.Length); } } else { currentObject = sql.ToString().Substring(0, sql.ToString().Length > 70 ? 70 : sql.ToString().Length); } currentObject = currentObject.Replace("\r", "").Replace("\n", " "); eventArgs.DisplayColor = Color.DarkGreen; eventArgs.DisplayText = Properties.Resources.Success + " " + currentObject + Environment.NewLine; } catch (Exception ex) { eventArgs.DisplayColor = Color.Red; if (ex is SqlException) { if (((SqlException)ex).Number == 208) { --AsyncProcessingStatus.NumberOfCommandsExecuted; sqlDepends.Add(sql.ToString()); eventArgs.DisplayText = ""; } else { eventArgs.DisplayText = DateTime.Now.ToString(CultureInfo.CurrentCulture) + CommonFunc.FormatString(Properties.Resources.ErrorNumAndMsg, ((SqlException)ex).Number.ToString(), ex.Message) + Environment.NewLine + sql.ToString() + Environment.NewLine; } } else { eventArgs.DisplayText = DateTime.Now.ToString(CultureInfo.CurrentCulture) + CommonFunc.FormatString(Properties.Resources.ErrorNumAndMsg, "", ex.Message) + Environment.NewLine + sql.ToString() + Environment.NewLine; } } ++AsyncProcessingStatus.NumberOfCommandsExecuted; eventArgs.PercentComplete = (int)(((float)AsyncProcessingStatus.NumberOfCommandsExecuted / (float)AsyncProcessingStatus.NumberOfCommands) * 100.0); eventArgs.StatusMsg = CommonFunc.FormatString(Properties.Resources.BCPProcessingStatus, AsyncProcessingStatus.NumberOfCommandsExecuted.ToString(), AsyncProcessingStatus.NumberOfCommands.ToString()); updateStatus(eventArgs); sql.Remove(0, sql.Length); } else { sql.AppendLine(sqlCmd); } lCharCnt += sqlCmd.Length + cah.CrLf; } // Ok, check for error that happened because of dependency and retry foreach (string sqlDep in sqlDepends) { try { Retry.ExecuteRetryAction(() => { NonQueryResults nqr = SqlHelper.ExecuteNonQuery(con, CommandType.Text, sqlDep); }, () => { ResetConnection(con); }); int startIdx = sqlDep.IndexOf("CREATE ", 0, StringComparison.CurrentCultureIgnoreCase); if (startIdx < 0) { startIdx = sqlDep.IndexOf("ALTER ", 0, StringComparison.CurrentCultureIgnoreCase); } int len = sqlDep.Substring(startIdx).Length > 70 ? 70 : sqlDep.Substring(startIdx).Length; currentObject = sqlDep.Substring(startIdx, len) + " ..."; eventArgs.DisplayColor = Color.DarkGreen; eventArgs.DisplayText = Properties.Resources.Success + " " + currentObject.Replace("\r", "").Replace("\n", " ") + Environment.NewLine; } catch (Exception ex) { eventArgs.DisplayColor = Color.Red; if (ex is SqlException) { eventArgs.DisplayText = DateTime.Now.ToString(CultureInfo.CurrentCulture) + CommonFunc.FormatString(Properties.Resources.ErrorNumAndMsg, ((SqlException)ex).Number.ToString(), ex.Message) + Environment.NewLine + sql.ToString() + Environment.NewLine; } else { eventArgs.DisplayText = DateTime.Now.ToString(CultureInfo.CurrentCulture) + CommonFunc.FormatString(Properties.Resources.ErrorNumAndMsg, "", ex.Message) + Environment.NewLine + sql.ToString() + Environment.NewLine; } } ++AsyncProcessingStatus.NumberOfCommandsExecuted; eventArgs.PercentComplete = (int)(((float)AsyncProcessingStatus.NumberOfCommandsExecuted / (float)AsyncProcessingStatus.NumberOfCommands) * 100.0); eventArgs.StatusMsg = CommonFunc.FormatString(Properties.Resources.BCPProcessingStatus, AsyncProcessingStatus.NumberOfCommandsExecuted.ToString(), AsyncProcessingStatus.NumberOfCommands.ToString()); updateStatus(eventArgs); } con.Close(); // Output BCP upload command summary if (bcpUploadCommands.Count > 0) { eventArgs.DisplayColor = Color.Green; eventArgs.DisplayText = Properties.Resources.BCPUploadSummary + Environment.NewLine; eventArgs.PercentComplete = 99; updateStatus(eventArgs); foreach (string bcpUploadCommand in bcpUploadCommands) { eventArgs.DisplayText = bcpUploadCommand + Environment.NewLine; updateStatus(eventArgs); } } AsyncProcessingStatus.FinishedAddingJobs = true; while (true) { if (AsyncProcessingStatus.FinishedProcessingJobs) { break; } Thread.Sleep(500); } // Done DateTime dtEnd = DateTime.Now; TimeSpan tsDuration = dtEnd.Subtract(dtStart); string sHour = tsDuration.Hours == 1 ? Properties.Resources.TimeHour : Properties.Resources.TimeHours; string sMin = tsDuration.Minutes == 1 ? Properties.Resources.TimeMinute : Properties.Resources.TimeMinutes; string sSecs = tsDuration.Seconds == 1 ? Properties.Resources.TimeSecond : Properties.Resources.TimeSeconds; eventArgs.StatusMsg = Properties.Resources.Done; eventArgs.DisplayColor = Color.DarkCyan; eventArgs.DisplayText = CommonFunc.FormatString(Properties.Resources.ProcessingFinished, dtEnd.ToString(), dtEnd.ToUniversalTime().ToString(), tsDuration.Hours + sHour + tsDuration.Minutes.ToString() + sMin + tsDuration.Seconds.ToString() + sSecs); eventArgs.PercentComplete = 100; updateStatus(eventArgs); } }
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 bool Process(SqlSmoObject[] objects, int start) { if (objects == null) { return(AsyncProcessingStatus.CancelProcessing); } SqlSmoObject[] sso = new SqlSmoObject[1]; StringCollection tableForeignKey = new StringCollection(); StringCollection bcpOutputCommands = new StringCollection(); StringCollection bcpTargetCommands = new StringCollection(); double step = ((100 - start) / (double)objects.Count()); int loopCnt = 0; 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 bcpOutputCommands, ref bcpTargetCommands, ref _outputDir); break; case "UserDefinedFunction": _sdb.ScriptUDF(sso); break; case "View": _sdb.ScriptViews(sso); break; case "StoredProcedure": _sdb.ScriptProcedures(sso); break; case "DdlTrigger": _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); } } _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 (tableForeignKey.Count > 0 && _smoScriptOpts.ScriptForeignKeys == true) { _sdb.ScriptStringCollection(ref tableForeignKey, 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 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); }
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 TargetServerInfo serverInfo = new TargetServerInfo(); _ErrorFlag = false; // Assume no error if (rbSpecifiedDB.Checked && tbDatabase.Text.Length == 0) { MessageBox.Show(Properties.Resources.MessageSpecifyDatabase); tbDatabase.Focus(); return; } serverInfo.ServerInstance = ServerNamesComboBox.Text; serverInfo.RootDatabase = rbSpecifiedDB.Checked ? tbDatabase.Text : "master"; serverInfo.LoginSecure = WindowsAuthenticationRadioButton.Checked; serverInfo.Save = rbSave.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(serverInfo); } else if (ServerConn != null) { ConnectServerConnection(serverInfo); } else { ServerConn = new ServerConnection(); ConnectServerConnection(serverInfo); } bool isNew = true; foreach (TargetServerInfo svrInfo in _servers) { if (svrInfo.ServerInstance.Equals(serverInfo.ServerInstance, StringComparison.OrdinalIgnoreCase) && svrInfo.ServerType == serverInfo.ServerType) { isNew = false; svrInfo.Version = serverInfo.Version; break; } } if (isNew) { _servers.Add(serverInfo); } List <TargetServerInfo> saveList = new List <TargetServerInfo>(); foreach (TargetServerInfo svrInfo in _servers) { if (svrInfo.Save) { saveList.Add(svrInfo); } } if (saveList.Count == 0) { if (File.Exists(HistoryFileName)) { File.Delete(HistoryFileName); } } else { CommonFunc.CompressObjectToFile(saveList, HistoryFileName); } _serverInfo.ServerInstance = serverInfo.ServerInstance; _serverInfo.ServerType = serverInfo.ServerType; _serverInfo.TargetDatabase = serverInfo.TargetDatabase; _serverInfo.Login = serverInfo.Login; _serverInfo.LoginSecure = serverInfo.LoginSecure; _serverInfo.Save = serverInfo.Save; _serverInfo.Password = serverInfo.Password; _serverInfo.RootDatabase = serverInfo.RootDatabase; _serverInfo.Version = serverInfo.Version; 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 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); }