xp_cmdshell Lunacy

Question

Post reply

xp_cmdshell Lunacy

PurpleLady

SSCertifiable

Points: 7698
More actions
February 16, 2012 at 8:17 am

#256874

I have a job that has been running fine up until last Friday (Feb. 10th). Starting on Friday, the job fails everytime I try to decompress a downloaded file using xp_cmdshell and UNRAR. I'm running SQL 2008 sp3 (installed Friday afternoon - job 1st failed Friday morning) on Windows 2008 R2 sp1. I use my DBA account and execute the script - works fine. I login as the SQL Agent account and execute the script - works fine. It's only when it's a scheduled job that it fails. Supposedly nothing changed - except sp3 but the job was failing before that was installed.

Any ideas?

Viewing 15 posts - 1 through 15 (of 17 total)

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

tp 25072 SSC Veteran Points: 232 More actions · Answer 1

tp 25072

SSC Veteran

Points: 232

February 16, 2012 at 8:24 am

#1447927

Have you tried going to Scheduled Tasks , Properties of this task, and setting / changing the <RunAs> credentials?

cw.izatt Right there with Babe Points: 776 More actions · Answer 2

cw.izatt

Right there with Babe

Points: 776

February 16, 2012 at 8:42 am

#1447948

I agree with above, why handle all of that inside SQL?

Never stop learning or you will be left behind.

Jared SSC Guru Points: 61793 More actions · Answer 3

Jared

SSC Guru

Points: 61793

February 16, 2012 at 8:46 am

#1447953

cw.izatt (2/16/2012)

I agree with above, why handle all of that inside SQL?

Why not? SQL is perfectly capable of handling it and if it is a dedicated SQL Server it is probably the best tool to use.

Jared
CE - Microsoft

cw.izatt Right there with Babe Points: 776 More actions · Answer 4

Why not? SQL is perfectly capable of handling it and if it is a dedicated SQL Server it is probably the best tool to use.

After thinking about it either is valid, but for me i would rather handle that with powershell and push it into the DB instead of the other way around, but that might be the sys admin talking in me.

Never stop learning or you will be left behind.

Jared SSC Guru Points: 61793 More actions · Answer 5

cw.izatt (2/16/2012)

Why not? SQL is perfectly capable of handling it and if it is a dedicated SQL Server it is probably the best tool to use.

After thinking about it either is valid, but for me i would rather handle that with powershell and push it into the DB instead of the other way around, but that might be the sys admin talking in me.

🙂 Skillsets are also important.

Jared
CE - Microsoft

cw.izatt Right there with Babe Points: 776 More actions · Answer 6

cw.izatt

Right there with Babe

Points: 776

February 16, 2012 at 9:55 am

#1448016

First question, What account does it run under when scheduled? any ideas on what piece is failing? My thought is that it is a permission issue and that is why when you run it as yourself it works might be wrong though.

Never stop learning or you will be left behind.

andersg98 SSCertifiable Points: 5168 More actions · Answer 7

andersg98

SSCertifiable

Points: 5168

February 16, 2012 at 12:19 pm

#1448085

When run manually it uses your permissions, when run on schedule by SQL agent it uses the agent accounts permissions. Make sure the domain account that runs your SQL agent has full access to the path where the file is located.

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 8

SQLKnowItAll (2/16/2012)

cw.izatt (2/16/2012)

I agree with above, why handle all of that inside SQL?

Why not? SQL is perfectly capable of handling it and if it is a dedicated SQL Server it is probably the best tool to use.

Ok... I'm starting to like your attitude even more, Jared. 🙂

--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)
Intro to Tally Tables and Functions

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 9

cw.izatt (2/16/2012)

Why not? SQL is perfectly capable of handling it and if it is a dedicated SQL Server it is probably the best tool to use.

After thinking about it either is valid, but for me i would rather handle that with powershell and push it into the DB instead of the other way around, but that might be the sys admin talking in me.

Understood. Let me ask... how are you scheduling the PS job to run?

--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)
Intro to Tally Tables and Functions

Jared SSC Guru Points: 61793 More actions · Answer 10

Jeff Moden (2/16/2012)

SQLKnowItAll (2/16/2012)

cw.izatt (2/16/2012)

I agree with above, why handle all of that inside SQL?

Why not? SQL is perfectly capable of handling it and if it is a dedicated SQL Server it is probably the best tool to use.

Ok... I'm starting to like your attitude even more, Jared. 🙂

So... You want to be my mentor? lol 😉

Jared
CE - Microsoft

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 11

SQLKnowItAll (2/16/2012)

Jeff Moden (2/16/2012)

SQLKnowItAll (2/16/2012)

cw.izatt (2/16/2012)

I agree with above, why handle all of that inside SQL?

Why not? SQL is perfectly capable of handling it and if it is a dedicated SQL Server it is probably the best tool to use.

Ok... I'm starting to like your attitude even more, Jared. 🙂

So... You want to be my mentor? lol 😉

Looks like I don't need to. You're already doing the same things that I might.

--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)
Intro to Tally Tables and Functions

cw.izatt Right there with Babe Points: 776 More actions · Answer 12

Jeff Moden (2/16/2012)

cw.izatt (2/16/2012)

Why not? SQL is perfectly capable of handling it and if it is a dedicated SQL Server it is probably the best tool to use.

After thinking about it either is valid, but for me i would rather handle that with powershell and push it into the DB instead of the other way around, but that might be the sys admin talking in me.

Understood. Let me ask... how are you scheduling the PS job to run?

Just like this.

http://www.sysadminsblog.com/microsoft/scheduling-powershell-scripts/

Never stop learning or you will be left behind.

Jared SSC Guru Points: 61793 More actions · Answer 13

cw.izatt (2/16/2012)

Jeff Moden (2/16/2012)

cw.izatt (2/16/2012)

Why not? SQL is perfectly capable of handling it and if it is a dedicated SQL Server it is probably the best tool to use.

After thinking about it either is valid, but for me i would rather handle that with powershell and push it into the DB instead of the other way around, but that might be the sys admin talking in me.

Understood. Let me ask... how are you scheduling the PS job to run?

Just like this.

http://www.sysadminsblog.com/microsoft/scheduling-powershell-scripts/

So why? What if the database is down... Is that check in your PS script? What if the database is offline? What if there are errors in the data? I'm not saying that PowerShell is bad, I'm just curious why you would rather use it. Familiarity? That's why I use SQL, but in this case I would also rather the source control how and when it gets its data, that way the process is more controlled; i.e. If SQL Server is down, it cannot call the data to load so no issues.

Jared
CE - Microsoft

cw.izatt Right there with Babe Points: 776 More actions · Answer 14

Most likely i would have Powershell control it and call a SP in SQL kinda of the best of both worlds. My job is more sys admin than DBA and we run more scripts on the server level than SQL level. Some of this has to do with the lack of our third party vendors willingness to work with me very frustrating by the way. I can see where you are coming from if most of your day is SQL and TSQL not saying your way is bad either. In some cases your way might be better i guess it all depends on what you need it to do and how you like to handle your scripting.

Never stop learning or you will be left behind.