Export SQL Data to Text File

Question

Hi,

I am using SQL Server 2008 and want to retrieve data from SQL to text file. I am using following stored procedure but when I execute it. It gives me 0 output and did not copy any data to text file as well. Please guide me where is my mistake?

ALTER Procedure ExportInText
(  
@table varchar(100),  
@FileName varchar(100)  
)  
as  
If exists(Select * from information_Schema.tables where table_name=@table)
    Begin
        Declare @str varchar(1000)  
        set @str='Exec Master..xp_Cmdshell ''bcp "Select * from '+db_name()+'..'+@table+'" queryout "'+@FileName+'" -c'''  
        Exec(@str)  
    end
else
    Select 'The table '+@table+' does not exist in the database'

Further I want to add 2 more parameters under Where Clause, how should I modify my SP for this output?

Answer 1

To start with, there is no need to use EXEC(). Just say

SELECT @cmd = 'bcp ...'
PRINT @cmd
EXEC xp_cmdshell @bcp, 'no_output'

I added the PRINT command, so that you easily can see if there are any errors.

I also like to point out that there are better alternatives for file export, for instance run BCP from a client program or a BAT file. And then there is SSIS...

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 2

Why do you have double '..' ? It should be just one . for the schema name in addition to Erland's comments.

Also, correction to Erland's code:

SELECT @cmd = 'bcp ...'
PRINT @cmd
EXEC xp_cmdshell @cmd, 'no_output'

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 3

Thank You Guys,

Can you please give me the complete SP code against my SP, I am first time using this BCP utility, so don't know how to compose it?

I am using it as:

ALTER Procedure ExportInText
(  
@cmd varchar(1000)  
)  
as  

Begin
	SELECT @cmd = 'bcp "Select * from AHT" queryout "c:\Output_Files\me.txt" -c'
	PRINT @cmd
	EXEC xp_cmdshell @cmd, 'no_output' 
End

Answer 4

Hi,

If possible try to use SSIS package. Its very easy and user friendly.

alter Procedure ExportInText
(  
@table varchar(100),  
@FileName varchar(100)  
)  
as
BEGIN
	If exists(Select * from information_Schema.tables where table_name=@table)
    Begin
        Declare @str varchar(1000)  
        set @str='Exec Master..xp_Cmdshell ''bcp "Select * from '+db_name()+'.'+@table+'" queryout "'+@FileName+'" -c'''  
		EXEC xp_cmdshell @str, 'no_output'
    end
END
GO

---

Thanks & Regards Prasad DVR