Real simple: How to get xp_cmdshell to keep from issuing "Access is denied" and succeed as in the help example.

Question

4.00/5 (1 vote)

See more:

SQL-Server-2008

SQL

/* example from help .. "Access is denied" along with a pesky "NULL" on another line */

DECLARE @cmd sysname, @var sysname
SET @var = 'Hello world'
SET @cmd = 'echo ' + @var + ' > C:\var_out.txt'
EXEC master..xp_cmdshell @cmd

Experimenting with the form:

EXEC xp_cmdshell '@echo SELECT [blahblah] FROM [dbo].[masterblah] WHERE [blahIdx] = 1 >> J:\blahOutput.txt'

Alternatives would be great. But ... this should work (been there, done it before).

Thanks

Posted 29-Dec-12 13:05pm

RedDk

Add a Solution

Comments

Richard MacCutchan 30-Dec-12 3:52am

Don't try to create files in the root of your C: drive.

2 solutions

Solution 1

The chances are that the problem is just the destination of your file: Vista and above require full admin rights to write or modify any file in the root directory of your boot drive. The user under which SQL server is executing will not have that permission. Change the folder: either to a created folder which as "open" access for all users, or an existing data directory which does.

Posted 29-Dec-12 22:06pm

OriginalGriff

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

RedDk · Accepted Answer · 2012-12-31T08:01:00

Solution 2

Found solution.

And I had done this before. Simple change of the service type. From "NETWORK SERVICES" to "LOCAL SYSTEM".

This can be done in Computer Managment console; "Log on As" tab of the Properties.

Posted 31-Dec-12 8:01am

RedDk

Comments

Zoltán Zörgő 31-Dec-12 14:06pm

Not a good one. Read this article: http://thinkingeek.com/2008/11/13/controlling-the-commands-executed-with-xp_cmdshell-on-sql-server-2005/

RedDk 31-Dec-12 14:18pm

... of note, also, is that ssmse execution requires dir /x path notation to be of any use here in this scenario. Aggrevating, yes?