Windows Batch & SQLCMD: perform a select with conditions

Luis Diaz
Luis Diaz used Ask the Experts™
on
SQLCMD : perform a select

Hello,

I am looking for a sqlcmd command in order to:
Perform a select count of an specific table
If the count is above 250 exit else continue with the batch.

If you have questions please contact me.

Regards,
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
ste5anSenior Developer

Commented:
Force an error and query the error level returned by SQLCMD. E.g.

SqlCmd -b -E -S (local) -Q "SELECT 1 / COUNT(*) FROM sys.databases"
Echo %ERRORLEVEL%
SqlCmd -b -E -S (local) -Q "SELECT 1 / COUNT(*) FROM sys.databases WHERE 0 = 1"
Echo %ERRORLEVEL%

Open in new window

Luis DiazIT consultant

Author

Commented:
Thank you very much for your help.

Could you please help me to adapt it with the following template.

SET SQLCMDPASSWORD=''
SET SQLCMDSERVER=''
SET SQLCMDUSER=''


sqlcmd -b -d database

REM if count is >VARIABLE
Generate an error log file with datestamp string and with the following message "Warning the count is > 250
Exit
Else continue with the procedure
exit

Open in new window


Remarks:
-Define if possible the count as variable
-Define if possible the select query as variable


Thank you very much for your help.
Jose TorresCertified Database Administrator

Commented:
Here is the script to work from,
Make changes as you need.

echo off

set dbname=%1
set scname=%2
set tbname=%3
set /A "maxcount=%4"


SQLCMD -v DbName =%dbname% SchemaName =%scname% TableName =%tbname% -Q "EXIT(SELECT COUNT(1) FROM $(DbName).$(SchemaName).$(TableName))" > C:\Temp\TestSQLCMD\TestSQL_Check.txt
set /A "errorlevelcheck=%ERRORLEVEL%"

if %errorlevelcheck% GTR %maxcount% goto ErrorRoutine

SQLCMD -v DbName =%dbname% SchemaName =%scname% TableName =%tbname% -Q "SELECT name FROM $(DbName).$(SchemaName).$(TableName)" > C:\Temp\TestSQLCMD\TestSQL_Execute.txt
set /A "errolevelexec=%ERRORLEVEL%"
exit/b

:ErrorRoutine
echo Warning the count is greater than %errorlevelcheck% > C:\Temp\TestSQLCMD\TestSQL_Error.txt

Open in new window

Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Luis DiazIT consultant

Author

Commented:
Thank you.
Variables should be defined with double quotes and the percentage at the end
Ex:
Set dbname = "Toto"%?
Luis DiazIT consultant

Author

Commented:
Hello experts,
Can someone help me with the variable definitions?
Jose TorresCertified Database Administrator

Commented:
I’m not sue I understand your question.

The way the script is written is correct.

Check out the Microsoft documentation for the usage of the set statement.

https://docs.microsoft.com/en-us/windows-server/administration/windows-commands/set_1
Luis DiazIT consultant

Author

Commented:
Could you please provide an example with db name user and password?
ste5anSenior Developer

Commented:
@Jose: The EXIT() trick is cool. Didn't know that..

@Echo Off

Set QUERY=SELECT COUNT(*) FROM sys.databases
Set THRESHOLD=1

SqlCmd -E -S (local) -Q "EXIT(%QUERY%)"
Set Count=%ERRORLEVEL%

If %Count% Gtr %THRESHOLD% ( Echo Count %Count% is greater the then treshold value %THRESHOLD%. )

Open in new window

Senior Developer
Commented:
Learn to read the help:

C:\Temp>sqlcmd /?
Microsoft (R) SQL Server Command Line Tool
Version 11.0.2100.60 NT x64
Copyright (c) 2012 Microsoft. All rights reserved.

usage: Sqlcmd            [-U login id]          [-P password]
  [-S server]            [-H hostname]          [-E trusted connection]
  [-N Encrypt Connection][-C Trust Server Certificate]
  [-d use database name] [-l login timeout]     [-t query timeout]
  [-h headers]           [-s colseparator]      [-w screen width]
  [-a packetsize]        [-e echo input]        [-I Enable Quoted Identifiers]
  [-c cmdend]            [-L[c] list servers[clean output]]
  [-q "cmdline query"]   [-Q "cmdline query" and exit]
  [-m errorlevel]        [-V severitylevel]     [-W remove trailing spaces]
  [-u unicode output]    [-r[0|1] msgs to stderr]
  [-i inputfile]         [-o outputfile]        [-z new password]
  [-f <codepage> | i:<codepage>[,o:<codepage>]] [-Z new password and exit]
  [-k[1|2] remove[replace] control characters]
  [-y variable length type display width]
  [-Y fixed length type display width]
  [-p[1] print statistics[colon format]]
  [-R use client regional setting]
  [-K application intent]
  [-M multisubnet failover]
  [-b On error batch abort]
  [-v var = "value"...]  [-A dedicated admin connection]
  [-X[1] disable commands, startup script, environment variables [and exit]]
  [-x disable variable substitution]
  [-? show syntax summary]

C:\Temp>

Open in new window


SqlCmd -S serverName -d databaseName -U loginId -P password -Q adhocQuery

Open in new window

Luis DiazIT consultant

Author

Commented:
Thank you. How should I proceed for log generation if the count is above threshold?
ste5anSenior Developer
Commented:
For the Windows command line and some other scripting stuff: https://ss64.com/nt/

Pipe the result to a file. Use either > or >>. E.g.

@Echo Off

Set LOG_FILE1=C:\Temp\log1.txt
Set LOG_FILE2=C:\Temp\log2.txt

Echo %DATE% Some Text (1)... > %LOG_FILE1%
Echo %DATE% Some Text (2)... > %LOG_FILE1%
                      
Echo %DATE% Some Text (3)... >> %LOG_FILE2%
Echo %DATE% Some Text (4)... >> %LOG_FILE2%

Echo %DATE% %TIME% Some Text (4)... >> %LOG_FILE2%
Echo %DATE% %TIME% Some Text (5)... >> %LOG_FILE2%

Open in new window

Luis DiazIT consultant

Author

Commented:
Thank you very much.
And last question, log generation with date at the beginning of log file name?

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial