Windows Batch & SQLCMD: perform a select with conditions

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,
LVL 1
LD16Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
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

LD16Author 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 AdministratorCommented:
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

Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

LD16Author Commented:
Thank you.
Variables should be defined with double quotes and the percentage at the end
Ex:
Set dbname = "Toto"%?
LD16Author Commented:
Hello experts,
Can someone help me with the variable definitions?
Jose TorresCertified Database AdministratorCommented:
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
LD16Author Commented:
Could you please provide an example with db name user and password?
ste5anSenior DeveloperCommented:
@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

ste5anSenior DeveloperCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
LD16Author Commented:
Thank you. How should I proceed for log generation if the count is above threshold?
ste5anSenior DeveloperCommented:
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

LD16Author Commented:
Thank you very much.
And last question, log generation with date at the beginning of log file name?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Windows Batch

From novice to tech pro — start learning today.