Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQLCMD mode in a job step? Expand / Collapse
Author
Message
Posted Monday, May 10, 2010 9:06 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Sunday, April 13, 2014 7:26 PM
Points: 58, Visits: 445
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
Posted Tuesday, May 11, 2010 8:28 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, January 14, 2014 5:30 PM
Points: 1,264, Visits: 721
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
Posted Tuesday, May 11, 2010 5:04 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Sunday, April 13, 2014 7:26 PM
Points: 58, Visits: 445
I guess its close, thanks for your input
Post #920087
Posted Friday, January 25, 2013 1:20 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Monday, January 28, 2013 8:10 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, January 14, 2014 5:30 PM
Points: 1,264, Visits: 721
You can use Windows authentication - you need to add -E parameter (no need to use username and password).
Post #1412730
Posted Tuesday, January 29, 2013 6:08 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Tuesday, January 29, 2013 6:50 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, January 14, 2014 5:30 PM
Points: 1,264, Visits: 721
Try -Q instead of -q
Post #1413017
Posted Tuesday, January 29, 2013 8:00 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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 »

Add to briefcase

Permissions Expand / Collapse