[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 338
  • Last Modified:

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.
 
0
poojanair
Asked:
poojanair
  • 3
  • 3
1 Solution
 
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
 
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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
 
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
 
poojanairAuthor Commented:
Thanks for all the help.Actually the folder I was trying to write the file to did not have write permission.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now