One of the clients needed its users to remotely execute a SQL
job and as usual I picked this up hoping for a quick brownie point.
Sure enough there was a catch and there was something to
learn.
Executing the job through SQLCMD was a no-brainer but
getting it to execute on the remote machine was bit of challenge.
On the coding Front
1 1.)
The bat file included the following code
SQLCMD
-S "[ServerName]" -E -Q "EXEC MSDB.dbo.sp_start_job
@Job_Name = ''[JobName]"
2 2.)
The Individual users were given minimum
permissions to execute the SQL job
Ex.
use msdb
EXECUTE
sp_addrolemember
@rolename
= 'SQLAgentOperatorRole',
@membername
= 'Domain\UserLogin'
At the client machine
This
took a fair bit of time till our sysadmin got me an empty VM machine. Thanks Michael G
I’m
just going to copy the exact instructions that I copied to OneNote and passed on to the client
1 1.)
Down Load the following “.EXE’s” from http://www.microsoft.com/en-us/download/confirmation.aspx?id=16978
-
Command Line Utilities à SqlCmdLnUtils.msi
-
Microsoft SQL Server Native Client à sqlncli.msi
-
Windows installer 4.5 if it doesn’t exists
already àWindows6.0-KB942288-v2-x64.msu
How can I verify if windows
installer is available
1. Click on 'Start'ping
2. Then click 'Run'
3. In the box type
" msiexec " then click OK.
4. Then another box will
come up and at the top of the box the first line should read which version of
Windows Installer you have installed on your PC.
2 2.)
Other instructions
-
Execute all the “.EXE” that have been listed
above
-
Locate the path where the sqlcmd.exe is located; generally it should be available at “C:\Program
Files\Microsoft SQL Server\100\Tools\Binn”
-
Add the above path to the “path” global variable
o Make
sure to back-up the existing variable value just in case of a muck up.
Editing the path environment
variable
Past the path(at the end of the
existing) where SQLCMD was located on the local machine
Ex; ;C:\Program Files\Microsoft SQL Server\100\Tools\Binn\
Please
remember to copy the ;
at the beginning and \ at the end
Hope this save someone’s time, I spent
more than what I should have on this and don’t want anyone to go through this
pain.
Comments
Post a Comment