Results 1 to 6 of 6

Thread: xp_cmshell

  1. #1
    Join Date
    Oct 2002
    Posts
    123

    xp_cmshell

    I am trying to execute a query through xp_cmdshell. when i run it through command prompt, it works fine.
    but when i run the same through xp_cmdshell, it says 0 rows affected.
    i have been able to use xp_cmdshell successfully before.
    here are the 2 statements. i am running on sql server 7.0, sp 3.


    bcp "select * from pubs.dbo.authors" queryout c:\Test\test2.txt -c -q -Sservername -U"username" -P"123"

    exec xp_cmdshell 'bcp "select * from pubs.dbo.authors" queryout "c:\\Test\test2.txt" -c -q -Sservername -U"username" -P"123"'

    thanks!
    Last edited by Ads; 01-27-2003 at 04:32 PM.

  2. #2
    Join Date
    Dec 2002
    Posts
    181
    Ads,
    It may be a permissions issue. Check out xp_cmdshell in BOL for details of what security context this command runs under.

    Jeff

  3. #3
    Join Date
    Oct 2002
    Posts
    123
    The user is member of sysadmin group! thats what BOL says!

  4. #4
    Join Date
    Feb 2003
    Posts
    1,048
    Is the c:\Test\ directory that you are targeting on the SQL Server itself?

    xp_cmdshell targets itself even if you are running the proc from a remote server. So, if the SQL Server doesn't have c:\Test, then it will fail. The UNC path to the directory will work though.

  5. #5
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    It must be a typo though
    *
    queryout "c:\\Test\test2.txt"

    As Rawhide said, When you run from xp_cmdshell the file will be created in the server but if you run from your desktop cmd prompt, it will created locally.

  6. #6
    Join Date
    Feb 2003
    Posts
    1,048
    Originally posted by skhanal
    It must be a typo though
    *
    queryout "c:\\Test\test2.txt"
    Yes, but it should still be parsed correctly.

Posting Permissions

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