A. As far as functionality goes, you can do anything you like - read/write files, send network messages, call OLE/COM functions, kick off other programs, run ftp's etc. etc.
There are a choice of ways of invoking these things :-
1. Write your own extended stored procedure - which is essentially a DLL with some SQL call constructs in. See Q190987 for more details. You can't code XP's in VB, so you will need C (or Delphi) programming knowledge.
For SQL 6.5 download the PTK from http://support.microsoft.com/download/support/mslfiles/PTK_I386.EXE
For SQL 7 look on the CD under \devtools
2. Use the SQL OLE Automation calls to call a COM object - sp_OAxyz See Q152801 and the books-online for details of sp_OACreate, sp_OAMethod, sp_OAGetProperty and sp_OASetProperty
3. Run an external .EXE via the supplied xp_cmdshell extended stored procedure. See the books online for details.