Smart questions
Smart answers
Smart people
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login




Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Donate Today!

Do you enjoy these
technical forums?
Donate Today! Click Here

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.
Jobs from Indeed

Link To This Forum!

Partner Button
Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

Using xp_cmdshell to count files in a folder on different server

sonname (Programmer)
23 May 08 13:12
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 (Programmer)
23 May 08 13:29
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 (Programmer)
25 May 08 22:20
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 (Programmer)
27 May 08 3:57
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 (Programmer)
29 May 08 2:59
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

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close