Executing BCP from C# code

Aloha to all,
I have thrown together some code that generates a file with a lot of
data (something like 200,000 rows). I would like to load all this data
in a SQL Server table, but my attempt to execute BCP from C# doesn't
want to behave.
I do as follows (more of less stealing everything from
http://dotnetjunkies.com/WebLog/ste...8/19/22566.aspx
):
System.Diagnostics.Process proc = new System.Diagnostics.Process();
proc.EnableRaisingEvents = false;
proc.StartInfo = new System.Diagnostics.ProcessStartInfo();
proc.StartInfo.UseShellExecute = false;
proc.StartInfo.RedirectStandardOutput = true;
proc.StartInfo.RedirectStandardError = true;
proc.StartInfo.FileName = "bcp";
proc.StartInfo.Arguments = @."DATABASE_NAME.dbo.TABLE_NAME in
c:\inetpub\wwwroot\project\bcpData.txt -c -Uusername -Ppassword -t','
-Sservername";
proc.EnableRaisingEvents = true;
proc.Start();
string s1 = proc.StandardOutput.ReadLine();
string s2 = proc.StandardOutput.ReadLine();
All I get from this code is:
s1 = "SQLState = 28000, NativeError = 18456";
s2 = "Error = [Microsoft][SQL Native Client][SQL Server]Login failed
for user 'username'."
I have off course checked the username/password a thousand times and it
is correct. If I copy my Arguments string to the command prompt, I can
execute BCP without any problem
Thank you and roger over, MadsYou could try creating a batch file and then run that. It seems like it's
having trouble putting the arguments in correctly. I don't have the time
right now to try compiling and running your code... but I'm sure you could
easily throw the whole line into a file and running it.
Security-wise though, I'd consider using a trusted connection and
impersonating a particular user.
Unfortunately my timezone means I'm about to go offline for a while, so I
can't post a follow-up. Someone else will though I'm sure (and they'll
probably tell you to ignore me!)
Rob
"Mads.phi@.gmail.com" wrote:

> Aloha to all,
> I have thrown together some code that generates a file with a lot of
> data (something like 200,000 rows). I would like to load all this data
> in a SQL Server table, but my attempt to execute BCP from C# doesn't
> want to behave.
> I do as follows (more of less stealing everything from
> http://dotnetjunkies.com/WebLog/ste...8/19/22566.aspx
> ):
> System.Diagnostics.Process proc = new System.Diagnostics.Process();
> proc.EnableRaisingEvents = false;
> proc.StartInfo = new System.Diagnostics.ProcessStartInfo();
> proc.StartInfo.UseShellExecute = false;
> proc.StartInfo.RedirectStandardOutput = true;
> proc.StartInfo.RedirectStandardError = true;
> proc.StartInfo.FileName = "bcp";
> proc.StartInfo.Arguments = @."DATABASE_NAME.dbo.TABLE_NAME in
> c:\inetpub\wwwroot\project\bcpData.txt -c -Uusername -Ppassword -t','
> -Sservername";
> proc.EnableRaisingEvents = true;
> proc.Start();
> string s1 = proc.StandardOutput.ReadLine();
> string s2 = proc.StandardOutput.ReadLine();
> All I get from this code is:
> s1 = "SQLState = 28000, NativeError = 18456";
> s2 = "Error = [Microsoft][SQL Native Client][SQL Server]Login failed
> for user 'username'."
> I have off course checked the username/password a thousand times and it
> is correct. If I copy my Arguments string to the command prompt, I can
> execute BCP without any problem
> Thank you and roger over, Mads
>|||Excellent! That fixed my problems, so the only thing left is that I'm
ashamed of not having thought of a .bat file myself.
Thank you, Mads|||About the C# code, in general I'd change
proc.StandardOutput.ReadLine();
to
proc.StandardOutput.ReadToEnd();
to grab all the error message for troubleshooting purposes. Of course, this
doesn't address your particular problem.
Linchi
"Mads.phi@.gmail.com" wrote:

> Excellent! That fixed my problems, so the only thing left is that I'm
> ashamed of not having thought of a .bat file myself.
> Thank you, Mads
>

Executing a Stored Procedure result

Hi Guys..

How Can i Execute a result from a StoredProcedure... I got a sp that generates drop index and pk from all tables in the DB..

I got this results from running (sp_dropallindex) like this:

ALTER TABLE Table1 DROP CONSTRAINT PK_Table1 GO
ALTER TABLE Table2 DROP CONSTRAINT PK_table2 GO
DROP INDEX table1.index1 GO
DROP INDEX table1.index2 GO

I need to execute that result.. I know that i can copy/paste into Query Analyzer and then run it but how can i handle that result and run all in shot ...

I tried something like this:

DECLARE @.DROP AS VARCHAR(8000)
SET @.DROP='exec sp_drop_allindex'
EXECUTE (@.DROP)

and i see the same out , but my indexes and PK still there ... i'm confused about it ..

PLEASE HELP ME OUT :DI'd use BCP with QUERYOUT and then OSQL against the output file.

BCP "exec db_name.dbo.sp_dropallindexes" queryout drop_all_indexes.sql /S server_name /T
OSQL -i drop_all_indexes.sql -S server_name -E -d db_namesql