Use batch file to call sqlcmd

[swede]

Env: MS Server 2008, MS SQL Server 2005

During the last couple of months I've discovered the beauty of outputting reports from MS SQL DB by calling sqlcmd from batch files.


This batch file outputs data that correspond to the WHERE clause, meaning that is 7 days old or newer.

Code: [Select]

@echo on

setlocal enabledelayedexpansion


:: How many days back should a date
:: be calculated?

set days=7

:: Calculate date that is x days back
:: for use in SQL Query below.
:: Date format on server: YYYY-MM-DD (2013-02-06)
:: Date format for SQL Query: YYMMDD (130206)

echo wscript.echo ^(Date^(^)- %days%^)>day.vbs
for /f %%a in ('cscript //nologo day.vbs') do set ydate1=%%a
del day.vbs
set ydate1=%ydate1:/=%
set y=%ydate1:~0,4%
set m=%ydate1:~5,2%
set d=%ydate1:~8,2%
set ydate2=%y%%m%%d%
set ydate3=%ydate2:~2,7%

SET SQL="SET NOCOUNT ON;SELECT col1,col2,col3 FROM t1 JOIN t2 ON (t1.col4=t2.col2) WHERE (t2.col1 LIKE '%%Wildcard goes here%%') AND (t1.col5 >= '%ydate3%') ORDER BY col1 ASC"

call sqlcmd -S server\s1 -U sa -P pw -d DB -Q %SQL% -o output.txt -u -n -s "" -w180

endlocal

The problem:
Seems as wildcards inside the SET SQL won't work for me.
Anyone who knows what I'm doing wrong here?

Any help is appreciated! 

[DaveLembke]

http://www.w3schools.com/sql/sql_wildcards.asp
Are you trying to use DOS type wildcard ( * ) in place of what should be SQL wildcard instruction of
  ( % ) ?

[Salmon Trout]

...and are you aware of the need to escape percent signs in batch scripts?

[DaveLembke]

Good point Salmon Trout, you have to use ^% vs ^*

[swede]

Thanks for advice guys. I tried to escape the percent sign with ^% using the following combinations:

^%
^%%
^%^%

but can't get it to work. The query returns nothing and I'm sure it isn't because of the %ydate3% clause which works fine when I only use that one.

Previously I followed Rob van der Woude's advice, which also don't fix it: In batch files, the percent sign may be "escaped" by using a double percent sign ( %% ).
That way, a single percent sign will be used as literal within the command line, instead of being furter interpreted. (source: http://www.robvanderwoude.com/escapechars.php)

[Salmon Trout]

Good point Salmon Trout, you have to use ^% vs ^*

No, to get a batch script to echo % you need to put %%

[swede]

Thanks for your efforts, but the problem remains...with no output from the query.

Any advice that points me in the right direction would be appreciated.   

[Salmon Trout]

Do you get an output if you run the query manually? Have you checked that the batch is producing exactly the same query?

[swede]

Salmon, thanks for taking the time to help out.

Do you get an output if you run the query manually? Have you checked that the batch is producing exactly the same query?

The SQL query runs without problem in MS SQL Mgmt Studio using the wildcard LIKE clause. The result that is returned there is correct.
The DOS prompt produces exactly the query that it should but nothing is returned as output. No visible errors are returned when running the batch script in the DOS prompt.

I've narrowed down the batch script and SQL Query to get rid of noise to make troubleshooting and testing easier, see code below:

Code: [Select]

@echo on
setlocal enabledelayedexpansion

:: There is one supplier with ID 41003898 in database, type is char

set sql="SELECT SupplierID FROM Supplier WHERE SupplierID LIKE '4100389%%'"
CALL sqlcmd -S server/s1 -U sa -P passw -d DB -Q%sql% -o output.txt -u -s "" -w180
output.txt
endlocal
If I change the SQL above and put LIKE '41003898' in the batch script it returns the correct result = one row in the output file.

[Salmon Trout]

Since you have ECHO on, what happens if you put PAUSE at the end of the batch to halt it to give you time to copy the echoed sqlcmd line from the console and paste it into another console window, i.e. run it manually?

I note you have "narrowed down" the batch; that is all very well, and I understand the reason, but you'd be surprised how often the cause of a script failing is somewhere in what has been removed "for clarity". For example it makes me uneasy to see you using a double colon (a broken label) as a comment. It might have been OK in MS-DOS, but in Windows NT family batch scripts this is a frowned-on practice (for example it breaks a script if it is within parentheses such as in a multiline FOR or IF structure.) It is undocumented, unsupported and should be avoided.

[Salmon Trout]

Assuming sqlcmd is the Microsoft sqlcmd.exe, why are you using CALL to invoke it?

[swede]

This was embarrasing...so simple, yet so friggin difficult for me to solve on my own...thanks a bunch Salmon.

Leaving out the CALL cmd solved it.  A bit strange though that only the wildcard LIKE clause broke and nothing else...

 

[Salmon Trout]

CALL is only for running one batch file from another and passing control back to the first when the second one finishes; you can use START for other types of executable but you can use the path and/or name of an exe directly.