Page 1 of 3 123 LastLast
Results 1 to 15 of 31
  1. #1
    Join Date
    Mar 2006
    Posts
    82

    Unanswered: .exe from stored procs

    Hey guys.

    I am wondering if it is possible to execute an .exe from a stored proc. I have an application that calls a stored proc and I am wanting for that stored proc to call up an app to show certain results. I have no way of doing this on the application itself as I dont have the source code. So, since I do have access to the SQL I am wondering if it could be done there.

    Thanks
    tibor

  2. #2
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    you can launch an exe from a proc with xp_cmdshell. you can't display any of it's output though, except for whatever it writes to stdout.

    if it's a GUI app, there's no point to it. Also, remember that this app would have to be located on the server.

    Be very careful with this. If you enable xp_cmdshell, it opens up a fairly large security hole on your db server. Make sure only appropriate logins have perms to execute it. don't give any clients permissions to execute it directly or you may find that a criminal has stolen all your data.

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    It's not advisable

    You could bring down the server from what I understand

    But if you want to try you need to use master..xp_cmdshell

    But if you don't know about the sproc, then I'd really advise against it
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Mar 2006
    Posts
    82
    Thanks guys

    Turns out, I may be able to avoid the project in its entirety but this is good info to know for the future.

    Again, very much appreciated

  5. #5
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    just be careful if you ever decide to use it. xp_cmdshell is a cross between a loaded gun and a vial of nitroglycerin.

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by jezemine
    just be careful if you ever decide to use it. xp_cmdshell is a cross between a loaded gun and a vial of nitroglycerin.

    pffffftttttt

    The real danger is trying to launch an application with it

    Hey DROP Database is kinda dangerous as well
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    the real danger is that you can use xp_cmdshell to do anything you could do from a command prompt, which is pretty much anything.

    if the wrong people have rights to execute xp_cmdshell, you are in trouble. so best left disabled.

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by jezemine
    if the wrong people have rights to execute xp_cmdshell, you are in trouble.
    Of course, would you give a gun to a toddler?

    so best left disabled.
    Couldn't disagree more
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    Tibor, don't listen to Brett.

    he's a wild vocalist for an all-girl 80's hair-metal band. everyone knows such types can't be trusted.


  10. #10
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    belly aching laughter. on the floor. verge of tears.

    but really if your shop is not locked down with regards to db server security, shame on you. Although I have been somewhere that refused to be locked down. So I left.

    I find xp_cmdshell extremely useful especially combined with osql. I do my database builds that way by doing a fresh dump from source control and letting those rip with a little cursor and some logging. A little Redgate afterwards to be sure and everything is right as rain.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  11. #11
    Join Date
    Jul 2007
    Posts
    6

    another way to call .exe from stored procs?

    Hi,

    I've quite the same Problem as Tibor.
    I also try to call a .exe from a trigger.
    The .exe is a VB.Net command line application, that shall invoke a ETL script on a third party BI Application. The VB application first calls the getObject Function and then calls the Reload Function of the BI application.
    This application works fine, when I start it in the command line.
    But when I try to call it with the xp_cmdshell proc the execution stucks and after a while an exception is thrown ("ActiveX component couldn't be started" or so on)
    Until now, I haven't found any way to fix this problem.
    Now I'm searching for another way of calling an .exe from a trigger. I don't know, if this helps. Maybe you have any suggestions to deal with this problem
    Thanks in advance

    Gojo

  12. #12
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    you want to call an exe from a trigger? pardon me, but that's a less than good idea. It's quite bad actually.

    triggers need to have a very constrained scope - they shouldn't be launching other processes, sending emails, writing data to remote servers, calculating your horoscope, or anything like that.

    if you do somehow figure out how to launch an exe from a trigger, the perf or your application will suffer big time.

    what are you trying to achieve with this? why do you think launching an exe is the way to do it?

  13. #13
    Join Date
    Nov 2005
    Posts
    122
    Quote Originally Posted by jezemine
    triggers need to have a very constrained scope - they shouldn't be launching other processes, sending emails, writing data to remote servers, calculating your horoscope, or anything like that.
    If you're using SQL Server 2005, you can use the Service Broker to do such "crazy" stuff without ending up with long running transactions.

  14. #14
    Join Date
    Jul 2007
    Posts
    6
    Thanks for the fast replies

    @ jezemine
    I know, the idea sonds weird. My problem is, that I want to keep my data in the dashboard event triggered.
    In this case a new DB entry is a event. But when I'm constantly polling the table (as I do now) the solution isn't event triggered. The only way to achieve this, is to call the .exe (which calls the load script), when there is a new entry in the table (therefore the trigger).
    Do you have another idea how to accomplish that (I mean how to tell my .exe that there's a new entry, so that the .exe can call the load script)?

    @kaffenils
    Unfortunately I have to use the 2000 version

    thanks
    gojo

  15. #15
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    you could write an app that polls the table every so often, and when there's a new row then it executes your load script.

    I don't understand what you mean by "the solution isn't even triggered" btw.

Posting Permissions

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