Results 1 to 2 of 2

Thread: xp_cmdshell

  1. #1
    Join Date
    Oct 2012
    Posts
    1

    Unanswered: xp_cmdshell

    Hi,

    I created stored procedure to create trigger file in a perticular directory using
    xp_cmdshell.

    I am calling the procedure from windows batch script as follws

    @@set osq200=osql /a 4096 /b /E /e /d %dbn% /m-1 /r 0 /S %dbs% /Q "exec SP_Create_TriggerFile %2,%1 "
    @%osq200% >>%3rec.txt 2>%3rec_err.txt
    @set dberr=%errorlevel%
    @if %dberr% GTR 0 goto createDATriggerFileErr >>%3rec.txt

    If the directory doesn't exist, its throwing error "The system cannot find the path specified" , but the %errorlevel% still showing as 0..

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    This was a relatively common problem at one client, but it depends on a specific build of the OS and a range of builds of OSQL. It does not occur with SQLCMD.EXE, so the easy fix would be to use SQLCMD.EXE instead of OSQL.EXE but that sidesteps a much larger problem.

    Using xp_cmdshell is terribly insecure, which is why Microsoft has all but removed xp_cmdshell from the SQL Server product. Calling xp_cmdshell from within a trigger is a performance nightmare waiting for its next victim. You seem to be playing will many tools that can and will hurt you badly!

    You may or may not realize it, but you are playing in very dangerous territory. I would make finding another way to do whatever you need to do a very high priority!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

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