INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • 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!

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

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

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

Using xp_cmdshell to count files in a folder on different server

(OP)
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

RE: Using xp_cmdshell to count files in a folder on different server

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.

RE: Using xp_cmdshell to count files in a folder on different server

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

RE: Using xp_cmdshell to count files in a folder on different server

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.

RE: Using xp_cmdshell to count files in a folder on different server

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

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!

Resources

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