Why is my bcp query not working?

Question

I'm beginner in C#, I want use the bcp utility in C# and to do so, I wrote this code:

 string connstr = "Data Source=192.168.50.172;Initial Catalog=CDRDB;User ID=CDRLOGIN;Password=beh1368421"; 

 //string connstr = "Enter your connection string here";
 System.Diagnostics.Process proc = new System.Diagnostics.Process();
 proc.StartInfo.FileName = "bcp";
 proc.StartInfo.Arguments = @"select * from CDRTABLE"" queryout c:\filename.csv -S 192.168.50.172 -U CDRLOGIN -P beh1368421 -f -w";
 proc.Start();

That code runs, but filename.csv is not created on my C:\ drive. What happened? How can I solve this problem?

What is [this "BCP" thing](https://en.wikipedia.org/wiki/BCP)? — Uwe Keim, Feb 08 '16 at 13:37
Needs a leading ". Print your string and try it manually from the console. — Alex K., Feb 08 '16 at 13:38
@UweKeim BCP use the in sql server for read from other sql server database and write query result in csv file — user3671271, Feb 08 '16 at 13:38
bcp: The bcp utility bulk copies data between an instance of Microsoft SQL Server and a data file in a user-specified format. https://msdn.microsoft.com/en-IN/library/ms162802.aspx — Anil, Feb 08 '16 at 13:38
Does that bcp command work *outside* you program (eg. from the command line). If not: fix the command line and then look at integrating. If it does then there is a problem with your process launching code (start checking defaulted working directories); — Richard, Feb 08 '16 at 13:40
How many times are you going to ask BCP questions without following up when people answer your question??? I remember at least two from the recent future that you simply abandoned. Did the answers help you? Did they not help you? How about at least dropping a comment when people **do** answer and show some respect for the effort they put in trying to help you. — TT., Feb 08 '16 at 13:45

score 0 · Answer 1 · edited Feb 08 '16 at 14:19

For Bcp executing from System.Diagnostics.Process(), one should use the -c and -CRAW options.

Below is sample code.

string execPath = @"..\bcp.exe";
string localFile = @"MetaData.Table";
string localPath = @"..\OutputFolder";

System.Diagnostics.Process P = new System.Diagnostics.Process();
P.StartInfo.UseShellExecute = false;
P.StartInfo.RedirectStandardOutput = true;
P.StartInfo.RedirectStandardError = true;
P.StartInfo.CreateNoWindow = true;
P.StartInfo.FileName = execPath;

P.StartInfo.Arguments = string.Concat("\"", "MetaData.Table", "\" out \"", localPath, "\\", localFile, ".dat\" -c -C RAW -t\"", separator, "\" -S \"", "(local)", "\" -T");

P.Start();
P.WaitForExit();
P.Close();
P.Dispose();

You can also use BCP in C# code using BULK INSERT Transact-SQL statement and using the ExecuteNonQuery method of SqlCommand.

Something like below will work:

sCommandText = "exec xp_cmdShell 'bcp.exe'"+Database + ".." + TableName + " in "  + 
                 FileName + " -c -q -U " + UserId + " -P " + Password +  "-t  ";

SqlConnection conn = new SqlConnection(ConnectionString);
conn.Open(); 

SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = sCommandText;

return cmd.ExecuteNonQuery();

Why is my bcp query not working?

1 Answers1