BCP to export .csv file - 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)

BCP to export .csv file

Author

Topic

osupratt
Posting Yak Master

238 Posts

Posted - 2010-10-27 : 10:59:15

I have a View that I want to export from SQL Server 2005 to a network folder in a .csv file. This basically is sending Closed 'batch' information to a file so our accounting system (NAV) can import it and create Sales Orders/Invoices. There is something to do with SOX requirements that we can't run a SSIS job or whatever and we have to be pulling data from a .csv file.

Anyhow, I am hoping someone can help me through this as this will be the first time running BCP and I have tried from the posts and can't figure it out. I should point out that I am not the DBA, but am a Report Developer who has been assigned this. I have a test server and the DBA states that I should have all access rights to test the BCP utility. I currently am working through a remote computer.

Here is some info:
Server name= TXD-9LCC0G1
Database name= MercerDailyWorkTicket
View name= VW_WellService_CloseBatch1

I have a folder labeled 'BCP' on my C: drive that I would like to be the destination for now.

Here is the View if anyone needs to see it:

USE [MercerDailyWorkTicket]
GO
/****** Object: View [dbo].[VW_WellService_CloseBatch1] Script Date: 08/14/2009 13:33:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[VW_WellService_CloseBatch1]
AS

WITH DATA AS(
SELECT
'COM' AS LineType, hdr.Misc_Text_Field4 AS TicketBatchNum, hdr.WorkLocation, hdr.RigNumber,
'' AS DailyWorkTicketNumber,hdr.ContractNumber AS CustomerNumber,hdr.Customer,hdr.CustomerAddress,
hdr.CustomerCity,hdr.CustomerState,hdr.CustomerZip,hdr.Lease,
hdr.CustomerRep,hdr.ClosedTicketDate,'' AS MiscCode,'' AS GLSalesAcct,'' AS UnitOfMeasure,
0 Quantity,0 STDUnitPrice,'' AS TaxSchedule,'' AS TaxClass,
0 TaxRate,0 LineTotal,
( SELECT MiscCodeDesc + '; '
FROM MercerDailyWorkTicket.dbo.VW_WellService_CommentLine1 vw
WHERE vw.TicketBatchNum = hdr.Misc_Text_Field4
ORDER BY MiscCodeDesc
FOR XML PATH('') ) AS MiscCodeDesc
FROM dbo.MercerDailyWorkTicketHdr AS hdr INNER JOIN
dbo.MercerDailyWorkTicketInv AS inv ON hdr.DailyWorkTicketNumber = inv.DailyWorkTicketNumber
GROUP BY hdr.Misc_Text_Field4,hdr.WorkLocation,
hdr.RigNumber,hdr.ContractNumber,hdr.Customer,hdr.CustomerAddress,
hdr.CustomerCity,hdr.CustomerState,hdr.CustomerZip,hdr.Lease,
hdr.CustomerRep,hdr.ClosedTicketDate

UNION ALL

SELECT

'INV' AS LineType, hdr.Misc_Text_Field4 AS TicketBatchNum, hdr.WorkLocation,
hdr.RigNumber, hdr.DailyWorkTicketNumber, hdr.ContractNumber AS CustomerNumber,
hdr.Customer, hdr.CustomerAddress, hdr.CustomerCity, hdr.CustomerState, hdr.CustomerZip,
hdr.Lease, hdr.CustomerRep, hdr.ClosedTicketDate, inv.MiscCode, inv.GLSalesAcct,
inv.UnitOfMeasure,inv.Quantity, inv.STDUnitPrice, inv.TaxSchedule, inv.TaxClass, inv.TaxRate,
(inv.Quantity*inv.STDUnitPrice)*inv.TaxRate AS LineTotal,inv.Misc_Text_Field2 AS MiscCodeDesc
FROM dbo.MercerDailyWorkTicketHdr AS hdr INNER JOIN
dbo.MercerDailyWorkTicketInv AS inv ON hdr.DailyWorkTicketNumber = inv.DailyWorkTicketNumber
)
SELECT
LineType,TicketBatchNum,WorkLocation,RigNumber,CustomerNumber,Customer,CustomerAddress,
CustomerCity,CustomerState,CustomerZip,Lease,CustomerRep,ClosedTicketDate,MiscCode,
MiscCodeDesc,GLSalesAcct,UnitOfMeasure,SUM(Quantity)Quantity,STDUnitPrice,TaxSchedule,
TaxClass,TaxRate,SUM(LineTotal)LineTotal
FROM(
SELECT
LineType,TicketBatchNum,WorkLocation,RigNumber,CustomerNumber,Customer,CustomerAddress,
CustomerCity,CustomerState,CustomerZip,Lease,DailyWorkTicketNumber,CustomerRep,ClosedTicketDate,
MiscCode,MiscCodeDesc,GLSalesAcct,UnitOfMeasure,Quantity,STDUnitPrice,TaxSchedule,TaxClass,TaxRate,LineTotal
FROM DATA
) AS X
WHERE ClosedTicketDate IS NOT NULL
GROUP BY LineType,TicketBatchNum,WorkLocation,RigNumber,CustomerNumber,Customer,CustomerAddress,
CustomerCity,CustomerState,CustomerZip,Lease,CustomerRep,ClosedTicketDate,
MiscCode,MiscCodeDesc,GLSalesAcct,UnitOfMeasure,Quantity,STDUnitPrice,TaxSchedule,TaxClass,TaxRate

Again, thanks for any help you may provide and let me know if there is any other info that I need to provide!

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-10-27 : 11:41:16

what have you tried? and what results/errors did you get?

If you don't have the passion to help people, you have no passion

osupratt
Posting Yak Master

238 Posts

Posted - 2010-10-27 : 12:52:20

Running this:

bcp "MercerDailyWorkTicket.dbo.VW_WellService_CloseBatch1" OUT "c:\BCP\test.csv"

Getting this error:

Cannot use the OUTPUT option when passing a constant to a stored procedure.

Mallen
Starting Member

27 Posts

Posted - 2010-10-27 : 13:19:01

Are you running this as a batch script or from a sql procedure?

If it is a sql procedure it would be something like this:

DECLARE @sql varchar(8000)
SELECT @sql= 'bcp MercerDailyWorkTicket..VW_WellService_CloseBatch1 out c:\BCP\test.csv -c -t, -T -S'
EXEC master..xp_cmdshell @sql

That will export the file to the destination.

osupratt
Posting Yak Master

238 Posts

Posted - 2010-10-27 : 14:46:51

I have this working going to my E: drive.

DECLARE @sql varchar(8000)
SELECT @sql= 'bcp MercerDailyWorkTicket..VW_WellService_CloseBatch1 out E:\test.csv -c -t, -T -S'
EXEC master..xp_cmdshell @sql

I have a datetime column that when exported is not staying as datetime. Is there a switch for keeping the format true?

osupratt
Posting Yak Master

238 Posts

Posted - 2010-10-28 : 11:41:53

OK. I have this formatted correctly. How do I call this from a Stored Procedure and have a WITH statement something like:

WITH ClosedTicket BETWEEN DATEADD(N,-10,GETDATE()) AND
GETDATE()

???

Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources