Export data from SQL to text file dynamcally

Question

I would like to know how I can export data from a select command to a normal text file from MSSQL using the xp_cmdshell bcp method.

I'm using SQL 2012 an my code looks like this :

Declare @filename varchar(50),
        @cmd varchar(4000),
        @file varchar(100)
set @filename = 'TestMe'
set @file = 'c:\' + @FileName + '.txt'
set @cmd = 'bcp "Select * from TableName" queryout"'
set @cmd = @cmd + @file + '" -T -is'
EXEC xp_cmdshell @cmd

The query executes successfully, but the it gives me an output that says:

bcp: Unable to open input file s: No such file or directory

I even tried creating the file and hardcoding the name, but to no avail. Any help would be greatly appreciated.

Stephen Byrne · Accepted Answer · 2013-11-06 11:16:28Z

2

you are specifying the -i flag which is to specify an input file. Hence the command is interpreted as "use input file 's'" which of course doesn't exist...

Just for reference, your BCP command should look like this:

bcp "Select * from TableName" queryout "OutputFilename" -d "DatabaseName" -T -n

where TableName is the name of the table, OutputFilename is the full path + file name and DatabaseName is the actual database. This assumes Trusted connection (-T), local server (didn't put in the -S switch), and automatically picks a format for the output columns (-n)

Hope that helps.

edited Nov 6, 2013 at 11:16

answered Nov 6, 2013 at 11:03

Stephen Byrne

7,5151 gold badge35 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Dewald Henning Over a year ago

Thanks. It does help. The problem now is that it says : Error = [Microsoft][SQL Server Native Client 10.0]Unable to open BCP host data-file. I have given the folder full access.

Stephen Byrne Over a year ago

Are you sure that the path exists from the server's point of view?

Stephen Byrne Over a year ago

Hmm. Can you edit your question to include the bcp command that's being generated (also, did you try executing this on the command line) so we can have a look?

Dewald Henning Over a year ago

I've managed to find the problem by running it on the server in command prompt. It was a space issue on the drive. I couldn't believe it. Thank you very very much for your help.

Stephen Byrne Over a year ago

:) always the last thing you check! glad its working.

Bogdan Bogdanov · Accepted Answer · 2013-11-06 10:56:59Z

2

Check if your SQL have permissions to access C:. This can be done easily by trying to backup something from SSMS on C:\

answered Nov 6, 2013 at 10:56

Bogdan Bogdanov

1,7232 gold badges21 silver badges31 bronze badges

1 Comment

Dewald Henning Over a year ago

Rights is fine. Checked that first.

Collectives™ on Stack Overflow

Export data from SQL to text file dynamcally

2 Answers 2

5 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related