Stored Procedure to count files in a directory 1

[smitan]

Hi, at the moment i'm using a loop in Access, which counts the files in a directory by using the DIR-statement.
This works fine, only time consuming.

If Nz(Dir(strPKPath & "*pk.doc")) <> "" Then
   i = 1
   Do Until Nz(Dir) = ""
      i = i + 1
   Loop
End If

I wonder if I could use a Stored Procedure in SQL-server that would do the job for me faster?
If possible it would be nice to supply a mask like '*pk.doc', if not it's also ok.
It's just a matter of taking ½ the total number of files, as each '*pk.doc' file also has a '*pk.jpg'.

Thanks in advance.
Smitan

 

[Jamfool]

You could use the below to grab the dir results to table.

drop table #t
create table #t(file_list varchar(8000))
insert into #t
EXEC master..xp_cmdshell 'dir c:\*.log /b ' 
delete from #t where (file_list not like '%.log' or file_list is NULL)
select * from #t
select count(*) from #t

 

[smitan]

Thanks, this gives a good result.

 

[smitan]

Hi Jamfool,

Still experimenting with SP.
Could you please have a look at this one?

Select @tbl='tblTemp' + @Param1
Select @SQL = 'Create Table ' + @tbl + '(fld nvarchar(1000)'
Execute @SQL

Why do I get the message: "Could not find SP 'Create Table tblTempxxx(fld nvarchar(1000)'

(xxx is input)

How do I run a statements for building and dropping tables etc.?

Thanks very much.
Smitan

 

[Jamfool]

declare @somevar as varchar(255)
select @somevar = 'select * from information_schema.tables'
exec @somevar

Will give:

Server: Msg 2812, Level 16, State 62, Line 5
Could not find stored procedure 'select * from systables'.

But if you add brackets around the sql you wish to execute it will work:

declare @somevar as varchar(255)
select @somevar = 'select * from information_schema.tables'
exec (@somevar)

Syntax is: Drop Table [TableName]

More detail can be found in BOL.

 

[smitan]

Thanks a lot again.