Send CMD output to Variable - having trouble

[SIMMS7400]

Hi Folks -

I'd like to send the output of a command to a variable.

Currently this is how I'm doing it:

Command Line:

Code: Select all

SET TBL_CNT=TBL_CNT.txt

SQLCMD -S EPM11124 -U EPMAdmin -P Peloton123! -d HYPDRM -Q "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'dbo' and TABLE_TYPE='BASE TABLE'" >>%TBL_CNT%

for /f "tokens=1*delims=:" %%G in ('findstr /n "^" %TBL_CNT%') do if %%G equ 3 echo %%H

PAUSE

The output looks like such:

-----------
129

(1 rows affected)

Result:

129

Any way to do this without sending to a text file?

Thank you!

[aGerman]

Did you try it that way?

Code: Select all

for /f "tokens=1* delims=:" %%G in (
 'SQLCMD -S EPM11124 -U EPMAdmin -P Peloton123! -d HYPDRM -Q "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'dbo' and TABLE_TYPE='BASE TABLE'"^|findstr /n "^"'
) do if %%G equ 3 echo %%H

Steffen

[pieh-ejdsch]

You take a 2nd findstr and variable set once

Code: Select all

for /f "tokens=1*delims=:" %%G in ('findstr /n "^" %TBL_CNT% ^|findstr "^3:"') do set "VAR=%%H"

Phil

[SIMMS7400]

Steffen -

That works like a charm!!! Thank you so much!!!

[SIMMS7400]

Did you try it that way?

Code: Select all

for /f "tokens=1* delims=:" %%G in (
 'SQLCMD -S EPM11124 -U EPMAdmin -P Peloton123! -d HYPDRM -Q "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'dbo' and TABLE_TYPE='BASE TABLE'"^|findstr /n "^"'
) do if %%G equ 3 echo %%H

Steffen

Is there a way to use variables within my command for the switched? When I try to add them it fails.

This is my intention:

Code: Select all

for /f "tokens=1* delims=:" %%G in (
 'SQLCMD -S %SSQLS% -U  %SSQLU% -P %SSQLP% -d %SSQLDB% -Q "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'dbo' and TABLE_TYPE='BASE TABLE'"^|findstr /n "^"'
 ) do if %%G equ 3 echo %%H

Thanks!

[Squashman]

What fails?
What is the error message?
Are you seeing the variables expanded before the SQL command executes?
Is this set of code sitting inside another block of code where delayed expansion would need to be used?

[SIMMS7400]

Interesting - I must have fat fingered something. It's working now! SOrry, folks!

[SIMMS7400]

Hi Guys -

Changed things up a bit and need to adapt this process to another script.

I currently run the following to do a compare:

Code: Select all

@ECHO OFF

::-- Retrieve total number of tables in Source Database

SQLCMD -S EPM11124 -U EPMAdmin -P Password123! -d HYPDRM -Q "SELECT TABLE_NAME FROM HYPDRM.INFORMATION_SCHEMA.Tables WHERE TABLE_TYPE = 'BASE TABLE'">>T1.txt

::-- Retrieve total number of tables in Destination Database

SQLCMD -S EPM11123TRAIN -U EPMAdmin -P Password123! -d HYP_DRM_TRAIN -Q "SELECT TABLE_NAME FROM HYP_DRM_TRAIN.INFORMATION_SCHEMA.Tables WHERE TABLE_TYPE = 'BASE TABLE'">>T2.txt

del T3.txt 2>nul
for /f "delims=" %%a in (T1.txt) do (
   set "flag="
     for /f "delims=" %%b in (T2.txt) do (
       if "%%a"=="%%b" set flag=1
     )
   if not defined flag >>T3.txt echo %%a
)

Is there anyway to do that without redirecting to a text files?

Thanks!

[aGerman]

Same as last time
for /f "options" %%i in ('command line') do ...

Code: Select all

for /f "delims=" %%a in (
  'SQLCMD -S EPM11124 -U EPMAdmin -P Password123! -d HYPDRM -Q "SELECT TABLE_NAME FROM HYPDRM.INFORMATION_SCHEMA.Tables WHERE TABLE_TYPE = 'BASE TABLE'"'
) do (
   set "flag="
   for /f "delims=" %%b in (
    'SQLCMD -S EPM11123TRAIN -U EPMAdmin -P Password123! -d HYP_DRM_TRAIN -Q "SELECT TABLE_NAME FROM HYP_DRM_TRAIN.INFORMATION_SCHEMA.Tables WHERE TABLE_TYPE = 'BASE TABLE'"'
   ) do (
     if "%%a"=="%%b" set flag=1
   )
   if not defined flag >>T3.txt echo %%a
)

Steffen

[SIMMS7400]

AH - great! Fantastic, thank you Steffen.

I nearly had it - was off a by a few minor things. Thank you again!

Would there be anyway to add logic to identify which environment has the missing file?

For instance, the current out takes the total number of SQL tables from each environments, compares the list, and outputs the differences. Is there a way to identify which environment is the missing the table?

If this additional logic is too complex for this ask, please do no worry about. Its not necessary. Just figured I'd ask, thanks!

[aGerman]

Probably it would be possible. Unfortunately I'm not familiar with SQL statements in order to gather these informations. Also I'm not quite sure what you mean with the "environment" in this context.

Steffen

[SIMMS7400]

HI Steffan -

Sorry about the confusion:

So the following line generates the list of total SQL tables from EPM11124 environment (or "Server"):

Code: Select all

  'SQLCMD -S EPM11124 -U EPMAdmin -P Password123! -Q "SELECT TABLE_NAME FROM HYPDRM.INFORMATION_SCHEMA.Tables WHERE TABLE_TYPE = 'BASE TABLE'"'

As does this line from environment EPM11123TRAIN:

Code: Select all

  'SQLCMD -S EPM11123TRAIN -U EPMAdmin -P Password123! -Q "SELECT TABLE_NAME FROM HYPDRM.INFORMATION_SCHEMA.Tables WHERE TABLE_TYPE = 'BASE TABLE'"'

What just wondering if we could add code that knows which list each command generated, compares both, and identify which table is missing from which output essentially.

Currently the output as it stands today reads like this:

RM_Property_External_Col_Map
RM_Property_External_Param_Map
RM_WF_Task_Commit_Op_Param
RM_WF_Task_Commit_Operation
RM_Blender_Default_Param
RM_Book_Default_Param
RM_Compare_Default_Param
RM_Export_Default_Param
RM_Import_Default_Param
RM_Query_Default_Param
RM_WF_Model_Stage_Validation
RM_Config_Info
RM_Connection_Operation
RM_Connection_Operation_Column
RM_Connection_Operation_Param
(129 rows affected)

but that list doesn't say which environment they are missing from.

No big deal if we can't! I'll look to capture that metric using SQL instead. not a problem.

[foxidrive]

So the following line generates the list of total SQL tables from EPM11124 environment (or "Server"):

As does this line from environment EPM11123TRAIN:

You've given a lot of information, but looking at it from this side of the screen I'm in aGerman's shoes in not knowing what your 'environment' pertains to also.

You have the background of knowing what your SQL files are created for and how the data is being used, and that doesn't come across in your question.

EG: The comparison isn't clear to me of what things are being compared and where an item is missing.

[aGerman]

Currently you only report tables that exist in EPM11124 but are missing in EPM11123TRAIN. Thus, it's quite clear what list you are creating.
You have to exchange the SQL statements in both loops in order to generate a list that reports tables that exist in EPM11123TRAIN but not in EPM11124.

Steffen