CODEWITHSUNDEEP
sqlsql-serverbcpxp-cmdshell
Mike D
Mike D

Reputation: 179

BCP Parameter issues

I Hope I'm not re-asking.. but..

here's the pertinent section of my SQL. The view in question (vw_TempALlItems) is created in prior steps, and yes its created using column names, and paying attention to types & etc.. The view works 100%.. But the BCP, not so much..

---------------------------------------------------------------
DECLARE @bcpCommand varchar(2000)

SET @bcpCommand = 'bcp vw_TempAllItems out
"c:\FEPData.csv" -c -t -U USER -P PWD'

EXEC master..xp_cmdshell @bcpCommand
GO

Drop View vw_TempAllItems
GO
----------------------------------------------------------------

The only thing I get is the results pane shows the BCP command parameters 

    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"]         [-x generate xml format file]
    [-d database name]
    NULL

and the CSV file is NOT created..

Anyone?

Upvotes: 1

Views: 2346

Answers (2)

Mike D
Mike D

Reputation: 179

For anyone who may care here's the end result which works well

    DECLARE @bcpCommand varchar(2000)
    SET @bcpCommand = 'bcp ' + DB_NAME() + '..vw_TempAllItems out c:\FEPData.csv -c -t, -U User -P Pwd  -S' + @@SERVERNAME
    EXEC master..xp_cmdshell @bcpCommand
    GO

Upvotes: 1

coge.soft
coge.soft

Reputation: 1674

For one, try making it a query:

SET @bcpCommand = 'bcp "SELECT * FROM vw_TempAllItems" out
"c:\FEPData.csv" -c -t -U USER -P PWD'

Also, to answer your question, check out: How to pass parameter to a bcp command in sql server on stackoverlow.

Upvotes: 1

Related Questions