DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 9 of 9

Thread: Execute a DOS command from an Extended Stored Procedure dll

  1. #1
    Join Date
    Sep 2005
    Posts
    6

    Execute a DOS command from an Extended Stored Procedure dll

    Hi.
    I want to execute a shell command from a dll.

    Specifically, I want to use C++ to generate an Extended Stored Procedure, to be called from a VB6 app, which takes a DOS command as a string input parameter and execute this command.

    I want to use this to call the bcp utility to import or export text file data to or from database tables.

    Is this possible using ShellExecute?
    I've tried to do it using the system() function but with no success.
    Is there some other function that I can use?

    I can get it to work in an C++ executable but when I try to use it in an extended stored procedure, SQL Server hangs.

    I'm new to C++ so any help would be appreciated.

  2. #2
    Join Date
    Dec 2003
    Posts
    3,366
    try using shellexecute or shellexecuteex and maybe thread it or tell it NOT to wait for the dos command to complete. This is just a hunch, but I think that might help.

  3. #3
    Join Date
    Nov 2003
    Posts
    4,118
    look at the spawn family of functions, they launch a new process without waiting for it to return (there are about 6 functions, so be sure to use the one that doesn't wait for the child process to exit).
    Danny Kalev

  4. #4
    Join Date
    Sep 2005
    Posts
    6
    Hi All.

    Thanks for your input on this.

    I tried the following code, (hard coded Notepad for testing purposes:

    int pid=spawnlp(P_NOWAIT,"notepad","notepad",NULL,NULL);

    When I tried to run this through Query Analyser it ran OK, (i.e. it didn't hang), but Notepad didn't open.

    When I compiled it as an ordinary C++ executable and ran it, it worked fine and NotePad opened.

    Anything I try seems to work fine as an executable, but doesn't seem to do anything as an Extended Stored Procedure call.

    I tried the Shellexecute function, (thanx for that Jonnin), and it's the same story. The executable works fine but the Extended Stored Procedure call does nothing.

    This is very frustrating.
    Any more ideas?

    Thanks in advance.

  5. #5
    Join Date
    Nov 2004
    Posts
    31
    In your experiment to launch Notepad from your extended stored procedure, did you verify via task manager or tlist that notepad was not loaded & running? Becareful when launching a GUI based process from processes such as SQL Server where SQL Server is running in a different user context then the interactive login. It's possible that notepad is running but is not visible in your interactive desktop.

    You may already be aware, but you can probably achieve comparable functionality using the SQL Server provided extended stored procedure xp_cmdshell.

    Thanks,
    Mike

  6. #6
    Join Date
    Sep 2005
    Posts
    6

    Execute a DOS command from an Extended Stored Procedure dll

    Hi Mike,

    Good point about Taskmanager.
    I ran the program again and checked it but Notepad wasn't showing up.

    The point you make about the "user context" is also a good one.
    i'll have to investigate this a bit more.

    I was using xp_cmdshell and it was working fine.
    However, each SQL Server service pack release has messed this up.
    I've been able to hack around this up till now but SP4 has stumped me.
    The application is for a bank so general EXECUTE permission for xp_cmdshell is not an option.

    I know that SQL Server uses a Proxy Account to execute my bcp command.
    Mgiht this be related to the "user contex" point you mentioned?

    Anyway, thanks for your input.
    Any further suggestions would be appreciated.

  7. #7
    Join Date
    Sep 2005
    Posts
    6
    My apologies to everyone.
    I now find that my original code, using the system function, does actually work.
    I had set up my local as a server as was doing all my testing on this.
    I had mistakenly set this up such that files were being written to my share on the company server instead of my local machine, (don't really understand how I managed to do this, it was my boss who actually pointed it out to me).
    Anyway, thanks to everyone for their input.
    Hope I haven't wasted too much of your time.

  8. #8
    Join Date
    Nov 2003
    Posts
    4,118
    Well, at least you've learned some new ways of launching a child process. Anyway, notice that system() is very insecure so you may still need to replace with something less vulnerable to hacking in the future.
    Danny Kalev

  9. #9
    Join Date
    Sep 2005
    Posts
    6
    Thanks Danny.
    I'll bear that in mind.

    What I intend at the moment is to set up a database role with EXECUTE permission to my new procedure, then add all relevant users to this.
    This will have to do for the moment as our clients are eager to apply SQL Server SP4 as soon as possible.
    I'll need to investigate the security implications of the system() function.

Similar Threads

  1. execute a stored procedure from c#
    By xxxxx in forum ASP.NET
    Replies: 0
    Last Post: 07-26-2005, 05:01 AM
  2. Replies: 1
    Last Post: 06-15-2003, 10:49 AM
  3. call a dll from a stored procedure
    By Daniel Teufert in forum Database
    Replies: 0
    Last Post: 08-30-2002, 05:26 AM
  4. Replies: 0
    Last Post: 03-29-2002, 12:23 AM
  5. Replies: 1
    Last Post: 09-23-2001, 04:52 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center