bcp error handling - 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

SQL Server Administration (2000)

bcp error handling

Author

Topic

rubs_65
Posting Yak Master

144 Posts

Posted - 2003-12-17 : 10:48:18

Hi,

We are writing a procedure in which we use bcp to archive the data out of tables and then delete data from these tables. At any point if we get error we want procedure to return with failure status. If we get error during bcp operation then it will just return back and leave the files on hard disk and if we get error during delete operation then it will rollback the transaction and return. Now we are trying to simulate the failure that bcp fails with insufficient space on hard disk and I can see that it is not returning immediately when it encountered the first error and keep trying to archive the tables with 0 bytes file on hard disk for each table.....How to return immediately with first error.

Thanks
--Harvinder

Following is the sample code of procedure:

create procedure bcpout1 (@interval int,@status int output)
as
set nocount on
declare @sqlstmt nvarchar(1000)
declare @sql1 varchar(8000)
declare @var1 varchar(100)
declare @result int
declare c1 cursor for select distinct col_name from t_table1 where interval = @interval
open c1
fetch next from c1 into @var1
while (@@fetch_status = 0)
begin
SET @SQLStmt = N'IF EXISTS (SELECT 1 FROM dbo.sysobjects WHERE id = object_id(''bcpview'')) DROP view bcpview'
EXEC sp_executesql @SQLStmt
select @str1 = N'create view bcpview as SELECT top 100 percent pv.* FROM db1..' + @var11 + ' pv inner join db1..t_table1 au on pv.col2=au.col2 where au.interval=' + cast (@interval as char (5))'
exec (@str1)
select @sql1 = 'bcp "db1..bcpview" out c:\temp\' + @tab1 + cast (@interval as char (5)) + '.txt -c -Sserver1'
exec @result = master.dbo.xp_cmdshell @sql1
if (@result <> 0)
begin
close c1
deallocate c1
set @status = -99
return
end
fetch next from c1 into @var1
end
close c1
deallocate c1
declare c1 cursor for select distinct col_name from t_table1 where interval = @interval
begin tran
open c1
fetch next from c1 into @var1
while (@@fetch_status = 0)
begin
select @str1 = N'delete FROM db1..' + @var1 + ' where col2 in (select col2 from db1..t_table1 au where au.interval=' + cast (@interval as char (5)) + ' and view =' + + cast (@var1 as char (5)) +')'
exec (@str1)
if (@@error <> 0)
begin
select @status = -99
rollback tran
close c1
deallocate c1
return
end
fetch next from c1 into @var1
end
close c1
deallocate c1
commit tran
select @status = 0

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources