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

[Jorge Choy]

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

[Wyatt]

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.

[Jorge Choy]

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

[Wyatt]

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?

[Jorge Choy]

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

[Jorge 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?

Regards
Jorge Enrique

[Wyatt]

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

[anand vertex]

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

[Sam]

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