BCP Stored Procedure

Question

Post reply

BCP Stored Procedure

am-244616

SSCarpal Tunnel

Points: 4936
More actions
December 13, 2007 at 12:18 pm

#184999

Hi
I have following stored procedure where adds the results of the stored procedure to data.txt file. Is there any BCP command to delete the file and create a new file each time when I run the stored procedure instead of appending to the same file each time
ALTER PROCEDURE [dbo].[INSERTDML]
AS
BEGIN
declare C_sql cursor for
select name from aicms_meta_tab
WHERE issdf = 'Y'ORDER BY name
DECLARE @cmd varchar(2000)
DECLARE @TAB_NAME VARCHAR(50)
DECLARE @FETCHCOUNT INT
DECLARE @TAB_NAME_PREV VARCHAR(20)
DECLARE @cmd1 varchar(2000)
set @tab_name = ''
set @tab_name_prev = ''
set @fetchcount = 0
open C_sql fetch next from C_sql into @tab_name
WHILE @@fetch_status<>-1
BEGIN
set @cmd1 = 'sqlcmd -E -dDEV -SNV-DEVSQL3 -Q"exec [DELETE] '+@TAB_NAME+'" -W >>\\dt-sc2\shares\Data.txt'
set @cmd = 'sqlcmd -E -dDEV -SNV-DEVSQL3 -Q"exec SP_GENERATE_INSERTS '+@TAB_NAME+'" -W >>\\dt-sc2\shares\Data.txt'
select @cmd1 -- + '...'
EXEC master.dbo.xp_cmdShell @cmd1
select @cmd -- + '...'
EXEC master.dbo.xp_cmdShell @cmd
set @fetchcount=@fetchcount+1
fetch next from C_sql into @tab_name
END
close c_sql
deallocate c_sql
END
Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 1

[font="Courier New"]SET @Cmd = 'DEL \\dt-sc2\shares\Data.txt'

EXEC Master.dbo.xp_CmdShell @Cmd[/font]

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

peterhe SSChampion Points: 11469 More actions · Answer 2

if the file exists, bcp jsut overwrites it. So you don't need to delete it

TimothyAWiseman SSCrazy Eights Points: 8819 More actions · Answer 3

Jeff Moden (12/13/2007)
[font="Courier New"]SET @Cmd = 'DEL \\dt-sc2\shares\Data.txt'
EXEC Master.dbo.xp_CmdShell @Cmd[/font]

If you are a stickler for avoiding error messages like I am you could add xp_fileexists to Jeff's piece to avoid deleting things that don't exists. For instance:

declare @exists int

exec sys.xp_fileexist '\\dt-sc2\shares\Data.txt', @exists OUTPUT

if @exists = 1

begin

SET @Cmd = 'DEL \\dt-sc2\shares\Data.txt'

EXEC Master.dbo.xp_CmdShell @Cmd

end

---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 4

peterhe (12/14/2007)
if the file exists, bcp jsut overwrites it. So you don't need to delete it

Not when you use ">>" for redirection to the log file...

Of course, if the OP were to use just ">" for very first write to the log file outside the loop, the problem would take care of itself and the delete would not be necessary.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions