Results 1 to 14 of 14

Thread: xp_cmdshell

  1. #1
    Join Date
    Jun 2006
    Location
    DC
    Posts
    123

    Unanswered: xp_cmdshell

    Where should i read up on using
    dos commands in conjunction with the xp_cmdshell extended stored procedure..
    I need to manipulate file names....
    move copy take off ... parts of the filename and replace... them...
    etc... any good sites or topics... so that I can learn how to do this...
    thanks...
    jonathan

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    A good resource for DOS commands would be any old codger with grey hairs in his beard.
    Or you could google for DOS COMMANDS.
    Also, google ROBOCOPY and see if it might do somethings for you that standard DOS does not.
    I miss DOS
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Jun 2006
    Location
    DC
    Posts
    123
    I want to append the date to the file OrgExtract
    i guess everything within the quote is in the dos world not allowing me to use a concatenation like in sql server... i really need to learn how to use the FSO object and active X this is silly....
    how would I do that... appending the date
    like 042707 on the end.....

    EXEC master..xp_cmdshell 'rename E:\SumTotal\FTPFiles\Elu\OrgExtract'+@FDate+'.txt OrgExtract.txt'

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Well, you could do this with dynamic sql, though the idea of mixing dynamic sql with xp_cmdshell calls gives me the shivers.
    I hate to say this, but you could also do this through DTS....
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    why do you need dynamic sql? xp_cmdshell takes a string argument. you just build up that string in a varchar or whatever, and then call the xp with that string. or is that what you mean by dynamic sql?

    when I think dynamic sql, I think sp_executesql, which there is no need for here unless I'm missing something.

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    To me, executing a dynamically constructed string is dynamic SQL. But that's just my opinion.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    understood.

    under that definition, most uses of xp_cmdshell that i've seen involve dynamic sql. this is not to say they shouldn't make you shiver.

  8. #8
    Join Date
    Dec 2002
    Posts
    1,245
    You could also (presuming it's SQL 2000) use the sp_OA* stored procs to instantiate Scripting.FileSystem objects. If it's SQL 2005, you could use the CLR to accomplish more or less the same thing.

    However, imho, file system manipulation belongs outside the database.
    Quote Originally Posted by SQL BOL (KJV)
    Render unto SQL the things which are SQL's, and unto the OS the things that are the OS'.
    Regards,

    hmscott
    Have you hugged your backup today?

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    ...and unto the interface such things as are the interface's.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by hmscott
    If it's SQL 2005, you could use the CLR to accomplish more or less the same thing.
    This is my preference if you do this sort of thing from within SQL Server.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    if you do, you'll have to import the assembly as UNSAFE as sql server won't allow you to touch the file system from an assembly otherwise. just something to be aware of.

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by jezemine
    if you do, you'll have to import the assembly as UNSAFE as sql server won't allow you to touch the file system from an assembly otherwise. just something to be aware of.
    I've only required EXTERNAL_ACCESS for the stuff I have done so far though I am, admittedly, still dipping my toe in the water.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  13. #13
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    hmm, i was under the impression that if you write to the file system you are UNSAFE. I guess he'll find out.

  14. #14
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I will test out some file wibbling tomorrow in the office. I can't be bothered writing it all out from scratch at home.....

    In any event I am pretty ignorant to the risks of using CLR integration but I am well aware of the risks of concatenating user input into a string for execution by xp_cmdshell. It can't get much worse than that... can it?
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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