Export a text file

Posted on 2006-06-28
Medium Priority
Last Modified: 2010-05-18
When I run this in query analyzer I get the out put below. Does anyone know why this occurs?

DECLARE @FileName varchar(50),
        @bcpCommand varchar(2000)

SET @FileName = REPLACE('c:\Test'+ CONVERT(char(8),GETDATE(),112)+'.dat','/','-')

SET @bcpCommand = 'bcp "SELECT * FROM mytable" queryout "'
SET @bcpCommand = @bcpCommand + @FileName + '" -U username -P pw -password'

EXEC master..xp_cmdshell @bcpCommand


usage: bcp {dbtable | query} {in | out | queryout |
format} datafile
[-m maxerrors] [-f formatfile] [-e
[-F firstrow] [-L lastrow] [-b
[-n native type] [-c character type] [-w
wide character type]
[-N keep non-text native] [-V file format version] [-q
quoted identifier]
[-C code page specifier] [-t field terminator] [-r
row terminator]
[-i inputfile] [-o outfile] [-a
[-S server name] [-U username] [-P
[-T trusted connection] [-v version] [-R
regional enable]
[-k keep null values] [-E keep identity values]
[-h "load hints"]
Question by:earngreen
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +1

Author Comment

ID: 17001205
Nor does it export the text file.
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 17001212
you have to specify the Servername and the dbname

SET @bcpCommand = 'bcp "SELECT * FROM database.dbo.mytable" queryout "'
LVL 28

Expert Comment

ID: 17001248
There is no -password option for the bcp.  Try removing that and see if it works:

SET @bcpCommand = @bcpCommand + @FileName + '" -U username -P pw'
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI


Author Comment

ID: 17001266

 I just used username and password as an example.
LVL 75

Accepted Solution

Aneesh Retnakaran earned 2000 total points
ID: 17001269
Here is an exaaple

BCP Northwind.dbo.Categories out c:\categories.txt -n -Smyservername -Uusername -Ppassword

If you are using Windows authentication
BCP Northwind.dbo.Categories out c:\categories.txt -n -Smyservername  -T
LVL 28

Expert Comment

ID: 17001317
What I was trying to point out is that you specified 2 values for the password like this:

-P pw -password

Just make sure that there is only 1 value for this.
LVL 21

Expert Comment

ID: 17001385
And after you make those two changes, I think you'll find you need to add -n to tell it what format to output.

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

801 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question