September 9, 2010 at 11:46 am
I am trying to run OSQl from a batch job. Is there a way to use a date comparison?
Code:
@echo off
set table=HOLIDAY_SCHEDULE
set var=%date%
osql -Q "exit(SELECT '1' FROM %table% where convert(varchar,HOLIDAY,101) = %var%)"
The query works fine in management studio but I get an error ('Conversion failed when converting the varchar value '09/09/2010' to data type int') when running batch job.
Thanks in advance.
September 9, 2010 at 12:07 pm
Add the set commands inside of the -Q parameter. You might want to do it as a Script.sql file instead. Put your query in a file and then use the -i parameter for the osql command.
osql -i MyScript.sql
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
September 9, 2010 at 12:10 pm
Can you explain what you mean by putting it in the Q statement?
Maybe I did not explain everything.....if I remove the "Where clause" the script works. It is the where clause that is causing the issue.
September 9, 2010 at 4:19 pm
I don't mess much with CONVERT statements in T-SQL but should you not be specifiying the length of VARCHAR?
WHERE CONVERT(varchar(10),HOLIDY,101) = %var%
If your complete query works when you are in SSMS, save the query as a .sql file and then just call that file with your OSQL command in the batch file.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply