performing a DIR command from a sproc without using XP_CmdShell

Question

performing a DIR command from a sproc without using XP_CmdShell

winston Smith

SSCoach

Points: 19500
More actions
June 16, 2008 at 1:14 pm

#381141

i need to find the contents of a directory.
generally i just do the following:
CREATE TABLE #DirTable
(
ReturnedRow varchar(255)
)
DECLARE @Command varchar(255)
set @command = 'dir ' + '\\someDirectory\'
INSERT INTO #DIRTABLE
EXEC master..xp_cmdshell @Command
Unfortunately on the server the XP_CmdShell is disabled for security reasons.
How can i find out if a file exists and delete it without using xp_CmdShell?
I need to be able to delete a server Trace file and recreate it, as when the server is reset, the trace no longer exists in sql server, but the trace file still exists on the disk drive, and when the startup sproc executes to create the server trace it errors out as the file already exists on the specified location.

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

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

Steve Jones - SSC Editor SSC Guru Points: 736193 More actions · Answer 1

I'm not sure how you could do all that. Perhaps kick off an SSIS package or a job that runs a VBS script (or Powershell) to do this?

winston Smith SSCoach Points: 19500 More actions · Answer 2

hmmm. thats an idea, but is overkill, for something that was supposed to be very simple.

might just send an email to an alias whenever the server is restarted to notify the team to recreate the trace.

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 3

You can specify a Job Agent job to run when the Agent starts. If your agent starts when the server starts, you could just have a job that deletes the file.

PW-201837 SSC-Insane Points: 20805 More actions · Answer 4

You could use sp_OAcreate and related stored procs to create an instance of the Scripting.FileSystemObject COM component and call the FileExists() and DeleteFile() methods.

winston Smith SSCoach Points: 19500 More actions · Answer 5

i would like to try the sp_OACreate method of doing this. Ive tried to look it up online ( spent the last 45 mins) but all i can find out is that it creates ole object. i cannot find a list of the ole objects. i know i need to use the scripting.filesystemobject ole object but as to what that is or as to the other ole objects, i cant find anything. can anyone give me a basic overview of this area?

winston Smith SSCoach Points: 19500 More actions · Answer 6

actually, instead of calling old OLE commands, is there a way of calling .NET commands instead of using Scripting.filesystemobject?

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 7

I suppose you could have someone write a CLR for this particular functionality.

--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)

PW-201837 SSC-Insane Points: 20805 More actions · Answer 8

Yep, a CLR stored proc, with access to all the .Net System.IO namespace classes would do the trick nicely.

Of course, any organisation that has xp_cmdshell locked down, will likely also have similar security policy applied to CLR stored procs. And to the sp_OA* stored procs too, that I posted a few days ago.

That is the crux of the problem. xp_cmdshell is usually locked down for a very good reason. Any alternate solution needs to satisfy whatever person or group that has determined that xp_cmdshell is not acceptable.

It's really not a technology problem here, it's a security & policy issue.

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

I agree... our DBA's have xp_CmdShell, sp_OA*, and a whole bunch of other "ad hoc" access turned off. But, they do allow it through stored procedures that are executed by a "proxy login".

--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)