Export a text file

Posted on 2006-06-28
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
  • 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'
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.


Author Comment

ID: 17001266

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

Accepted Solution

Aneesh Retnakaran earned 500 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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sql 2016 Integration Service connecting to 2012 3 31
Access join syntax when converting to T-SQL query 4 33
sql, case when & top 1 14 26
Acces SQL Insert Problem 6 31
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

840 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