Using xp_cmdshell to count files in a folder on different server

[sonname]

Hi,
Is there a way to use the xp_cmdshell in SQL Server to count the number of files in a folder on a different server? I have a process that watches a folder on a different server and sometimes it fails and the files keep accumulating. I would like to create a job in SQL Server where I could use xp_cmdshell to get a count of the number of files that are in that folder on a different server and send out an email if the number of files in the folder exceed a specified number. Is this possible?

Thanks

 

[ESquared]

For what it's worth, if the account that your SQL Server service is running under has network access to other machines, then you have a security issue.

It's better for you to schedule a script to run on the file server itself. You could ask the folks in the vbscript forum how to check for a count of files and send an email. It's very easy.

This really has nothing to do with SQL Server and I don't recommend that you use xp_cmdshell for it.

 

[mrdenny]

If you wanted to do the entire process within T/SQL, which is probably the goal you can use xp_dirtree to get the list of files on the remote server without using xp_cmdshell. The Windows Account which runs the SQL Server will still need rights to the network share which hosts the files.

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)

My Blog

 

[ESquared]

Denny,

Thanks for the info about xp_dirtree.

But just because it can be done in SQL doesn't mean it should. Unless the process that watches the files on the other server is something in SQL Server, then it seems like a clear misuse of the server when better methods are available.

sonname,

Another route is to watch for the process itself to fail. If it is actually terminating, you can use the batch command statement that lists running processes to check and see if it is still running. Other ways to check the health of the process may be possible.

 

[mrdenny]

This is true. SQL isn't the answer for everything.

However without knowing the goal of the process it's hard to give advise in either direction.

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)

My Blog