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

Hey Jorge,

Yes, wyBuild supports that. Just make sure the batch file you're using to execute the SQL script returns the exit code from sqlcmd.

Quits the CMD.EXE program (command interpreter) or the current batchscript.


EXIT [/B] [exitCode]


  /B          specifies to exit the current batch script instead of              CMD.EXE.  If executed from outside a batch script, it              will quit CMD.EXE


  exitCode    specifies a numeric number.  if /B is specified, sets              ERRORLEVEL that number.  If quitting CMD.EXE, sets the process              exit code with that number.

For example, if you're just executing one SQLCMD line then the batch file would look something like this:

sqlcmd .....exit /B %ERRORLEVEL%

If you're executing more than 1 line then you need to store the %ERRORLEVEL% after each SQLCMD call correctly handle any failures.

Tell me if you need any help.

Thanks very much for your reply and suggestions.

We have just purchased wybuild last week and we are really new with this product

What I was thinking is execute a sql script file so I could specify only one sqlcmd command. For example: sqlcmd -U userDummy -P xxxxxxx -S NEPTUNE\SQLEXPRESS -i "script.sql"

Is it possible?? I mean, can we catch a return code even by executing a sql file from a batch process?

Thanks

Yes, in your example just create a simple batch file (e.g. sqlchanges.bat), then paste your SQLCMD line followed by the exit code line:

sqlcmd -U userDummy -P xxxxxxx -S NEPTUNE\SQLEXPRESS -i "%~dp0YOURSCRIPTNAME.sql"exit /B %ERRORLEVEL%

Then, in wyBuild add the "sqlchanges.bat" file, "YOURSCRIPTNAME.sql" file, then click the "sqlchanges.bat file and click the execute file after the update.

Some side notes: See the "%~dp0" before "YOURSCRIPTNAME.sql"? That's not a typo. What "%~dp0" means is "get the absolute path to the batch file's directory". Does that make sense?

Absolutely, Thanks very much. We are creating a script in order to test our first mini patch.

Thanks again

Hi There.

It is me again. I am creating a batch file with an .exe. The reason is because I need to retrive the credentials (username and password), database name, and server name located in our client server.

So What I have done is add two files in the "Temporary folder" section of the WyBuild folder and files section.

The first file; a script called changesV11174.sql, which apply the changes to the database; and the second file is an executable file "changesv11174.exe" that reads the database connection string from the configuration file of the client application.

Once the connection string is read, then the executable file creates a batch file called tempBatchV11714.cmd with the same suggestions that gave me before.

Thats is working fine, but once the executable file stars a process for the cmd execution it fails because i believe that the .cmd file is generated in the application folder instead of the temporary folder.

Now I have tried to use the variable %temp%, but WyBuid is not recognising it.

Do you have a better suggestion for my scenario?

Kind RegardsJ. ChoyApplied Network Solutions

Hi there

Why WyBuild is not recognising the %temp% variable?? I could quickly fix that if I have a way of detecting where the sql file was deployed

RegardsJ. Choy

I have managed to capture the %temp% value, but is not giving me the path where the temporary files are copied.

The %temp% variable is giving me "C:\Users\ADMINI~1\AppData\Local\Temp\1". However, I have noticed the the temporary files are copied in a random directory like "C:\Users\Administrator\AppData\Local\Temp\1\w3535\temp".

So How could I indicate my sqlcmd that the sql file is located in that random temporary folder???

Remember that the batch process cmd file is created with the exe file hence is saved in the application folder by default.

Please help

RegardJ Choy

Well, as I said above, you can get the current file's current directory by using the "%~dp0" within your batch file. So you can pass this value to your custom EXE so it will know where the "temp" folder is.

Does that make sense?

Or you can modify your EXE to detect where it's sitting (which is relatively easy).

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); }

}

Hi Wyatt

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

RegardsJ. 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

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

RegardsJ. 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

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 %~dp0exit /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 🙂

RegardsJ. Choy

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?

RegardsJorge Enrique

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

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

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