how to make this batch file work using xp_cmdshell?

Question

Post reply

how to make this batch file work using xp_cmdshell?

curious_sqldba

SSC-Dedicated

Points: 36494
More actions
February 15, 2012 at 2:33 pm

#253103

If i copy and paste this "D:\SQL2005_CU\SQLServer2005-KB2507769-x64-ENU.exe" /allinstances /quiet in dos and hit enter , i am able to deploy the CU on sql 2005. My goal is to make this run through xp_cmdshell. I tried to put the same command in a .bat file and run from xp_cmdshell, i dont see the CU is being installed. Anyhelp is appreciated.

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

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

most likely a permissions issue.

to run a cumulative update, you need to be an administrator ont he amchine...

xp_cmdshell does not run as the logged in user...it runs under whatever the service account that it is using...

you'd be better off running that via powershell than xp_cmdshell.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

curious_sqldba SSC-Dedicated Points: 36494 More actions · Answer 2

Lowell (2/15/2012)
most likely a permissions issue.
to run a cumulative update, you need to be an administrator ont he amchine...
xp_cmdshell does not run as the logged in user...it runs under whatever the service account that it is using...
you'd be better off running that via powershell than xp_cmdshell.

hmm....how do i run using power shell?

Jeff Moden SSC Guru Points: 1004441 More actions · Answer 3

Ummmm.... ok. I'm confused. You have the /QUIET switch set... why would you expect to see anything?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

curious_sqldba SSC-Dedicated Points: 36494 More actions · Answer 4

Jeff Moden (2/15/2012)
Ummmm.... ok. I'm confused. You have the /QUIET switch set... why would you expect to see anything?

Oki should had provid more details on this.my goal here is to install CU across set of servers in parallel.I thought I realized I wont be able to use xpcmdshell because the svc account is not admin on the server.so I wanted to see if I can deploy these patches in parallel for given set of servers using powershell.

curious_sqldba SSC-Dedicated Points: 36494 More actions · Answer 5

Ok i got this working,hope this would help someone.

For SP's i haven't tried this because WSUS has the capability to deploy SP's.

For CU's, you can make use of PowerShell 2.0. You will need to do the following before you make use of it:

i) Have powershell 2.0 installed on local

ii) Enable windowsrm on target servers.

iii) Place .bat files on the same location of each target server

Invoke-Command -computer yourtargetservername,servername2,.. -scriptBlock {D:\SP_CU\SQL2005_CU.bat} -AsJob

SQL2005_CU.bat:Mentioned below is the command need to be placed in batch file

"D:\SQL2005_CU\SQLServer2005-KB2507769-x64-ENU.exe" /allinstances /quiet

i have tried this multiple times on sql 2005, this might work on sql 2008/2008R2.

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 6

sqldba_newbie (2/19/2012)
Ok i got this working,hope this would help someone.
For SP's i haven't tried this because WSUS has the capability to deploy SP's.
For CU's, you can make use of PowerShell 2.0. You will need to do the following before you make use of it:
i) Have powershell 2.0 installed on local
ii) Enable windowsrm on target servers.
iii) Place .bat files on the same location of each target server
Invoke-Command -computer yourtargetservername,servername2,.. -scriptBlock {D:\SP_CU\SQL2005_CU.bat} -AsJob
SQL2005_CU.bat:Mentioned below is the command need to be placed in batch file
"D:\SQL2005_CU\SQLServer2005-KB2507769-x64-ENU.exe" /allinstances /quiet
i have tried this multiple times on sql 2005, this might work on sql 2008/2008R2.

Very nice solution! Thanks for posting the steps.

Even if permissions were however they needed to be, xp_CmdShell may not have worked anyway. You passed /allinstances so consider what would have happened if the CU needed to restart the instance what would happen to the SQL session running xp_CmdShell :pinch:

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

curious_sqldba SSC-Dedicated Points: 36494 More actions · Answer 7

opc.three (3/13/2012)
sqldba_newbie (2/19/2012)
Ok i got this working,hope this would help someone.
For SP's i haven't tried this because WSUS has the capability to deploy SP's.
For CU's, you can make use of PowerShell 2.0. You will need to do the following before you make use of it:
i) Have powershell 2.0 installed on local
ii) Enable windowsrm on target servers.
iii) Place .bat files on the same location of each target server
Invoke-Command -computer yourtargetservername,servername2,.. -scriptBlock {D:\SP_CU\SQL2005_CU.bat} -AsJob
SQL2005_CU.bat:Mentioned below is the command need to be placed in batch file
"D:\SQL2005_CU\SQLServer2005-KB2507769-x64-ENU.exe" /allinstances /quiet
i have tried this multiple times on sql 2005, this might work on sql 2008/2008R2.
Very nice solution! Thanks for posting the steps.
Even if permissions were however they needed to be, xp_CmdShell may not have worked anyway. You passed /allinstances so consider what would have happened if the CU needed to restart the instance what would happen to the SQL session running xp_CmdShell :pinch:

Thanks..In my batch file i have set to restart the box itself after applying CU.

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 8

sqldba_newbie (3/14/2012)
Thanks..In my batch file i have set to restart the box itself after applying CU.

That's a common practice. It is not usually necessary however I rarely oppose it when it is requested. It is driven into most manager's DNA to restart everything whenever you make a change, if for nothing else than it creates a new baseline.

My point was that some CU installers will themselves restart the instance during the normal installation process. Consider what that would do if you were running the CU installer via an xp_CmdShell call on the instance that you were updating.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato