SQLCMD mode in a job step?

  • Is there anyway to get SQLCMD mode to run in a job step?

    I find it a quick method to execute *.sql scripts with :r "C:\path\file.sql"

    Also quick access using !! to execute cmdshell

    I know there are methods using xp_cmdshell just not as elegant as SQLCMD mode in query editor

  • Yes, you can do it.

    Create "Operating system" type job step: "sqlcmd -S SQLSRV -i C:\Tests\TestCMD.sql"

    example of TestCMD.sql file :

    :Setvar BackFldr "C:\TestCMD\"

    !!if exist C:\TestCMD\*.bak (move K:\TestCMD\*.bak K:\TestCMD\ARCHIVE\) ELSE (ECHO No files found)

  • I guess its close, thanks for your input

  • "Yes, you can do it.

    Create "Operating system" type job step: "sqlcmd -S SQLSRV -i C:\Tests\TestCMD.sql"

    example of TestCMD.sql file :

    :Setvar BackFldr "C:\TestCMD\"

    !!if exist C:\TestCMD\*.bak (move K:\TestCMD\*.bak K:\TestCMD\ARCHIVE\) ELSE (ECHO No files found)"

    My job is not working

    don't you need to specify user and password for sqlserver?

  • You can use Windows authentication - you need to add -E parameter (no need to use username and password).

  • you don't need to add the -E parameter if you don't specify password and username. it uses windows authentication by default.

    I got it to run when i changed this :

    "sqlcmd -S SQLSRV -i C:\Tests\TestCMD.sql"

    to this:

    sqlcmd -S SQLSRV -q "C:\Tests\TestCMD.sql"

    I executed a tiny query that respond on the Command promt console inmediatly but my Job starts running and never finishes.. has this happened to you ?

    thanks

    Tom

  • Try -Q instead of -q

  • That was awsome. Thank you so much

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply