Passing Variable to Xp_CmdShell

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Passing Variable to Xp_CmdShell

Author

Topic

keithc1
Yak Posting Veteran

88 Posts

Posted - 2004-06-10 : 20:49:53

Hi, i'm very new to sql and am trying to list just the Directories from the following XP_CmdShell Extended proc using the following procedure. This gives me Directories but it also gives me rows where string i am evaluating for does not exist in result set. Probably wondering what the use is of this proc and really there isn't much like I said I am new and just trying things out to learn, sorry code is a bit noisy and unorganized. Any Ideas?

CREATE PROC SpXpcmd
@pass varchar(15)
as
SET NOCOUNT on

CREATE TABLE #tmp (result varchar (255))
INSERT INTO #tmp EXEC master.dbo.Xp_Cmdshell @pass
PRINT 'Values inserted into #temp is ' + CAST(@@ROWCOUNT AS varchar(12))
PRINT ''

DECLARE @TableVariable TABLE (
Ident int IDENTITY (1,1) NOT NULL
,result varchar (255) NULL )
INSERT INTO @TableVariable
SELECT result
FROM #tmp

DECLARE @Id int
SET @id = @@IDENTITY

PRINT 'The value of last inserted Identity is ' + convert(varchar, @id)
PRINT ''

-- Print results from XPCmdShell procedure that contain "Dir" string
DECLARE @Sub int
SELECT @sub = @ID
WHILE @Sub >= 1
BEGIN
SELECT @sub = @sub - 1
SELECT ident as 'Identity Value', result as 'Directory' from @Tablevariable where ident = @sub and result like '%Dir%'
ORDER BY ident desc , result
--Print 'The value of @sub is ' + Convert(Varchar, @sub)
END

SELECT * from #tmp

IF Object_id ('tempdb..#tmp') is not null
Drop table dbo.#tmp

EXEC dbo.SpXpcmd 'Dir D:\'

Keithc MCSE MCSA

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-06-10 : 23:41:15

ALTER PROC SpXpcmd
@pass VARCHAR(255)
AS
SET NOCOUNT ON

SELECT @pass = 'dir ' + @pass + ' /A:D /B'
EXEC master..xp_cmdshell @pass

GO
EXEC dbo.SpXpcmd 'c:\'
EXEC dbo.SpXpcmd 'd:\'
EXEC dbo.SpXpcmd 'c:\windows'

??????

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

keithc1
Yak Posting Veteran

88 Posts

Posted - 2004-06-11 : 00:18:57

OMG, Like I said I'm new lol

Thanks Man, thats awesome

Keithc MCSE MCSA

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-06-11 : 00:22:50

no prob :)

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources