• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1274
  • Last Modified:

Export a text file

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


output:

usage: bcp {dbtable | query} {in | out | queryout |
format} datafile
[-m maxerrors] [-f formatfile] [-e
errfile]
[-F firstrow] [-L lastrow] [-b
batchsize]
[-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
packetsize]
[-S server name] [-U username] [-P
password]
[-T trusted connection] [-v version] [-R
regional enable]
[-k keep null values] [-E keep identity values]
[-h "load hints"]
NULL
0
earngreen
Asked:
earngreen
  • 2
  • 2
  • 2
  • +1
1 Solution
 
earngreenAuthor Commented:
Nor does it export the text file.
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
you have to specify the Servername and the dbname

SET @bcpCommand = 'bcp "SELECT * FROM database.dbo.mytable" queryout "'
0
 
rafranciscoCommented:
There is no -password option for the bcp.  Try removing that and see if it works:

SET @bcpCommand = @bcpCommand + @FileName + '" -U username -P pw'
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
earngreenAuthor Commented:
rafrancisco

 I just used username and password as an example.
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
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
0
 
rafranciscoCommented:
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.
0
 
mastooCommented:
And after you make those two changes, I think you'll find you need to add -n to tell it what format to output.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 2
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now