Questions about BCP - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Questions about BCP

Author

Topic

jbates99
Constraint Violating Yak Guru

396 Posts

Posted - 2011-04-27 : 12:39:44

-- Construct report path and name

Set @ReportPath = '\\pncllwappco\E$\MESABA\OUT\' + 'INV' + '_' + Convert(varchar(8), @curDate, 112) + '.csv'

EXEC master..xp_cmdshell 'bcp "SELECT PO_NUMBER as [PO NUMBER], LINE_NBR as [PO LINE] from PROD.dbo.MAINVDTL" queryout @ReportPath -SPNCLLWDBCO\ -T -t, -r\r\n -c'

I ran this in the Query window. It seemed to run successfully but i cannot find the output file.
I'm running this from the database server (PNCLLWDBCO) but directing the out file to the application server PNCLLWAPPCO E drive.

** When using bcp, can the output path be a variable?

Thanks, John

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-04-27 : 13:01:42

>> ** When using bcp, can the output path be a variable?
No - it is executing the string as a dos command. It doesn't parse the string

declare @sql varchar(1000)
select @sql = 'bcp "SELECT PO_NUMBER as [PO NUMBER], LINE_NBR as [PO LINE] from PROD.dbo.MAINVDTL" queryout ' + @ReportPath + ' -SPNCLLWDBCO -T -t, -r\r\n -c'
EXEC master..xp_cmdshell @sql

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jbates99
Constraint Violating Yak Guru

396 Posts

Posted - 2011-04-27 : 14:26:02

Thanks nigelrivett.

I ran this in the Query window:

Set @ReportPath = 'C:\Reports\' + 'INV' + '_' + Convert(varchar(8), @curDate, 112) + '.csv'

declare @sql varchar(1000);
select @sql = 'bcp "SELECT PO_NUMBER as [PO NUMBER], LINE_NBR as [PO LINE] from PROD.dbo.MAINVDTL" queryout ' + @ReportPath + ' -T';
EXEC master..xp_cmdshell @sql;

Results:
- the Results section shows this message "Enter the file storage type of field PO NUMBER [char]:" ??

- it DID create the csv file INV_20110427.csv but it is empty

Thanks for any tips.
John

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-04-27 : 14:40:19

Add "-c" to you command, without quotes.

jbates99
Constraint Violating Yak Guru

396 Posts

Posted - 2011-04-27 : 15:17:15

Thanks, Rob.

Adding -c worked.

Just have 2 problems to resolve:

When I open the .csv file, both data elemts (PO Number and Line Number) are in the same column. It looks like this....

IP00139686o8

1. I need the data to be in separate columns, that's why I chose bcp

2. In looking up bcp in BOL, I didn't see a switch to output column headings. Is there a switch to have bcp include the column headings...
SELECT PO_NUMBER as [PO NUMBER], LINE_NBR as [PO LINE] ?

I need the file to have column headings like:
PO NUMBER PO LINE

Thanks very much. John

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-04-27 : 15:22:15

You lost the -t, from your original post.

There's no easy way to get column headings using bcp. However you can get column headings using sqlcmd. However again, you also get a dividing line that can't be removed without removing the headers.

jbates99
Constraint Violating Yak Guru

396 Posts

Posted - 2011-04-27 : 18:00:29

Thanks, Rob.

select @sql = 'bcp "SELECT PO_NUMBER as [PO NUMBER], LINE_NBR as [PO LINE],ceiling(ENTERED_QTY) as [INVOICE QUANTITY], TOT_BASE_AMT as [INVOICE PRICE] from PROD.dbo.MAINVDTL where COMPANY=200" queryout ' + @ReportPath + ' -T -c';
EXEC master..xp_cmdshell @sql;

This creates a tab-delimited file.

I hope the recipients do not insist on having column headings.

Yes, I originally tried sqlcmd, which did give me column headings but the output was all in the same column. Another poster said the only way to split the data was to use bcp.

You have been a tremendous help. Thanks. John

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources