non-zero return code using sqlcmd. (Rollback facilities)

See: Executing files in your update, RE: %wu-temp% variable

Hi
May i know how to find the pathof temp folder of the batch file and sql file placed in temporary folder in build wyupdate.?

Here's a pretty good tutorial on error levels and control flow in batch files: Errorlevels.

Hi there

It is me again.

We are planning to create our second patch soon and we have realised that we will need to executed more than 1 line of SQLCMD.

How can we store the %ERRORLEVEL% value. And if in the case that the first SQLCMD line fail, then how could we inmidiately return the error value and not execute the rest of the SQLCMD lines?

Regards
Jorge Enrique

Hi Wyatt

Thanks again for your suggestion.

It is working now, but I have to do additional amendments. It may help others with the same situation.

1.- in the batch cmd that you recommended me to create i had to specified the %~dp0 variable for the exe file and the parameter as well. For example:

"%~dp0Web247AppConfigMerge.exe" --APPLY247LIBWSV11714 %~dp0
exit /B %ERRORLEVEL%

2.- Executing the second cmd file generated from the exe file, I had to specify the option -b for the SQLCMD utility. Otherwise, the exe file is not going to catch the exit code of the SQLCMD.

It is working nicely. However I have tested on purpose using long path directories and I was getting the error message that the input variable for the .exe file was too long.

Now, that is not the case as I believe our clients do not have a long temp folder, but just to let you know about it.

Anyway, I believe that we will change the strategy in the future when you release the version 2.6.18.

Again, Thanks a lot for your help. We are ready for deploying our patches

Regards
J. Choy

We'll add another variable to wyBuild in version 2.6.18 that allows you to reference the "Temporary folder" being used by wyUpdate (which, as you've noticed, differs between updates).

In the meantime, the way to solve this is to add a *.bat file to the Temporary folder in wyBuild, have it execute, and this batch file will pass it's directory (i.e. the temp directory) to your special SQL apps.

Does that make sense?

Hi Wyatt

Unfortunately I cannot add the batch file to the temporary folder section in WyBuild. As I mentioned before, the reason is because first I need to retrieve the database connection string from the client side as we do not know the database name, username and password for connecting to the database.

The batch file is created manually or generated from and .exe file. The steps that we have done are:

From WyBuild:

1.- We add the SQL file script to the temporary folder section of WyBuild.
2.- We add the .exe file to the temporary folder section of WyBuild and set to be executed with the option "before update has begun".


Then the client execute the WyUpdate and the results are as follow:

1.- Tempory files (SQL file script and the .exe file) are copied to a temp directory that is not represented by the %temp% folder.
%temp% = "C:\Users\ADMINI~1\AppData\Local\Temp\1".
WyUpdate copied them to a random folder like: "C:\Users\Administrator\AppData\Local\Temp\1\w3535\temp".

2.- The .exe file is executed and creates the batch file ( .cmd ).

3.- For some reason the batch file ( .cmd ) is created by the .exe file to the application folder firectory instead of the temp folder.

4.- The generated batch file ( .cmd ) is executed by the same .exe file.

5.- The cmd fail because it shows that the script file cannot be found. It is because the .cmd file was generated in the application folder and executed from there hence cannot see the script file.

Now, if I use the option "%~dp0", then is still translating it to the application folder as I believe the execution context is still there and the cmd file was created there as well.

%temp% is working fine, but is still giving me the error that cannot fine the file. This is because WyUpdate is NOT COPYING the files to that directory. WyUpdate generates random subfolders withing %temp% such as "w3535\temp" so I do not have a way of specifiying the batch sqlcmd that the script is located there.

So My question is how could I retrieve the real temp folder path where WyUpdate is copying the script.

If that is not posible, can you advice us who to deal when you do not have the name of the database, username and password of the clients to be updated? Please help

Thanks

Regards
J. Choy

Are you adding the batch file and/or the exe program to the temporary folder? That's what I was talking about in the example. If the batch file is in the same temp directory as the *.sql then what I wrote should work.

Hi Wyatt

Do you have any other idea? or why the %dp0 is still retrieving the application path instead of the temporary folder path?

Regards
J. Choy

Hi Wyatt

Thanks for replying me.

I did your suggestion, but the %~dp0 is still giving me the application path and not the temporary folder where the sql script is located. I believe is because the cmd batch file is generated as well in the application folder

Look what I have done so far in the exe application:

if (File.Exists("tempBatchV11714.cmd"))
{
File.Delete("tempBatchV11714.cmd");
}

//Then I create the 'cmd' file, but for some reason is generated in the application directory

using (StreamWriter sw = File.CreateText("tempBatchV11714.cmd"))
{ sw.WriteLine("sqlcmd -U " + sqlServerUser + " -P " + sqlServerPwd + " -S " + sqlServer + " -i \""+aTempFolder+"\\changesV11714.sql\"");
sw.WriteLine("pause");
sw.WriteLine("exit /B %ERRORLEVEL%");
//Tested before
// --> Tested before --> sw.WriteLine("sqlcmd -U "+sqlServerUser+" -P "+sqlServerPwd+" -S "+sqlServer+" -i \"%~dp0changesV11714.sql\"");
//--> Tested before --> sw.WriteLine("sqlcmd -U " + sqlServerUser + " -P " + sqlServerPwd + " -S " + sqlServer + " -i \"%temp%\\changesV11714.sql\"");
}



in the case of "+aTempFolder+", is a function parameter. This variable is having the value passed from WyBuild with %temp%.



The complete funtion is as follow:

