Explain xp_cmdshell

I've got some questions about xp_cmdshell and was hoping someone could explain some things to me. I've written a trigger for a table that uses 'xp_cmdshell' to launch a VB.Net application. The syntax is correct and it "seems" to work but in the way I intended. When the app launches there is no GUI present but when I check the task list I see that the exe has been launched. This intrigued me so I started playing with it in Query Analyzer. When I run the command

exec master.dbo.xp_cmdshell 'C:\WINDOWS\system32\notepad.exe'

I seen a process for notepad show up on my processes list started by 'system' as opposed to myself and notepad still doesn't open up for me to see. I also noticed that the query executes until I kill that particular process. I'm guessing cause it's waiting on the tranaction to complete. What I want to know though is why can't I see the program when I open like this? Is it because 'system' is the user behind it or is there some extra parameter to throw in the xp_cmdshell?

It's stuck because the process is waiting for notepad to complete, which it never will on it's own.

You can't see it, because frankly, you don't own that process, nor does it run within the context of your desktop. SQL Server is a service, it starts before you even log into the machine, and windows can have many users logged in at once (Terminal Services, remote desktop, quick user switching, etc). If a service were to want to show a UI, to whom should it show it? What if there are no users logged in at all?

If all you want to do is notify the logged in users on the machine (or a particular user on your network) that an event has occurred, use net send, or your can write your own "net send"-like clients.

|||What I'm hoping to achieve is have a SQL trigger launch a VB.Net console app that uploads the newly inserted data to an AS400 database. I had originally thought a DTS would be most appropriate but that idea didn't seem to work out so I thought I would write a small console app to do this. Whenever I run the app myself it works out fine, but whenever the trigger lanches the app it just sits in the process list indefinately. If you have any suggestions I'm all ears.|||

I have worked for a bank that uses xp_cmdshell to do the opposite of what you want; the DTS package takes deposits in DB2 AS400 and populates SQL Server database used for bank at home. No I did not write the package all I know it works because I have run SQL Server Profiler on the package and watched it work for four hours. The only thing different is SQL Server Agent runs with Domain Admin permissions and the SQL Server running the Agent runs without a database. The data is not immediate but it runs without problems. Hope this helps.