public void Initialize(ScriptDatabase sdb, SMOScriptOptions options, AsyncUpdateStatus updateStatus, AsyncNotificationEventArgs eventArgs, string outputDir)
{
_sdb = sdb;
_smoScriptOpts = options;
_updateStatus = updateStatus;
_eventArgs = eventArgs;
_outputDir = outputDir;
}
private void CreateSplitpoint(object obj)
{
string tsql = (string)obj;
AsyncUpdateStatus updateStatus = new AsyncUpdateStatus(StatusUpdateHandler);
AsyncNotificationEventArgs eventArgs = new AsyncNotificationEventArgs(NotificationEventFunctionCode.ExecuteSqlOnAzure, 5, "", "", Color.Black);
updateStatus(eventArgs);
try
{
Retry.ExecuteRetryAction(() =>
{
using (SqlConnection connection = new SqlConnection(_serverInfo.ConnectionStringRootDatabase))
{
connection.Open();
SqlHelper.ExecuteNonQuery(connection, CommandType.Text, "USE FEDERATION ROOT WITH RESET");
SqlHelper.ExecuteNonQuery(connection, CommandType.Text, tsql.ToString());
if (_federationDetails.Federation_id == 0)
{
ScalarResults sr = SqlHelper.ExecuteScalar(connection, CommandType.Text, "SELECT federation_id FROM sys.federations fed where fed.name = '" + _federationDetails.FederationName + "'");
_federationDetails.Federation_id = Convert.ToInt32(sr.ExecuteScalarReturnValue);
}
while (true)
{
ScalarResults sr = SqlHelper.ExecuteScalar(connection, CommandType.Text, "SELECT percent_complete FROM sys.dm_federation_operations WHERE federation_id = " + _federationDetails.Federation_id);
if (sr.ExecuteScalarReturnValue == null || _cancelProcessing)
{
break;
}
eventArgs.PercentComplete = Convert.ToInt32(sr.ExecuteScalarReturnValue);
if (eventArgs.PercentComplete == 100)
{
break;
}
updateStatus(eventArgs);
Thread.Sleep(500);
}
connection.Close();
eventArgs.PercentComplete = 100;
updateStatus(eventArgs);
}
});
}
catch (Exception ex)
{
eventArgs.PercentComplete = -1;
updateStatus(eventArgs);
ErrorHelper.ShowException(null, ex);
}
}
public void StatusUpdateHandler(AsyncNotificationEventArgs args)
{
if (this.InvokeRequired)
{
AsyncUpdateStatus updateStatus = new AsyncUpdateStatus(StatusUpdateHandler);
this.Invoke(updateStatus, new object[] { args });
}
else
{
if (_cancelProcessing)
{
return;
}
if (args.PercentComplete < 0)
{
progressBar1.Value = 0;
btnCreate.Text = Properties.Resources.Retry;
btnCreate.Enabled = true;
}
else if (args.PercentComplete == 100)
{
progressBar1.Value = 100;
// btnCreate.Text = Properties.Resources.Done;
btnCreate.Enabled = true;
btnCancel.Enabled = false;
if (tabControlCreate.SelectedIndex == 1)
{
MessageBox.Show(Properties.Resources.FederationMemberCreated);
}
else
{
MessageBox.Show(CommonFunc.FormatString(Properties.Resources.FederationCreated, tbFederationName.Text));
}
this.DialogResult = System.Windows.Forms.DialogResult.OK;
this.Close();
}
else
{
progressBar1.Value = args.PercentComplete > 5 ? args.PercentComplete : 5;
}
}
}
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 void QueueBCPJobs(AsyncUpdateStatus updateStatus, AsyncQueueBCPJob queueBCPJob, string schema, string table, long totalRowsCount, string bcpArgs, string connectionString)
{
AsyncProcessingStatus.FinishedProcessingJobs = false;
BCPJobInfo jobInfo = new BCPJobInfo();
jobInfo.Schema = schema;
jobInfo.TableName = table;
jobInfo.NumberOfRows = totalRowsCount;
jobInfo.BCPUploadCommand = ConfigurationManager.AppSettings["BCPExe"] + " " + bcpArgs;
jobInfo.ConnectionString = connectionString;
jobInfo.JobStatus = CommandStatus.Waiting;
jobInfo.UpdateStatus = updateStatus;
AsyncQueueBCPJobArgs jobArgs = new AsyncQueueBCPJobArgs();
jobArgs.JobInfo = jobInfo;
queueBCPJob(jobArgs);
return;
}
public static void Process()
{
TargetProcessor tp = new TargetProcessor();
AsyncUpdateStatus updateStatus = new AsyncUpdateStatus(TargetAsyncUpdateStatusHandler);
string sqlToExecute = CommonFunc.GetTextFromFile(_FileToProcess);
_OutputResultsDirectory = _OutputResultsFile.Substring(0, _OutputResultsFile.LastIndexOf('\\') + 1);
TargetServerInfo tsi = new TargetServerInfo();
tsi.ServerInstance = _TargetServerName;
tsi.TargetDatabase = _TargetDatabase;
if (Regex.IsMatch(_TargetServerName, CommonFunc.GetAppSettingsStringValue("RegexSearchForAzureServer"), RegexOptions.IgnoreCase))
{
tsi.ServerType = ServerTypes.SQLAzure;
}
else
{
tsi.ServerType = ServerTypes.SQLServer;
}
if (_bTargetConnectNTAuth == true)
{
// Use Windows authentication
tsi.LoginSecure = true;
}
else
{
// Use SQL Server authentication
tsi.LoginSecure = false;
tsi.Login = _TargetUserName;
tsi.Password = _TargetPassword;
}
//AsyncProcessingStatus.FinishedProcessingJobs = true;
AsyncQueueBCPJob queueBCPJob = new AsyncQueueBCPJob(AsyncQueueJobHandler);
tp.CreateDatabase(tsi, _Collation, _TargetEdition, _TargetDatabaseSize, _bDropExistingDatabase);
tp.ExecuteSQLonTarget(tsi, updateStatus, queueBCPJob, sqlToExecute);
}
public List <string> GenerateScriptFromSourceServer(ServerConnection sc, List <DatabaseObject> objectList)
{
var dtStart = DateTime.Now;
AsyncUpdateStatus updateStatus = GenScriptAsyncUpdateStatusHandler;
var args = new AsyncNotificationEventArgs(NotificationEventFunctionCode.GenerateScriptFromSQLServer, 0, "", CommonFunc.FormatString("Process started at {0} -- UTC -> {1} ... ", dtStart.ToString(CultureInfo.CurrentUICulture), dtStart.ToUniversalTime().ToString(CultureInfo.CurrentUICulture)) + Environment.NewLine, Color.DarkBlue);
StreamWriter swTSQL = null;
SqlSmoObject[] smoTriggers = null;
Object sender = System.Threading.Thread.CurrentThread;
var smoScriptOpts = SMOScriptOptions.CreateFromConfig();
updateStatus(args);
sc.Connect();
var ss = new Server(sc);
var db = ss.Databases[sc.DatabaseName];
var sdb = new ScriptDatabase();
sdb.Initialize(ss, db, updateStatus, smoScriptOpts, swTSQL);
args.DisplayText = "";
args.StatusMsg = "Sorting objects by dependency ...";
args.PercentComplete = 1;
updateStatus(args);
var sorted = GetSortedObjects(db, objectList);
var sp = new SourceProcessor();
var tempPathForBCPData = Path.Combine(Path.GetTempPath(), "/BCPData");
Directory.CreateDirectory(tempPathForBCPData);
sp.Initialize(sdb, smoScriptOpts, updateStatus, args, tempPathForBCPData);
//NOTE: This is what does the magic!
if (sp.Process(sorted, 30))
{
return(output);
}
if (!Regex.IsMatch(smoScriptOpts.ScriptTableAndOrData, smoScriptOpts.GetLocalizedStringValue("ScriptOptionsTableData"), RegexOptions.IgnoreCase))
{
if (sp.Process(DatabaseObjectsTypes.Triggers, smoTriggers, 95))
{
return(output);
}
}
var dtEnd = DateTime.Now;
var tsDuration = dtEnd.Subtract(dtStart);
var sHour = tsDuration.Minutes == 1 ? " hour, " : " hours, ";
var sMin = tsDuration.Minutes == 1 ? " minute and " : " minutes and ";
var sSecs = tsDuration.Seconds == 1 ? " second" : " seconds";
args.StatusMsg = "Done!";
args.DisplayColor = Color.DarkCyan;
if (smoScriptOpts.CheckCompatibility() == 1)
{
args.DisplayText = CommonFunc.FormatString(@"No analysis done on script.
Processing finished at {0} -- UTC -> {1}
Total processing time: {2}", dtEnd.ToString(CultureInfo.CurrentUICulture), dtEnd.ToUniversalTime().ToString(CultureInfo.CurrentUICulture), tsDuration.Hours + sHour + tsDuration.Minutes.ToString(CultureInfo.CurrentUICulture) + sMin + tsDuration.Seconds.ToString(CultureInfo.CurrentUICulture) + sSecs);
}
else
{
args.DisplayText = CommonFunc.FormatString(@"Analysis completed at {0} -- UTC -> {1}
Any issues discovered will be reported above.
Total processing time: {2}", dtEnd.ToString(CultureInfo.CurrentUICulture), dtEnd.ToUniversalTime().ToString(CultureInfo.CurrentUICulture), tsDuration.Hours + sHour + tsDuration.Minutes.ToString(CultureInfo.CurrentUICulture) + sMin + tsDuration.Seconds.ToString(CultureInfo.CurrentUICulture) + sSecs);
}
args.PercentComplete = 100;
updateStatus(args);
return(output);
}
public static int Process()
{
Assembly assem = Assembly.GetEntryAssembly();
AssemblyName assemName = assem.GetName();
StreamWriter swTSQL = null;
if (_bAddDateTimeFolder)
{
if (!_outputDir.ToString().EndsWith("\\"))
{
_outputDir.Append("\\");
}
_outputDir.Append(DateTime.Now.ToString("dd-MMMM-yyyy HHmm"));
}
DirectoryInfo di = Directory.CreateDirectory(_outputDir.ToString());
if (di.Exists)
{
string file = CommonFunc.FormatString("{0}\\{1}.sql", di.FullName, _SourceDatabase);
swTSQL = new StreamWriter(File.Create(file));
}
else
{
Console.WriteLine(CommonFunc.FormatString(Properties.Resources.BCPOutputDirectoryNotFound, ConfigurationManager.AppSettings["BCPFileDir"]));
return(-1);
}
if (_outputDir.ToString().EndsWith("\\"))
{
_OutputResultsFile = _outputDir.ToString() + _OutputResultsFile;
}
else
{
_OutputResultsFile = _outputDir.ToString() + "\\" + _OutputResultsFile;
}
try
{
File.AppendAllText(_OutputResultsFile, CommonFunc.FormatString(Properties.Resources.ProgramVersion, assemName.Name, assemName.Version.ToString()) + Environment.NewLine);
}
catch (Exception ex)
{
Console.WriteLine(CommonFunc.FormatString(Properties.Resources.ErrorOpeningFile, _OutputResultsFile, ex.Message));
return(-1);
}
// Fill in necessary information
ServerConnection conn = new ServerConnection();
conn.ServerInstance = _SourceServerName;
//conn.DatabaseName = _SourceDatabase;
// Setup capture and execute to be able to display script
conn.SqlExecutionModes = SqlExecutionModes.ExecuteSql;
// Set connection timeout
conn.ConnectTimeout = 30;
if (_bSourceConnectNTAuth == true)
{
// Use Windows authentication
conn.LoginSecure = true;
}
else
{
// Use SQL Server authentication
conn.LoginSecure = false;
conn.Login = _SourceUserName;
conn.Password = _SourcePassword;
}
// Go ahead and connect
conn.Connect();
Server svr = CommonFunc.GetSmoServer(conn);
AsyncUpdateStatus updateStatus = new AsyncUpdateStatus(SourceAsyncUpdateStatusHandler);
SourceServer tp = new SourceServer();
foreach (Database sdb in svr.Databases)
{
if (sdb.Name.Equals(_SourceDatabase, StringComparison.CurrentCultureIgnoreCase))
{
tp.GenerateScriptFromSourceServer(svr, sdb, swTSQL, updateStatus, _outputDir.ToString(), _ObjectSelector);
break;
}
}
return(0);
}
public void GenerateScriptFromSourceServer(Server sourceServer, Database sourceDatabase, StreamWriter swTSQL, AsyncUpdateStatus updateStatus, string outputDir, ObjectSelector objectFilter)
{
_updateStatus = updateStatus;
_smoScriptOpts = SMOScriptOptions.CreateFromConfig();
_ObjectSelector = objectFilter;
//if (_smoScriptOpts.TargetServer == _smoScriptOpts.GetLocalizedStringValue("ServerType_AzureSQLDatabase"))
//{
// _ObjectSelector.Assemblies.Script = false;
// _ObjectSelector.SchemaCollections.Script = false;
//}
DateTime dtStart = DateTime.Now;
AsyncNotificationEventArgs eventArgs = new AsyncNotificationEventArgs(NotificationEventFunctionCode.GenerateScriptFromSQLServer, 0, "", CommonFunc.FormatString(Properties.Resources.MessageProcessStarted, dtStart.ToString(CultureInfo.CurrentUICulture), dtStart.ToUniversalTime().ToString(CultureInfo.CurrentUICulture)) + Environment.NewLine, Color.DarkBlue);
_updateStatus(eventArgs);
ScriptDatabase sdb = new ScriptDatabase();
sdb.Initialize(sourceServer, sourceDatabase, _updateStatus, _smoScriptOpts, swTSQL, true);
eventArgs.DisplayText = "";
eventArgs.StatusMsg = Properties.Resources.MessageSorting;
eventArgs.PercentComplete = 1;
_updateStatus(eventArgs);
// Tables, Views, Stored Procedures, and Triggers can all have dependencies. GetSortedObjects returns
// these objects in dependency order.
SqlSmoObject[] smoAssemblies = GetAssemblies(sourceDatabase);
SqlSmoObject[] smoPartitionFunctions = GetPartitionFunctions(sourceDatabase);
SqlSmoObject[] smoPartitionSchemes = GetPartitionSchemes(sourceDatabase);
SqlSmoObject[] smoTriggers = GetTriggers(sourceServer, sourceDatabase);
SqlSmoObject[] smoRoles = GetRoles(sourceDatabase);
SqlSmoObject[] smoSchemas = GetSchemas(sourceDatabase);
SqlSmoObject[] smoSchemaCols = GetSchemaCollections(sourceServer, sourceDatabase);
SqlSmoObject[] smoUDTs = GetUDTs(sourceDatabase);
SqlSmoObject[] smoUDTTs = GetUDTTs(sourceDatabase);
SqlSmoObject[] smoSynonyms = GetSynonyms(sourceDatabase);
SqlSmoObject[] sorted = GetSortedObjects(sourceDatabase);
if (Regex.IsMatch(_smoScriptOpts.ScriptDropCreate, _smoScriptOpts.GetLocalizedStringValue("SOSDrop"), RegexOptions.IgnoreCase) ||
Regex.IsMatch(_smoScriptOpts.ScriptDropCreate, _smoScriptOpts.GetLocalizedStringValue("SOSDropCreate"), RegexOptions.IgnoreCase))
{
eventArgs.StatusMsg = Properties.Resources.MessageCreatingDropScripts;
eventArgs.PercentComplete = 2;
_updateStatus(eventArgs);
ScriptDrops(sorted, smoRoles, smoSchemas, smoSchemaCols, smoUDTs, smoUDTTs, smoAssemblies, smoSynonyms, smoPartitionFunctions, smoPartitionSchemes, sdb);
}
if (Regex.IsMatch(_smoScriptOpts.ScriptDropCreate, _smoScriptOpts.GetLocalizedStringValue("SOSCreate"), RegexOptions.IgnoreCase) ||
Regex.IsMatch(_smoScriptOpts.ScriptDropCreate, _smoScriptOpts.GetLocalizedStringValue("SOSDropCreate"), RegexOptions.IgnoreCase))
{
SourceProcessor sp = new SourceProcessor();
sp.Initialize(sdb, _smoScriptOpts, _updateStatus, eventArgs, outputDir);
// Roles, Schemas, XML Schema Collections and UDT have no dependencies. Thus we process one at a time.
if (!Regex.IsMatch(_smoScriptOpts.ScriptTableAndOrData, _smoScriptOpts.GetLocalizedStringValue("ScriptOptionsTableData"), RegexOptions.IgnoreCase))
{
if (sp.Process(DatabaseObjectsTypes.Assemblies, smoAssemblies, 3))
{
return;
}
if (sp.Process(DatabaseObjectsTypes.PartitionFunctions, smoPartitionFunctions, 6))
{
return;
}
if (sp.Process(DatabaseObjectsTypes.PartitionSchemes, smoPartitionSchemes, 9))
{
return;
}
if (sp.Process(DatabaseObjectsTypes.Roles, smoRoles, 12))
{
return;
}
if (sp.Process(DatabaseObjectsTypes.Schemas, smoSchemas, 15))
{
return;
}
if (sp.Process(DatabaseObjectsTypes.XMLSchemaCollections, smoSchemaCols, 20))
{
return;
}
if (sp.Process(DatabaseObjectsTypes.UserDefinedDataTypes, smoUDTs, 25))
{
return;
}
if (sp.Process(DatabaseObjectsTypes.UserDefinedTableTypes, smoUDTTs, 30))
{
return;
}
if (sp.Process(DatabaseObjectsTypes.Synonyms, smoSynonyms, 35))
{
return;
}
}
if (sp.Process(sorted, 40))
{
return;
}
if (!Regex.IsMatch(_smoScriptOpts.ScriptTableAndOrData, _smoScriptOpts.GetLocalizedStringValue("ScriptOptionsTableData"), RegexOptions.IgnoreCase))
{
if (sp.Process(DatabaseObjectsTypes.Triggers, smoTriggers, 95))
{
return;
}
}
}
if (swTSQL != null)
{
swTSQL.Flush();
swTSQL.Close();
}
DateTime dtEnd = DateTime.Now;
TimeSpan tsDuration = dtEnd.Subtract(dtStart);
string sHour = tsDuration.Minutes == 1 ? Properties.Resources.MessageHour : Properties.Resources.MessageHours;
string sMin = tsDuration.Minutes == 1 ? Properties.Resources.MessageMinute : Properties.Resources.MessageMinutes;
string sSecs = tsDuration.Seconds == 1 ? Properties.Resources.MessageSecond : Properties.Resources.MessageSeconds;
eventArgs.StatusMsg = Properties.Resources.Done;
eventArgs.DisplayColor = Color.DarkCyan;
if (_smoScriptOpts.CheckCompatibility() == 1)
{
eventArgs.DisplayText = CommonFunc.FormatString(Properties.Resources.MessageFinishedNoAnalysis, dtEnd.ToString(CultureInfo.CurrentUICulture), dtEnd.ToUniversalTime().ToString(CultureInfo.CurrentUICulture), tsDuration.Hours + sHour + tsDuration.Minutes.ToString(CultureInfo.CurrentUICulture) + sMin + tsDuration.Seconds.ToString(CultureInfo.CurrentUICulture) + sSecs);
}
else
{
eventArgs.DisplayText = CommonFunc.FormatString(Properties.Resources.MessageFinishedWithAnalysis, dtEnd.ToString(CultureInfo.CurrentUICulture), dtEnd.ToUniversalTime().ToString(CultureInfo.CurrentUICulture), tsDuration.Hours + sHour + tsDuration.Minutes.ToString(CultureInfo.CurrentUICulture) + sMin + tsDuration.Seconds.ToString(CultureInfo.CurrentUICulture) + sSecs);
}
eventArgs.PercentComplete = 100;
_updateStatus(eventArgs);
}
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);
}
}
public static void Process()
{
TargetProcessor tp = new TargetProcessor();
AsyncUpdateStatus updateStatus = new AsyncUpdateStatus(TargetAsyncUpdateStatusHandler);
string sqlToExecute = CommonFunc.GetTextFromFile(_FileToProcess);
_OutputResultsDirectory = _OutputResultsFile.Substring(0, _OutputResultsFile.LastIndexOf('\\') + 1);
TargetServerInfo tsi = new TargetServerInfo();
tsi.ServerInstance = _TargetServerName;
tsi.TargetDatabase = _TargetDatabase;
if (_bTargetConnectNTAuth == true)
{
// Use Windows authentication
tsi.LoginSecure = true;
}
else
{
// Use SQL Server authentication
tsi.LoginSecure = false;
tsi.Login = _TargetUserName;
tsi.Password = _TargetPassword;
}
tsi.Version = "";
try
{
Retry.ExecuteRetryAction(() =>
{
using (SqlConnection con = new SqlConnection(tsi.ConnectionStringRootDatabase))
{
ScalarResults sr = SqlHelper.ExecuteScalar(con, CommandType.Text, "SELECT @@VERSION");
string version = (string)sr.ExecuteScalarReturnValue;
if (version.IndexOf("Azure") > 0)
{
tsi.ServerType = ServerTypes.AzureSQLDB;
}
else
{
tsi.ServerType = ServerTypes.SQLServer;
}
Match result = Regex.Match(version, @"[0-9]+\.[0-9]+\.[0-9]+\.[0-9]+");
if (result.Success)
{
tsi.Version = result.Value;
}
else
{
Console.WriteLine(CommonFunc.FormatString(Properties.Resources.ErrorGettingSQLVersion, version));
return;
}
}
});
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
return;
}
int dbMajor = Convert.ToInt32(tsi.Version.Substring(0, 2));
//ok, we don't want to break the old version where they just passed in an int value without the "MB" or "GB". So, we
//can guess based on the edition what it should be.
//web = 100 MB, 1 GB, 5 GB
//business = 10 GB, 20 GB, 30 GB, 40 GB, 50 GB, 100 GB, or 150 GB
//basic = 100 MB, 500 MB, 1 GB, 2 GB
//standard = 100 MB, 500 MB, 1 GB, 2 GB, 5 GB, 10 GB, 20 GB, 30 GB, 40 GB, 50 GB, 100 GB, 150 GB, 200 GB, or 250 GB
//premium = 100 MB, 500 MB, 1 GB, 2 GB, 5 GB, 10 GB, 20 GB, 30 GB, 40 GB, 50 GB, 100 GB, 150 GB, 200 GB, 250 GB, 300 GB, 400 GB, or 500 GB
if (tsi.ServerType == ServerTypes.AzureSQLDB)
{
if (_TargetEdition == "web")
{
if (dbMajor > 11)
{
Console.WriteLine(CommonFunc.FormatString(Properties.Resources.InvalidDatabaseEdition1V12, _TargetEdition));
return;
}
if (_TargetDatabaseSize[_TargetDatabaseSize.Length - 1] != 'B')
{
if (_TargetDatabaseSize == "1")
{
_TargetDatabaseSize = "1 GB";
}
else if (_TargetDatabaseSize == "5")
{
_TargetDatabaseSize = "5 GB";
}
else
{
_TargetDatabaseSize = "100 MB";
}
}
}
else if (_TargetEdition == "business")
{
if (dbMajor > 11)
{
Console.WriteLine(CommonFunc.FormatString(Properties.Resources.InvalidDatabaseEdition1V12, _TargetEdition));
return;
}
if (_TargetDatabaseSize[_TargetDatabaseSize.Length - 1] != 'B')
{
_TargetDatabaseSize = _TargetDatabaseSize + " GB";
}
}
else if (_TargetEdition == "standard")
{
if (dbMajor > 11)
{
if (!Regex.IsMatch(_TargetDatabasePerforance, "S[0-3]"))
{
Console.WriteLine(CommonFunc.FormatString(Properties.Resources.InvalidPerformanceLevelV12, _TargetDatabasePerforance, _TargetEdition));
return;
}
}
else
{
if (!Regex.IsMatch(_TargetDatabasePerforance, "S[0-2]"))
{
Console.WriteLine(CommonFunc.FormatString(Properties.Resources.InvalidPerformanceLevel, _TargetDatabasePerforance, _TargetEdition));
return;
}
}
}
else if (_TargetEdition == "premium")
{
if (!Regex.IsMatch(_TargetDatabasePerforance, "P[1-3]"))
{
Console.WriteLine(CommonFunc.FormatString(Properties.Resources.InvalidPerformanceLevel, _TargetDatabasePerforance, _TargetEdition));
return;
}
}
}
if (dbMajor == 12)
{
// Note with dbMajor 12 and above, disk size no longer matters
_TargetDatabaseSize = "";
}
else
{
if (!ValidateDatabaseSize(_TargetEdition, _TargetDatabaseSize))
{
Console.WriteLine(CommonFunc.FormatString(Properties.Resources.InvalidDatabaseSize, _TargetEdition, _TargetDatabaseSize));
return;
}
}
//AsyncProcessingStatus.FinishedProcessingJobs = true;
AsyncQueueBCPJob queueBCPJob = new AsyncQueueBCPJob(AsyncQueueJobHandler);
DatabaseCreateStatus dcs = tp.CreateDatabase(tsi, _Collation, _TargetEdition, _TargetDatabaseSize, _TargetDatabasePerforance, _bDropExistingDatabase);
if (dcs == DatabaseCreateStatus.Waiting)
{
Console.Write(Properties.Resources.WaitingForDBCreation);
while (!tp.DoesDatabaseExist(tsi))
{
Thread.Sleep(2000);
Console.Write(".");
}
Thread.Sleep(2000);
Console.Write(Properties.Resources.DBCreated + Environment.NewLine);
}
else if (dcs == DatabaseCreateStatus.Failed)
{
Console.WriteLine(Properties.Resources.DBCreateFailed);
return;
}
tp.ExecuteSQLonTarget(tsi, updateStatus, queueBCPJob, sqlToExecute);
}
