0

I'm trying to add a two columns in addition to the results of xp_FileExist. Here's my T-SQL:

DECLARE @path varchar (100)
DECLARE @file varchar (100)
SET @file = '<location>\*.txt'
SET @path = 'dir /B ' + @file
EXEC xp_cmdshell  @path, NO_OUTPUT

CREATE TABLE FILEEXISTS (
        [NAME] VARCHAR (50) NULL)

INSERT into FILEEXISTS exec master..xp_cmdshell @path 

SELECT *
FROM FILEEXISTS

I tried to define columns in the "Create Table" section and add them after the @path but it throws an error every time saying something in the line of too many parameters. Any pointers?

1

2 Answers 2

1

You need to add code to your question that doesn't work (that causes the error you are seeing).

Having said that, you probably want to modify the code you are using to something like:

DECLARE @path varchar (100);
DECLARE @file varchar (100);
SET @file = 'C:\temp\*.*';
SET @path = 'dir /B ' + @file;

DECLARE @FILEEXISTS TABLE(
        [NAME] VARCHAR (255) NULL
);

INSERT into @FILEEXISTS 
exec master..xp_cmdshell @path ;

SELECT *
FROM @FILEEXISTS;

DECLARE @table TABLE(
        [NAME] VARCHAR (255) NULL
        , somecol VARCHAR(255) NULL
);

INSERT INTO @table(NAME)
SELECT name
FROM @FILEEXISTS;

UPDATE @table SET somecol = 'test' /* add a 'WHERE' clause if necessary */;

SELECT *
FROM @table;

This uses a table-variable to store the results, and doesn't execute the unnecessary xp_cmdshell right after the set @path=... line.

The @table declaration (and other code) I added to the end allows you grab the results into @fileexists, then insert those results into @table and add the other columns as necessary. xp_cmdshell returns a single column of results, one line for each line output by the command it runs.

1
  • 1
    Thank you so much! You're a lifesaver. This is exactly what I needed. :)
    – topcatdc
    Aug 26, 2014 at 13:51
0

Instead of using xp_cmdshell instead use xp_fileexist. It runs a variable to a parameter that you pass it, then you don't need to worry about adding columns to a table. Just drop the values that you want into the table and off you go.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.