I have done a bridge between an ERP system and a TA system. The user calls a SP on the sql server. The SP retrives a lot of data and makes several texfiles using XP_cmdshell. Finally it uses XP_cmdshell to send one file through FTP (curl.exe). It also updates a loggfile and should send error messages to the client if needed. My huge problem is that when the SP uses XP_cmdshell the spid changes and looses connection to the client and after that I cant get the SP to send error messages to the client anymore. Everything works fine when I execute the SP in a Query on the server. PLEASE, PLEASE, PLEASE could someone help me to solve this? Is it possible to send errormessges to a specified spid? What is the common solution to this problem?
By the way, is it stupid to have everything in the same SP? Is it better to split the SP into seperate ones?