private static void apply247libWsV11714(string aAppDir, string aTempFolder)
{
XmlDocument xmlDoc = GetWebConfig();
XmlNode xmlRootNode = xmlDoc.SelectSingleNode("//configuration");
if (xmlRootNode == null)
{
throw new Exception("configuration node in web.config not found!");
}

XmlNode connectionStringNode = xmlDoc.SelectSingleNode("/configuration/connectionStrings[last()]");
if (!connectionStringNode.HasChildNodes)
throw new Exception("Cannot find connection string in configuraton file web.config of 247lib Web Service!");
XmlNode connectionStrLocalNode = connectionStringNode.SelectSingleNode("add[@name='LocalConnStr']");
string connectionStrLocal = connectionStrLocalNode.Attributes["connectionString"].Value;

string[] spplitedConnString = connectionStrLocal.Split(new char[] {';'}, StringSplitOptions.RemoveEmptyEntries);
string sqlServer = string.Empty;
string sqlServerUser = string.Empty;
string sqlServerPwd = string.Empty;
string sqlServerDataBase = string.Empty;
foreach (string strConnPart in spplitedConnString)
{
if ((strConnPart.ToUpper().IndexOf("DATA SOURCE") > -1) || (strConnPart.ToUpper().IndexOf("SERVER") > -1))
sqlServer = strConnPart.Split(new char[] {'='}, StringSplitOptions.RemoveEmptyEntries)[1];
if ((strConnPart.ToUpper().IndexOf("USER ID") > -1) || (strConnPart.ToUpper().IndexOf("UID") > -1))
sqlServerUser = strConnPart.Split(new char[] { '=' }, StringSplitOptions.RemoveEmptyEntries)[1];
if ((strConnPart.ToUpper().IndexOf("PWD") > -1) || (strConnPart.ToUpper().IndexOf("PWD") > -1))
sqlServerPwd = strConnPart.Split(new char[] { '=' }, StringSplitOptions.RemoveEmptyEntries)[1];
if ((strConnPart.ToUpper().IndexOf("INITIAL CATALOG") > -1) || (strConnPart.ToUpper().IndexOf("DATABASE") > -1))
sqlServerDataBase = strConnPart.Split(new char[] { '=' }, StringSplitOptions.RemoveEmptyEntries)[1];
}


if (File.Exists("tempBatchV11714.cmd"))
{
File.Delete("tempBatchV11714.cmd");
}

using (StreamWriter sw = File.CreateText("tempBatchV11714.cmd"))
{
sw.WriteLine("sqlcmd -U " + sqlServerUser + " -P " + sqlServerPwd + " -S " + sqlServer + " -i \""+aTempFolder+"\\changesV11714.sql\"");
sw.WriteLine("pause");
sw.WriteLine("exit /B %ERRORLEVEL%");
}

ProcessStartInfo startInfo = new ProcessStartInfo();
//startInfo.FileName = "sqlcmd";
startInfo.FileName = aAppDir+"tempBatchV11714.cmd"; //--> I HAD TO SPECIFY THE APP FOLDER FOR EXECUTIN CMD
startInfo.ErrorDialog = false;
//startInfo.ErrorDialog = true;
startInfo.UseShellExecute = false;
//startInfo.WorkingDirectory = Directory.GetCurrentDirectory();
string[] args = new string[] { "-U", sqlServerUser, "-P", sqlServerPwd, "-S", sqlServer, "-d", sqlServerDataBase, "-i", Directory.GetCurrentDirectory() + "\\changesV11714.sql" };
//string[] quoted = Array.ConvertAll<string, string>(args, delegate(string input) { return string.Format("\"{0}\"", input); });
//startInfo.Arguments = string.Join(" ", args);

if (!System.Diagnostics.EventLog.SourceExists("wyUpdateTempExecution"))
{
System.Diagnostics.EventLog.CreateEventSource("wyUpdateTempExecution", "wyUpdateTempExecution");
}
System.Diagnostics.EventLog.WriteEntry("wyUpdateTempExecution", "Begining process for sql script changesV11714.sql - Process:" + startInfo.FileName, EventLogEntryType.Information);


Process process;
try
{
process = Process.Start(startInfo);
if (process == null)
{
//startInfo.EnvironmentVariables["Path"] = pathEnviroment;
System.Diagnostics.EventLog.WriteEntry("wyUpdateTempExecution", "Error starting process from executable file Web247AppConfigMerge.exe - Exit Code 1001", EventLogEntryType.Error);
//Clipboard.SetText("Error starting process from executable file Web247AppConfigMerge.exe - Exit Code 1001");
throw new Exception("Error starting process from executable file Web247AppConfigMerge.exe - Exit Code 1001");
}
if (process.WaitForExit(1000 * 60))
{
if (process.ExitCode != 0)
{
System.Diagnostics.EventLog.WriteEntry("wyUpdateTempExecution", "Error starting process from executable file Web247AppConfigMerge.exe - Exit Code 1002 - Process Exit Code " + process.ExitCode, EventLogEntryType.Error);
//Clipboard.SetText("Error starting process from executable file Web247AppConfigMerge.exe - Exit Code 1002");
throw new Exception("Error starting process from executable file Web247AppConfigMerge.exe - Exit Code 1002- Process Exit Code " + process.ExitCode);
}
//startInfo.EnvironmentVariables["Path"] = pathEnviroment;
if (File.Exists("tempBatchV11714.cmd"))
{
File.Delete("tempBatchV11714.cmd");
}
return;
}
process.CloseMainWindow();
if (!process.WaitForExit(3000))
{
process.Kill();
}
//startInfo.EnvironmentVariables["Path"] = pathEnviroment;
System.Diagnostics.EventLog.WriteEntry("wyUpdateTempExecution", "Error starting process from executable file Web247AppConfigMerge.exe - Exit Code 1003", EventLogEntryType.Error);
//Clipboard.SetText("Error starting process from executable file Web247AppConfigMerge.exe - Exit Code 1003");
throw new Exception("Error starting process from executable file Web247AppConfigMerge.exe - Exit Code 1003");

}
catch (Exception exc)
{
// TODO: log exception
//startInfo.EnvironmentVariables["Path"] = pathEnviroment;
System.Diagnostics.EventLog.WriteEntry("wyUpdateTempExecution", exc.Message, EventLogEntryType.Error);
//Clipboard.SetText(exc.Message);
throw new Exception(exc.Message);
}



}