Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2003
    Posts
    13

    Unanswered: Trying to run CMD File On One Server and Put Data on Another

    Alright, I've got a stored procedure one one SQL Server 2000 server that looks like this:

    CREATE PROCEDURE test AS

    declare @return_code int

    EXEC @return_code = master..xp_cmdshell call c:\inetpub\wwwroot\test\cmd\test.cmd'

    GO
    This obviously executes a cmd script that looks like this:

    copy \\testmachine\sldc\data\alltxns.dat \\testmachine\sldc\data\test.txt
    The above script is trying to write a file to the data folder of another server.

    When I execute the cmd from the DOS cmd window, it works fine. However, when I run it through a stored procedure, I keep getting 'Access Denied.'

    SQLAgent is listed as a user with full access on the target's data folder. Just in case, the logon id from the source server is listed as a user with full access on the target server's data folder. Yet still I get the error.

    Any ideas?

  2. #2
    Join Date
    Aug 2003
    Posts
    51
    Don't know the answer, but I know how to find out.

    If you use auditing from MS on the target directory, you should be able to see (within the event viewer) who is trying to access the directory. This will (or should help you) in terms of setting-up the proper security.

  3. #3
    Join Date
    Aug 2003
    Posts
    13
    Yeah, we did that but it didn't really tell us much.

  4. #4
    Join Date
    Aug 2003
    Posts
    51
    Originally posted by mferrell
    Yeah, we did that but it didn't really tell us much.
    Was there an entry on the target server to indicate that a user tried to access the folder? I'm guessing that SQL Agent is a local account on your SQL Server and not a domain account. If this is the case, then you will need to enable guest and allow guest to access the folder.

    The alternative is to make the SQLAgent account a domain account, then you should have little difficulty.

    Rich

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •