Combine certain checks before executing

[SIMMS7400]

Hi Folks -

I'd first like to thank everyone, particularly fox, Steffen, Doug, Squash, and Antonio for all the help as of late. I've learned an incredible amount with all of your suggestions and advice. Thank you!

With that said, I'm just in the final stages with one of my scripts and I was wondering the best way to capture some preliminary path and variable existence checks.

The first portion of my script sets certain paths and script names as variables.

However, I've added some checks in there that will spool to a text file (DNE.txt) if it doesn' exist.

Here it is:

Code: Select all

:BEGIN
echo    \\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\
echo    \\                                     \\
echo    \\        WELCOME TO SQL MGMT !        \\
echo    \\                                     \\
echo    \\             DOS TIPS                \\
echo    \\                                     \\
echo    \\      0        0  0      0    0      \\
echo    \\    \/!\/     (=!!=)   \/!\/\/!\/    \\
echo    \\     / \      /\  /\    / \  / \     \\
echo    \\                                     \\
echo    \\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\
TIMEOUT /T 5 >nul

CLS
echo *********************************************
echo This batch can perform the following actions:
echo ---------------------------------------------
echo.
echo [1] Backup SQL Server Database
echo.
echo [2] Backup local SQL DB and restore locally or remote
echo. 
echo [3] Compare ^& synchronize a single SQL Table
echo.
echo [4] Compare ^& synchronize all SQL Tables
echo.
echo [5] Restore SQL DB from *.BAK file 
echo.
echo ***************************************************
echo Which of the above options do you want to perform?
echo ---------------------------------------------------
echo.
CHOICE /N /C 12345 /M "Option: "

