SQLTeam.com | Weblogs | Forums

Bcp utility in clr procedure as a process


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 ...)