Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Export SQL Data to Text File

Answered Export SQL Data to Text File

  • Sunday, February 10, 2013 9:34 PM
     
      Has Code
    Sign In to Vote
    0

    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?


     

All Replies

  • Sunday, February 10, 2013 9:57 PM
     
     Proposed
    Sign In to Vote
    0

    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, esquel@sommarskog.se
     
  • Sunday, February 10, 2013 10:40 PM
    Moderator
     
      Has Code
    Sign In to Vote
    0

    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


     
  • Monday, February 11, 2013 4:37 AM
     
      Has Code
    Sign In to Vote
    0

    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

    • Edited by anwar.mustafa Monday, February 11, 2013 4:40 AM Code included
    •  
     
  • Monday, February 11, 2013 9:38 AM
     
     Answered Has Code
    Sign In to Vote
    0

    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

    • Marked As Answer by anwar.mustafa Saturday, February 16, 2013 6:14 AM
    •