Export table from Sql server to Text file

Hey,
I have a huge result set with 1 million records and I need to export them to text file.What's the most efficent  way of doing it in C# ?
Can I use bcp from C#?If so, could you please point me to the code.
Thanks in Advance.
 
poojanairAsked:
Who is Participating?
 
wht1986Commented:
I created a stored proc as

use PM_ReWrite
GO

CREATE PROCEDURE usp_DumpTable
AS
select * from CodeLookup
GO


The following c# code worked from a windows application on button click

        private void button3_Click(object sender, EventArgs e)
        {
            Process bcp = new Process();
            bcp.StartInfo.WorkingDirectory = @"C:\";
            bcp.StartInfo.FileName = "bcp";
            bcp.StartInfo.Arguments = "\"exec PM_ReWrite.dbo.usp_DumpTable\" queryout c:\\Results.txt -S SERVERNAME -U USERNAME -P PASSWORD -r \\n -c";
            bcp.Start();
            bcp.WaitForExit();
        }
0
 
wht1986Commented:
You can spawn it as a seperate process thread.  Example here
http://unplug1dot6.blogspot.com/2008/01/use-bcp-within-aspnet-c.html
0
 
poojanairAuthor Commented:
The below code does not seem to work for me.
 Process bcp = new Process();
            bcp.StartInfo.WorkingDirectory = @"C:\Test";
            bcp.StartInfo.FileName = "bcp";
            bcp.StartInfo.Arguments = "Sample..TEST"
              + " queryout " //out
              + @"C:\Results.txt"
              + @" -c -r \n -SSERVER -UUSERID -PPWD";
            bcp.Start();
            bcp.WaitForExit();

Sample is the Database Name and TEST is the stored procedure name. Am I passing wrong parameters?

Moreover in my case it wud be nice if I can make the call from Stored Procedure itself (btw I'm using SQL server 2005). Something like this

declare @sql varchar(8000)
select @sql = 'bcp Sample..Results  out  c:\Results.txt -c -SSERVER -UUSERID -PPWD'
exec master..xp_cmdshell @sql
where Sample is the Database name and Results is the table name and this seems to  work .

But in my case I hold the results in Temp table and when I say
select @sql = 'bcp Sample..#Results out  c:\Results.txt -c -SSERVER -UUSERID -PPWD'
I get an error 'Error = [Microsoft][SQL Native Client][SQL Server]Invalid object name '#Results'.'
can you please hep.
Thanks









0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
wht1986Commented:
The arguements are incorrect with the process version of the bcp call, you use queryout but you didnt specify a query.  The command should be something like
Process bcp = new Process();
bcp.StartInfo.WorkingDirectory = @"C:\";
bcp.StartInfo.FileName = "bcp";
bcp.StartInfo.Arguments = "\"select * from Sample.dbo.TEST\" queryout c:\\Results.txt -S MYSERVER -U MYUSER -P MYPASSWORD -r \\n -c";
bcp.Start();
bcp.WaitForExit();

Open in new window

0
 
poojanairAuthor Commented:
I can't say select * from Sample.dbo.TEST because TEST is name of Stored Procedure .
But I tried this
 bcp.StartInfo.Arguments =     "\"EXEC Sample..TEST\"  queryout C:\\xResults.txt -c -r \\n -SSERVER -UUSERID -PPWD";

But did not work even now.

         

         
0
 
poojanairAuthor Commented:
Thanks for all the help.Actually the folder I was trying to write the file to did not have write permission.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.