0

Below, I'm calling one stored procedure from c#.net windows forms.

The stored procedure contains following:

 exec master..xp_cmdshell ''bcp "select ''''Column1'''',''''Column2'''',''''Column3'''',''''Column4'''',''''Column4'''',''''Column5'''',''''Column6'''',''''Column7'''',''''Column8''''" queryout "' + @Parent_Folder + @Transformed_Tbl + '.csv" -c -t, -T -Slocalhost'''

    exec master..xp_cmdshell ''bcp Databasename..' + @database table+ ' out "' + @serverfolder+ 'temp_' + @user specified file name + '.csv" -c -t, -T -Slocalhost'''

    exec master..xp_cmdshell ''copy /b "' + @serverfolder+ @user specified file name + '.csv" + "' + @serverfolder+ 'temp_' + @user specified file name + '.csv" "' + @serverfolder + @user specified file name+ '.csv"'''

It's running perfectly,

but some times users want to cancel this process. How can I cancel this running bcp process when the user hits the cancel button. Here is my application in my local system and I am accessing Database from another sql server system then saving the file in same sql server system.

Kindly give me an idea about this issue.

kishore
  • 108
  • 1
  • 3
  • 12

2 Answers2

1

Using the "xp_cmdshell" is not secure because it allows you to do all those OS level things like running external executable.it is recommended to not use "xp_cmdshell".

you should look for replacement of xp_cmdshell-

http://www.mssqltips.com/sqlservertip/2014/replace-xpcmdshell-command-line-use-with-sql-server-agent/

Buzz
  • 6,030
  • 4
  • 33
  • 47
0

xp_cmdshell cannot be canceled from SQL Server using T-SQL commands. You have to remove the functionality to 'cancel' and export from your application, since is not something which is actually possible. If you want a cancelable export, you must roll your own w/o using xp_cmdshell.

Some may recommend killing the spawned bcp process, but that is flawed with many problems (discovering the right process is the simplest one) and unceremoniously killing a process may yield to um predictable results, like a partially finished corrupted export file which gets fed in the upstream consumption workflow.

Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569
  • Any how when the user hits cancel button, we are deleting the exported files from the system using another stored procedure. but the problem here is i am unable to delete the file why because the file used with bcp process.how should i kill this particular process from code behind in c# – kishore Aug 14 '12 at 05:19