BCP stored procedure - 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 2000 Forums

Transact-SQL (2000)

BCP stored procedure

Author

Topic

scelamko
Constraint Violating Yak Guru

309 Posts

Posted - 2007-12-13 : 14:17:55

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

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-12-13 : 14:23:14

You aren't using bcp in your stored procedure. What you are doing is just outputting the results of a query to a file using sqlcmd. Just use > instead of >> if you want to create/overwrite the file.

BTW, > and >> are DOS things and not part of sqlcmd or SQL Server. If you want to output using sqlcmd without using > or >>, then you can use the -o switch.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

scelamko
Constraint Violating Yak Guru

309 Posts

Posted - 2007-12-13 : 14:51:36

If I use > instead >> the data.txt stores the results only for last 'name' from the cursor. And hence I just want to delete the contents of file before it goes while loop.

Is there any way to achieve this?

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-12-13 : 14:58:10

Use > for the first one and then >> for the second. > means to create a new file or overwrite if it exists, >> means to append.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

scelamko
Constraint Violating Yak Guru

309 Posts

Posted - 2007-12-13 : 15:21:13

tkizer,

The below loop runs any where between 60 to 110 times

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

At which point do you want me to split the loop, any ideas??

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-12-13 : 15:28:06

I'm very confused on what you want. You said you want new files to be created, so use > and specify a unique name.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

scelamko
Constraint Violating Yak Guru

309 Posts

Posted - 2007-12-13 : 15:56:41

tkizer,

First sp_generate_inserts generate inserts for all the table names from the cursor.

Assume I have 51 tables and 51st table name is employee

If > is used instead of >> insert statements are generated only for table employee, since insert statements for all the 50 tables prior to employee table are overwritten.

I hope this clears up the confusion.

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-12-13 : 16:00:08

Like I said you need to provide a unique file name for each pass through the loop.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources