SQLCMD mode in a job step?

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » SQLCMD mode in a job step?

SQLCMD mode in a job step?

Rate Topic

Display Mode

Topic Options

Author

Message

glugingboulder

Posted Monday, May 10, 2010 9:06 PM

Valued Member

Group: General Forum Members
Last Login: 2 days ago @ 10:00 PM
Points: 55, Visits: 419

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

Post #919407

magasvs

Posted Tuesday, May 11, 2010 8:28 AM

Ten Centuries

Group: General Forum Members
Last Login: Monday, May 20, 2013 8:54 AM
Points: 1,259, Visits: 700

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)

Post #919730

glugingboulder

Posted Tuesday, May 11, 2010 5:04 PM

Valued Member

Group: General Forum Members
Last Login: 2 days ago @ 10:00 PM
Points: 55, Visits: 419

I guess its close, thanks for your input

Post #920087

tomiapi

Posted Friday, January 25, 2013 1:20 PM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, February 21, 2013 11:04 AM
Points: 3, Visits: 6

"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?

Post #1411916

magasvs

Posted Monday, January 28, 2013 8:10 PM

Ten Centuries

Group: General Forum Members
Last Login: Monday, May 20, 2013 8:54 AM
Points: 1,259, Visits: 700

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

Post #1412730

tomiapi

Posted Tuesday, January 29, 2013 6:08 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, February 21, 2013 11:04 AM
Points: 3, Visits: 6

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

Post #1412981

magasvs

Posted Tuesday, January 29, 2013 6:50 AM

Ten Centuries

Group: General Forum Members
Last Login: Monday, May 20, 2013 8:54 AM
Points: 1,259, Visits: 700

Try -Q instead of -q

Post #1413017

tomiapi

Posted Tuesday, January 29, 2013 8:00 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, February 21, 2013 11:04 AM
Points: 3, Visits: 6

That was awsome. Thank you so much

Post #1413063

« Prev Topic | Next Topic »

Permissions