stored proc for file counts (SQL 2000)? 4

[mutley1]

Hi all,

I am having a blonde moment - is there an sp that will list basically list the amount of files (same as a DIR in dos). either if I could specify the drive and get a breakdown of all dirs / sub dirs, or if I have to hardcode a specific folder to count the amount of docs. I thought xp_dirtree might have done it but it just gives the level of depth for a folder.

Cheers,

M.

 

[SQLhell]

Hi there,

use xp_cmdshell 'dir c:'

Any dos command can be executed through xp_cmdshell

 

[mutley1]

Thanks SQLHell,

I know that would work, but I am trying to automate a count of a few folders that have over 2 million docs in each - I just want the count of files and there does not seem to be a switch for DIR to just output the count. If I output a dir to a text file then it will list everything and the text output would be huge. I just want a simple 1 line (or 1 line per folder on a drive) output.

Cheers anyway.

M.

 

[SQLhell]

Ah ok,

Why not make a DTS package or SSIS for it, then use an Active X script like follows:

Dim oFSO
Dim folderName
Dim count
Dim file

SET oFSO = CreateObject("Scripting.FileSystemObject")
folderName = "d:\Files"
SET fol = oFSO.GetFolder(foldername)
SET files = fol.files
count = files.count


Then you can parse the value of the variable 'count' to s stored procedure or something

 

[gmmastros]

[COLOR=blue]Alter[/color] [COLOR=#FF00FF]Function[/color] FileCountForFolder
	(@FolderName [COLOR=blue]VarChar[/color](8000))
Returns [COLOR=blue]Int[/color]
[COLOR=blue]As[/color]
[COLOR=blue]Begin[/color]
	[COLOR=blue]DECLARE[/color]  @objFileSystem [COLOR=blue]int[/color],
			@objFolder [COLOR=blue]int[/color],
			@objFiles [COLOR=blue]int[/color],
			@FileCount [COLOR=blue]int[/color]
	
	[COLOR=blue]EXECUTE[/color] sp_OACreate [COLOR=red]'Scripting.FileSystemObject'[/color], @objFileSystem [COLOR=blue]OUTPUT[/color]
	[COLOR=blue]EXECUTE[/color] sp_OAMethod @objFileSystem, [COLOR=red]'GetFolder'[/color], @objFolder [COLOR=blue]OUTPUT[/color], @FolderName
	[COLOR=blue]EXECUTE[/color] sp_OAMethod @objFolder, [COLOR=red]'Files'[/color], @objFiles [COLOR=blue]OUTPUT[/color]
	[COLOR=blue]EXECUTE[/color] sp_OAGetProperty @objFiles, [COLOR=red]'Count'[/color], @FileCount [COLOR=blue]OUTPUT[/color]
	[COLOR=blue]EXECUTE[/color] sp_OADestroy @objFiles
	[COLOR=blue]EXECUTE[/color] sp_OADestroy @objFolder
	[COLOR=blue]EXECUTE[/color] sp_OADestroy @objFileSystem

	[COLOR=blue]Return[/color] @FileCount

[COLOR=blue]End[/color]

Call it like this...

[tt][blue]Select dbo.FileCountForFolder('C:\')[/blue][/tt]

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[mutley1]

Thanks Guys,

Much appreciated - I will have a play with both ideas when I'm back in tomorrow.

George - If I am trying to do a count on a server that doesn't have SQL installed, will I need a linked server or (assuming the folder I am looking for is shared) would Select dbo.FileCountForFolder('\\SERVER\Sharename') work (i.e. fully qualified path)?

Thanks,

M.

 

[gmmastros]

Probably not, but there are things you can do...

In order to access the file system, you must have permissions to do so. This will probably not work because of permission problems, but you can probably get it to work.

When SQL Server accesses the file system, it uses the domain account used to start the sql server service. To see what that account is...

Click Start->Run
Type Services.msc
scroll down to MSSQLSERVER
Right Click -> Properties
Click 'Log On' tab.

By default, this is set to 'Local System' account, which generally allows you to access files on the local computer, but not other computers. If you really want to use this method, then I suggest...

1. Create a domain account specifically for the SQL Server Service.
2. Make sure you give it a very strong password.
3. If you can, make this account's password never expire.
4. Make this account a local admin on the SQL Server computer.
5. Give this account read/write access to the network share where you want to get the file counts.
6. Don't use this account for anything else. ever.

The problem with using a domain account for the SQL Server service is that the password could be changed which would cause your sql server service to not start up.

This problem leads to those 'hair pulling' problems that can be difficult to track down. However, by creating an account specifically for this purpose, where the password never expires, you can completely control the permissions for this account, and allow it to interact with the file system of other computers.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[RonRepp]

Hi all:

I'll try this again. For some reason my last post did not take. Thanks to George and SQLHell.

I'm trying to perform this as a DTS package in SQL Server 2000. The goal is to iterate through a directory and FTP each file to its necessary FTP site.

Function Main()
Dim oFSO
Dim folderName
Dim count
Dim file


SET oFSO = CreateObject("Scripting.FileSystemObject") 
folderName  = "c:\"
SET fol = oFSO.GetFolder(foldername)
SET files = fol.files 
count = files.count

[b]For Each file in files
	msgbox file
Next[/b]

	Main = DTSTaskExecResult_Success
End Function

Where the message box is at, this is the file that I'd like to FTP. What I need to do is create steps to transfer the filename to the FTP step.

Can anyone steer me in the right direction?

Thanks,





Ron Repp

If gray hair is a sign of wisdom, then I'm a genius.

My newest novel: Wooden Warriors

http://www.repproductions.net