xp_CmdShell - Giving Back - Sproc ShortPathName

Question

Post reply

xp_CmdShell - Giving Back - Sproc ShortPathName

Peter E. Kierstead

SSCarpal Tunnel

Points: 4114
More actions
September 13, 2005 at 5:47 pm

#165945

Have seen a lot of queries regarding xp_CmdShell and Command Prompt tricks... Here is one of mine...
Ok, I know there are a lot of good reasons for not using xp_CmdShell, but, if your game, try this!
Pass in a long path name and yuo'll get:
1) a return code indicating if the path existed or not (0=No, 1=Yes)
2) an Output variable that is the short path name corresponding to the input long path
Use Common
If Object_Id('ShortPathName') is not Null Drop Procedure ShortPathName
Set Quoted_Identifier On
Set ANSI_Nulls On
Set ArithAbort On
Go
Create Procedure ShortPathName
(
@LongPath VarChar(8000),
   @ShortPath VarChar(8000) Output
)
As
Set NoCount On
Declare
   @rc Int,
   @Cmd VarChar(8000)
-- Pre-format the input path.
Set @LongPath=LTrim(RTrim(IsNull(@LongPath,'')))
-- Return gracefully if blank path.
If @LongPath='' Begin
   Select
      @rc=0,
      @ShortPath=''
   Goto Done
End
Select
   -- Make sure the path is enclosed in double-quotes.
   @LongPath=Case Left(@LongPath,1) When '"' Then '' Else '"' End+@LongPath+
      Case Right(@LongPath,1)When '"' Then '' Else '"' End,
   -- Build tricky "DOS" command to coax out short-path name.
   @Cmd='For %I In ('+@LongPath+') Do @Echo %~dpnxsI'
-- Set the return code to indicate if the path actually exists (0=No, 1=Yes).
Set @rc=Common.dbo.PathExists(@LongPath)
-- Create a temp table for xp_CmdShell results.
If Object_Id('TempDB.dbo.#Exec Common.dbo.ShortPathName') is not Null Drop Table [#Exec Common.dbo.ShortPathName]
Create Table [#Exec Common.dbo.ShortPathName](CmdLine VarChar(1024))
-- Pick up the short-path name.
Insert [#Exec Common.dbo.ShortPathName]
Exec master.dbo.xp_CmdShell @Cmd
Select Top 1 @ShortPath=CmdLine from dbo.[#Exec Common.dbo.ShortPathName] (nolock)
Return @rc
Done:

PeteK
I have CDO. It's like OCD but all the letters are in alphabetical order... as they should be.
September 16, 2005 at 8:00 am

This was removed by the editor as SPAM

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

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

Regan Galbraith SSCertifiable Points: 5227 More actions · Answer 1

Hmm - well, here's a response

I notice that you are using:

Set @rc=Common.dbo.PathExists(@LongPath)

So you should probably include the code for that SP. Are you makign use of xp_fileexist in it?

CiaO 4 NoW

Peter E. Kierstead SSCarpal Tunnel Points: 4114 More actions · Answer 2

Uuups, sorry, its another "DOS" hack sproc in our common library system... Here's the code:

Use Common

If Object_Id('PathExists') is not Null Drop Function PathExists

Go

Create Function PathExists

(

@Path VarChar(8000)

)

Returns Int

As Begin

Declare

@rc Int,

@Cmd VarChar(8000)

-- Pre-format the input path.

Set @Path=LTrim(RTrim(IsNull(@Path,'')))

-- Return gracefully if blank path.

If @Path='' Begin

Set @rc=0

Goto Done

End

Select

-- Make sure the path is enclosed in double-quotes.

@Path=Case Left(@Path,1) When '"' Then '' Else '"' End+@Path+

Case Right(@Path,1)When '"' Then '' Else '"' End,

-- Build tricky "DOS" command to determine if path exists.

@Cmd='If Exist '+@Path+' (Exit 1) Else (Exit 0)'

-- Set the return code to indicate if the path actually exists (0=No, 1=Yes).

Exec @rc=master.dbo.xp_CmdShell @Cmd,No_Output

Done:

Return @rc

End

PeteK
I have CDO. It's like OCD but all the letters are in alphabetical order... as they should be.

Regan Galbraith SSCertifiable Points: 5227 More actions · Answer 3

An alternaitve for you, using the undocumented xp_FileExist stored procedure:

--EXAMPLE

set nocount on

set quoted_identifier off

create table #direxists

(FileExists int,

FileIsDir int,

ParentDirExists int)

declare @movepathdata varchar(255)

set @movepathdata = 'c:\hello.txt'

insert into #direxists exec master..xp_fileexist @movepathdata

select * from #direxists

if not exists(select 'Dir Exists' from #direxists where fileExists = 1 and FileIsDir = 0 and ParentDirExists = 1)

Print 'File Does Not Exist'

else

Print 'File Does Exist'

***

Play around with the temp table, you can use this form existance of files and directories ( I use it for confirming existance of directories in an automated restore procedure).

HTH

Dave-3000 SSCertifiable Points: 6186 More actions · Answer 4

Why not spruce it up a bit and post it in the scripts section?

There is no "i" in team, but idiot has two.

Peter E. Kierstead SSCarpal Tunnel Points: 4114 More actions · Answer 5

Ya, I use to use xp_FileExists, but, I figured there's less a chance of the Command Prompt stuff going away than xp_FileExists... but then, who ever would have thought that the 8-track wouldn't last...

PeteK
I have CDO. It's like OCD but all the letters are in alphabetical order... as they should be.

Regan Galbraith SSCertifiable Points: 5227 More actions · Answer 6

Fair enough. I'm trying to use xp_cmdshell less, since it typically ends up being ugly work arounds.

You may be right in terms of xp_cmdshell not going - I sure there millions of homegrown dba scripts and processes that would die if they removed it

Peter E. Kierstead SSCarpal Tunnel Points: 4114 More actions · Answer 7

I use to write Extended Stored Procedures to handle the stuff that SQL wasn't "comfortable" with but XSProc are difficult to manage (versioning, interface requirements, environemtal requirements, testing, documentation... ad nauseam ) If I can find a more "natural" way to accomplish what are unnatural acts for SQL, well, then, I tend to gravitate to them...

PeteK
I have CDO. It's like OCD but all the letters are in alphabetical order... as they should be.