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.
[723 byte] By [
shay] at [2007-11-11 10:15:46]
# 1 Re: Execute a DOS command from an Extended Stored Procedure dll
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.
jonnin at 2007-11-11 20:59:09 >
# 2 Re: Execute a DOS command from an Extended Stored Procedure dll
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 at 2007-11-11 21:00:15 >
# 3 Re: Execute a DOS command from an Extended Stored Procedure dll
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.
shay at 2007-11-11 21:01:13 >
# 4 Re: Execute a DOS command from an Extended Stored Procedure dll
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
# 5 Re: 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.
shay at 2007-11-11 21:03:13 >
# 6 Re: Execute a DOS command from an Extended Stored Procedure dll
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.
shay at 2007-11-11 21:04:12 >
# 7 Re: Execute a DOS command from an Extended Stored Procedure dll
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 at 2007-11-11 21:05:11 >
# 8 Re: Execute a DOS command from an Extended Stored Procedure dll
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.
shay at 2007-11-11 21:06:15 >
