BCP script won't send .csv file when executed

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 script won't send .csv file when executed

Author

Topic

osupratt
Posting Yak Master

238 Posts

Posted - 2011-02-18 : 11:32:11

I have a SP that should send output via a .csv file to a designated location. When I run this for the first time (CREATE) it runs and gives me records AND sends the file to the path properly. When I open up my Programability folder and right-click 'Execute Stored Procedure' it will return my records, but won't send the file. I need the file to be sent once the SP is executed. The main reason is that I have a pass-through query in MS Access that some admin's 'close' files and then 'export' and when they want to export i need the back-end stored procedure to execute and the file needs to be sent. Any help would be much appreciated. I am strugglind with this. Here is the script:

USE [MercerDailyWorkTicket]
GO
/****** Object: StoredProcedure [dbo].[SP_WellService_CloseBatchBCPDetail_MWS] Script Date: 08/11/2009 12:01:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SP_WellService_CloseBatchBCPDetail_MWS]
AS
BEGIN

SELECT *
FROM VW_WellService_OpenBatchBCPDetail_MWS
ORDER BY TicketBatchNum,FTNumber,Resource
END
GO
EXEC dbo.SP_WellService_CloseBatchBCPDetail_MWS
GO

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

GO

KlausEngel
Yak Posting Veteran

85 Posts

Posted - 2011-02-18 : 11:50:56

[code]
DECLARE @FileName varchar(50),
@bcpCommand varchar(2000)

SET @FileName = REPLACE('e:\testWellServiceMWSdetail.csv','/','-')

SET @bcpCommand = 'bcp "EXEC SP_WellService_CloseBatchBCPDetail_MWS" queryout "'
SET @bcpCommand = @bcpCommand + @FileName + '" -T -c'

EXEC master..xp_cmdshell @bcpCommand
[/code]

osupratt
Posting Yak Master

238 Posts

Posted - 2011-02-21 : 15:19:39

thanks for the response, but i'm not sure where to put this code at? Where would it go in the script that i originally posted? thanks for your help.

osupratt
Posting Yak Master

238 Posts

Posted - 2011-02-23 : 12:47:40

I tried this and it didn't send the file (or replace the existing one). Has anyone ever done the BCP to send a file on a twice daily basis? I can't use SSIS. Help please. I need to create a SP that I can call through a MS Access query that will run and send a file to an existing location and which replaces any existing file.

Thanks......

USE [MercerDailyWorkTicket]
GO
/****** Object: StoredProcedure [dbo].[SP_WellService_CloseBatchBCPDetail_MWS2] Script Date: 08/11/2009 12:01:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SP_WellService_CloseBatchBCPDetail_MWS2]
AS
BEGIN

SELECT *
FROM VW_WellService_OpenBatchBCPDetail_MWS
ORDER BY TicketBatchNum,FTNumber,Resource
END
GO
EXEC dbo.SP_WellService_CloseBatchBCPDetail_MWS2
GO

DECLARE @FileName varchar(50),
@bcpCommand varchar(2000)

SET @FileName = REPLACE('e:\testWellServiceMWSdetail.txt','/','-')

SET @bcpCommand = 'bcp "EXEC SP_WellService_CloseBatchBCPDetail_MWS2" queryout "'
SET @bcpCommand = @bcpCommand + @FileName + '" -T -c'

EXEC master..xp_cmdshell @bcpCommand

GO

osupratt
Posting Yak Master

238 Posts

Posted - 2011-02-23 : 13:09:15

Sorry, just wanted to point out that I had to change the file type from .csv to .txt because of our accounting system dataport import set-up. the file needs to be opened in NotePad, but when I try an open it looks like a bunch of squares across the page. Would anyone know of why this might be? Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources