• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 221
  • Last Modified:

got error when bcp stored procedure to file

i got this stored procedure:
create procedure p
as
declare @a int,
      @b int,
      @c numeric(4,2)


set @a = 0
set @b = 0
set @c = 0

if ((@a + @b) = 0)
   begin
      set @c = 0
   end
else
   begin
      set @c = cast(@a as numeric)/(@a + @b)
   end
select @c
go

it works fine.( have a try)

but when i bcp it to a excel file:
Exec master..xp_cmdshell 'bcp "exec TransactCIB.dbo.p" queryout c:\g.xls  -c -q -S"(local)" -U"sa" -P""'

I got error:
SQLState = 22012, NativeError = 8134
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]encouter error when divided by zero
NULL

why is that? i work on sql server2000, and windows xp
0
thotwielder
Asked:
thotwielder
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I confirm that I got the same error.
here a workaround suggestion:

create procedure p
as
set nocount on
declare @a int,
      @b int,
      @c numeric(4,2)


set @a = 0
set @b = 0
set @c = 0

set @c = case when (@a + @b) = 0 then 0 else  cast(@a as numeric)/(@a + @b) end
select @c
go

Exec master..xp_cmdshell 'bcp "exec master.dbo.p" queryout c:\g.xls  -c -q -S"(local)" -U"sa" -P""'

go

drop procedure p
0
 
thotwielderAuthor Commented:
ok, thanks angelIII. it works. but i wonder why my approach didn't work, a bug or something?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I guess it is a bug, but found nothing on the bug site...
maybe no-one has found that yet :-)
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now