Trying to List Folder File Names (using DIR) 3

[JohnBates]

hi all,

This is easier if you use DTS/VBscript but I dont have that option this time.
I'm trying to automate a database restore. My problem at this point is just getting a list of the .bak files that exist in the specified path.

I'm using the DIR command, but getting a syntax error. (see last sentence)

Thanks for any tips. John

Declare @FilePath varchar(100)
Declare @FileName varchar(100)
DECLARE @SQLstmt varchar(2000), @RestoreDB varchar(200), @Cmd varchar(2000), @Return int

Set @FilePath = 'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\'
Set @FileName = 'webapp%.bak'

SET @Cmd = 'DIR ' + '''' + @FilePath + ''''

CREATE TABLE #BACKUPS (DBname varchar(100))

INSERT #BACKUPS EXECUTE @Return = master..xp_cmdshell @Cmd

.... when I run the above code in Q-A, it gives this error:
"The filename, directory name, or volume label syntax is incorrect"

 

[gmmastros]

use this line instead.


SET @Cmd = 'DIR ' + '"' + @FilePath + '" /b'

Notice the double quotes. I added the /b switch. Try running it both ways (with and without the /b).

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[mrdenny]

Look into using the xp_dirtree extended stored procedure instead.

exec xp_dirtree 'c:\Program Files\Microsoft SQL Server\MSSQL\Backup', 0, 1

The 0 says to read down through the directory tree as far as it goes (includes any sub-folders). The 1 tells it to include files as well as folders.

I'm a stickler for not using xp_cmdshell if I don't have to.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[JohnBates]

gmmastros - Your suggestion works well, when I include the /b switch. Without /b, it didn't retrieve any file names.

mrdenny, I like the idea of using xp_dirtree. I haven't moved this to production yet so I will experiment with that
undocumented system proc.
And I agree with you on the use of xp_cmdshell. I'm glad I may have another option

Thanks very much to both of you for helping me!

John

 

[mrdenny]

No problem.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[JohnBates]

mrdenny,

This works perfectly when I run it in Q-A:
exec xp_dirtree 'c:\Program Files\Microsoft SQL Server\MSSQL\Backup', 0, 1


But, to get the bak file names into my temp table I do this:

INSERT INTO #BACKUPS exec master..xp_dirtree 'c:\Program Files\Microsoft SQL Server\MSSQL\Backup', 0, 1

Server: Msg 213, Level 16, State 7, Procedure xp_dirtree, Line 8
Insert Error: Column name or number of supplied values does not match table definition.

What am I doing wrong?
Thanks, John

 

[ca8msm]

Are you sure you've created the #BACKUPS table correctly? This seems to work for me:

create table #BACKUPS
(
subdirectory varchar(255),
depth int,
isfile int
)
INSERT #BACKUPS exec xp_dirtree 'c:\Program Files\Microsoft SQL Server\MSSQL\Backup', 0, 1

SELECT * FROM #BACKUPS

____________________________________________________________
Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]

Need help finding an answer? Try the Search Facility or read FAQ222-2244 on how to get better results.

 

[mrdenny]

If done the way that ca8msm shows it should work fine. I've used it many times in this method.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[JohnBates]

Thanks for catching my oversight, ca8msm. It works now.

Nice web site too. I'm trying to learn .Net. Coming from a VB6 and classic ASP background - you have developed some helpful code I'll be looking into !

From the other side of the pond, Thanks. John