IF ERRORLEVEL 5 SET INT_OPT=5
IF ERRORLEVEL 4 SET INT_OPT=4
IF ERRORLEVEL 3 SET INT_OPT=3
IF ERRORLEVEL 2 SET INT_OPT=2
IF ERRORLEVEL 1 SET INT_OPT=1

   :::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
   ::           I N I T I A L I Z E   SQL   V A R I A B L E S                   ::
   :::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
   
   CLS
   color CF
   echo :::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
   echo.::                                                                     ::
   echo.::-- Setting Microsoft SQL Server Installation path --::               ::
   echo.::                                                                     ::
   echo ::-- Setting SQL Server Utility paths --::                             ::
   echo.::                                                                     ::
   echo ::-- Setting CMD ^& SQL script variables --::                          ::
   echo ::                                                                     ::
   echo ::-- Please allow 15-20 seconds to complete --::                       ::
   echo ::                                                                     ::
   echo.:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::

   ::-- SET SQLCMD output file --::
   ::-- Used for all command lines to direct output --::
   ::-- Option [1][2][3][4][5] --::
   
      SET SQL_OUTPUT=SQL_SERVER_OP.txt

   
   ::-- SET SQL Ghosted Users Report output file --::
   ::-- Option [1][2][3][4][5] --::
   
      SET SQL_GHOST_RPT=SQL_GHOST_RPT.txt   
   
   
   ::-- Set SQLCMD SQL script name --::   
   ::-- Option [1] --::
   
      SET SQL_KS=KILLSQLSESSION.sql
     
   ::-- Set SQLCMD SQL script name --::   
   ::-- Option [2] --::   
   
      SET RSTR_SQL_DB=RSTR_SQL_DB.sql
    
   ::-- Set SQLCMD SQL script name --::
   ::-- Option [3] --::
   
      SET SQLS=EXEC_SQL_SYNC.sql
   
   
   ::-- Set SQLCMD SQL ^& CMD script name --::
   ::-- Option [4] --::
   
      SET EXEC_TBCA=EXEC_TBLDIFF_CMD_ALL.cmd
      SET SQLS2=EXEC_SQL_SYNC_ALL.sql
      SET BTCA=BUILD_TBLDIFF_CMD_ALL.sql   
   
   ::-- SQL Path verification section --::
   If exist "%CD%DNE.txt" DEL "%CD%DNE.txt"
      
   ::-- Return MSSQL path --::
     
   set "sql="
   for %%a in (c d e f g h i j k l m n o p q r s t u v w x y z) do if not defined sql (pushd "%%a:\" & for /d /r %%b in (MSSQL*.MSSQLSERVER) do set "sql=%%b"&set DL=%%a & popd)
   if not defined sql (
      echo ------------------------------------------------
      echo Microsoft SQL Server installation path not found 
      echo ------------------------------------------------
   )>> "%CD%DNE.txt"
   if exist "%sql%\MSSQL\" SET MSSQL_PATH=%sql%\MSSQL\
   
   ::-- Return fully qualified path for SQLCMD --::

   for %%D in (c d e f g h i j k l m n o p q r s t u v w x y z) do if exist %%D: (
      for /f "delims=" %%A in ('dir/b/s %%D:\sqlcmd.exe 2^>nul') do (
         SET SQL_CMD_PATH=%%A
      )
   ) 
   If not defined SQL_CMD_PATH echo SQLCMD.exe utility path not found >>"%CD%DNE.txt"

   ::-- Return fully qualified path for tablediff.exe --::

   for %%D in (c d e f g h i j k l m n o p q r s t u v w x y z) do if exist %%D: (
      for /f "delims=" %%A in ('dir/b/s %%D:\tablediff.exe 2^>nul') do (
         SET TD_CMD_PATH=%%A
      )
   )
   If not defined TD_CMD_PATH (
      echo ------------------------------------
      echo tablediff.exe utility path not found 
      echo ------------------------------------
      ) >>"%CD%DNE.txt"
   
    ::-- Check for files before proceeding --::

   set DNE=
      for %%F in (
      %RSTR_SQL_DB%
      %SQL_KS%
      %BTCA%
      ) do if not exist %%F ( 
          echo --------------------------------------------------------
         echo %%F : Does not exist in current directory
         echo --------------------------------------------------------
         set DNE=1
         )>>"%CD%DNE.txt"
   
   ::-- If exists - Display DNE.txt output and exit batch session
   
   If exist "%CD%DNE.txt" start "" notepad.exe "%CD%DNE.txt" & EXIT 

   ::-- Reset color --::
   color 0f
   CLS   
   
   :::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
   ::                E N D   SQL   I N I T I A L I Z A T I O N                  ::
   :::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::

Is there anyway to capture the path checks and script name checks in one fell-swoop? As you can see just a few lines above, I'm already checking for the existence of the three script names, but was hoping to add the PATH checks in there as well.

SQL_CMD_PATH
TD_CMD_PATH
%RSTR_SQL_DB%
%SQL_KS%
%BTCA%


Thank you!

[SIMMS7400]

I've added the path checks as the end. Please let me know if both of the checks can be combined into 1.

Thanks!

Code: Select all

:BEGIN
echo    \\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\
echo    \\                                     \\
echo    \\        WELCOME TO SQL MGMT !        \\
echo    \\                                     \\
echo    \\             DOS TIPS                \\
echo    \\                                     \\
echo    \\      0        0  0      0    0      \\
echo    \\    \/!\/     (=!!=)   \/!\/\/!\/    \\
echo    \\     / \      /\  /\    / \  / \     \\
echo    \\                                     \\
echo    \\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\
TIMEOUT /T 5 >nul

CLS
echo *********************************************
echo This batch can perform the following actions:
echo ---------------------------------------------
echo.
echo [1] Backup SQL Server Database
echo.
echo [2] Backup local SQL DB and restore locally or remote
echo. 
echo [3] Compare ^& synchronize a single SQL Table
echo.
echo [4] Compare ^& synchronize all SQL Tables
echo.
echo [5] Restore SQL DB from *.BAK file 
echo.
echo ***************************************************
echo Which of the above options do you want to perform?
echo ---------------------------------------------------
echo.
CHOICE /N /C 12345 /M "Option: "

IF ERRORLEVEL 5 SET INT_OPT=5
IF ERRORLEVEL 4 SET INT_OPT=4
IF ERRORLEVEL 3 SET INT_OPT=3
IF ERRORLEVEL 2 SET INT_OPT=2
IF ERRORLEVEL 1 SET INT_OPT=1

    :::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
   ::           I N I T I A L I Z E   SQL   V A R I A B L E S                   ::
   :::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
   
   CLS
   color CF
   echo :::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
    echo.::                                                                     ::
    echo.::-- Setting Microsoft SQL Server Installation path --::               ::
    echo.::                                                                     ::
   echo ::-- Setting SQL Server Utility paths --::                             ::
   echo.::                                                                     ::
   echo ::-- Setting CMD ^& SQL script variables --::                          ::
    echo ::                                                                     ::
    echo ::-- Please allow 15-20 seconds to complete --::                       ::
    echo ::                                                                     ::
   echo.:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::

   ::-- SET SQLCMD output file --::
   ::-- Used for all command lines to direct output --::
   ::-- Option [1][2][3][4][5] --::
   
      SET SQL_OUTPUT=SQL_SERVER_OP.txt

   
   ::-- SET SQL Ghosted Users Report output file --::
   ::-- Option [1][2][3][4][5] --::
   
      SET SQL_GHOST_RPT=SQL_GHOST_RPT.txt   
   
   
   ::-- Set SQLCMD SQL script name --::   
   ::-- Option [1] --::
   
      SET SQL_KS=KILLSQLSESSION.sql
     
   ::-- Set SQLCMD SQL script name --::   
   ::-- Option [2] --::   
   
      SET RSTR_SQL_DB=RSTR_SQL_DB.sql
    
   ::-- Set SQLCMD SQL script name --::
   ::-- Option [3] --::
   
      SET SQLS=EXEC_SQL_SYNC.sql
   
   
   ::-- Set SQLCMD SQL ^& CMD script name --::
   ::-- Option [4] --::
   
      SET EXEC_TBCA=EXEC_TBLDIFF_CMD_ALL.cmd
      SET SQLS2=EXEC_SQL_SYNC_ALL.sql
      SET BTCA=BUILD_TBLDIFF_CMD_ALL.sql
   
   
   ::-- SQL Path verification section --::
   If exist "%CD%DNE.txt" DEL "%CD%DNE.txt"
      
   ::-- Return MSSQL path --::
     
   set "sql="
   for %%a in (c d e) do if not defined sql (pushd "%%a:\" & for /d /r %%b in (MSSQL*.MSSQLSERVER) do set "sql=%%b"&set DL=%%a & popd)
   if exist "%sql%\MSSQL\" SET MSSQL_PATH=%sql%\MSSQL\
   
   ::-- Return fully qualified path for SQLCMD --::

   for %%D in (c d e f g h i j k l m n o p q r s t u v w x y z) do if exist %%D: (
      for /f "delims=" %%A in ('dir/b/s %%D:\sqlcmd.exe 2^>nul') do (
         SET SQL_CMD_PATH=%%A
      )
   ) 

   ::-- Return fully qualified path for tablediff.exe --::

   for %%D in (c d e f g h i j k l m n o p q r s t u v w x y z) do if exist %%D: (
      for /f "delims=" %%A in ('dir/b/s %%D:\tablediff.exe 2^>nul') do (
         SET TD_CMD_PATH=%%A
      )
   )
   
    ::-- Verify file and path existences before proceeding --::

   set DNE=
      for %%F in (
      %RSTR_SQL_DB%
      %SQL_KS%
      %BTCA%
      ) do if not exist %%F ( 
          echo ---------------------------------------------------------------
         echo %%F : Does not exist in current directory
         echo ---------------------------------------------------------------
         set DNE=1
         )>>"%CD%DNE.txt"
         
   if not defined TD_CMD_PATH (
      If not defined SQL_CMD_PATH (
          echo ---------------------------------------------
         echo SQLCMD.exe utility path cannot be established
         echo ---------------------------------------------
      )
      echo ------------------------------------------------
      echo TABLEDIFF.exe utility path cannot be established
      echo ------------------------------------------------
   )>>"%CD%DNE.txt"

[foxidrive]

Using English to describe what you are doing, and giving small examples in code along with it, is a better way to illustrate your problem and target that exact issue.

If you have more aspects to describe then add them too, in targeted descriptions.

Readers can then go further and read a large section of code, already knowing the kinds of things they are looking for.

[SIMMS7400]

HI Fox -

I think I speak English Do my posts need more clarity?

[foxidrive]

HI Fox -

I think I speak English Do my posts need more clarity?

Did you understand any of the words I wrote after "English?"

Programming code is not English.

Using a phase "checks in there that will spool to a text file" has no meaning without context about the actual process.

Printer buffers spool their output to a printer, but spool is not a term that I've seen used when talking about text files and checking for items.

[SIMMS7400]

Fox -

Yes - which is why I'm confused. I thought my explanation was clear enough. Can't tell if you're being passive aggressive or not

[foxidrive]

Fox -

Yes - which is why I'm confused.

No you didn't understand. You ignored it completely without asking for clarification of any point that you didn't understand.

You're being obtuse and have a very poor grasp of programming.

You shove a wad of code in a post and say nothing about it, expecting people to spend their time to figure out what you are doing without a reasonable explanation of what the problem itself is or what you are doing.

I added some explanation to my earlier post.

[SIMMS7400]

Team -

I have the following checks in the preliminary portion of my code:

Code: Select all

   set DNE=
      for %%F in (
      %RSTR_SQL_DB%
      %SQL_KS%
      %BTCA%
      ) do if not exist %%F ( 
          echo ---------------------------------------------------------------
         echo %%F : Does not exist in current directory
         echo ---------------------------------------------------------------
         set DNE=1
         )>>"%CD%DNE.txt"
         
   if not defined TD_CMD_PATH (
      If not defined SQL_CMD_PATH (
          echo ---------------------------------------------
         echo SQLCMD.exe utility path cannot be established
         echo ---------------------------------------------
      )
      echo ------------------------------------------------
      echo TABLEDIFF.exe utility path cannot be established
      echo ------------------------------------------------
   )>>"%CD%DNE.txt"

Would there be anyway to combine the above two checks into 1?

Thanks

[foxidrive]

I'll be clear here and say that after more than 20 years of providing free computer support, I no longer read huge wads of code that come with a poor description or no description.

You have little awareness of the pitfalls in providing free programming support, and very often it's code that doesn't work or code that isn't even what the person is using.

Instead of asking questions about poor information and receiving verbal sparring matches from people who think they are so clever but who can't solve their own problems, I now spend my time giving people pointers on how they can improve their question and also the information they provide - to make the job of helping them easier for the volunteers.

[foxidrive]

This thread has been locked.

SIMMS7400, you can approach a moderator and provide information about your task.

They will reopen your thread if they think you have given enough information, but adding in huge blocks of code without discussing the aim of the task and also the problem is not good information in my view.