I made an CLR project and I want to call bcp utility as a process in it. My project looks like :
[SqlProcedure] public static int CmdShellBridgeBCP(string arguments) { return processCMD("bcp.exe", arguments); }
private static int processCMD(string fileName, string arguments) { try { Process proc = new Process(); proc.StartInfo.FileName = fileName; proc.StartInfo.Arguments = arguments; proc.Start(); proc.WaitForExit(); } catch (Exception ex) { return 1; } return 0; }
And the stored procedure where I try to use this looks like :
set @execcommand = @CSSdbname+'.BankCardtrxGPEtemp in '+@importdir + @Kfilename +' -c -S '+@CSSserver+' -r \n -T -C 1250 -e '+@importdir+'bcp.error -m 1000 >NUL'
exec @result = dbo.CmdShellBridgeBCP @execcommand
Now the result is that nothing was imported in my BankCardtrxGPEtemp table. I have no errors, the return value is 0, not 1.
If I use
exec @result = master..xp_cmdshell @execcommand, NO_OUTPUT
it works ok. But I want not to use xp_cmdshell.
What is wrong with my code ?
Assembly has UNSAFE permission, because I have to use external shared folders to access files (txt, cvs, etc ...)