Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 5157
  • Last Modified:

Insert failed... Arithabort

Ive seen lots of posts here regarding "insert failed because the following Set options have the incorrect settings: 'Arithabort' ".

I use Indexed Views because Im replicating to a denormalized table so the schema is different on the Subscriber than the Publisher. I know that the above setting needs to be set in its own batch in the create of the Indexed View so thats what I do:

SET QUOTED_IDENTIFIER ON
go
SET ANSI_NULLS ON
go
set arithabort on
go
alter  view vu_ReplTransDTL
with schemabinding
as
select
bla bla bla

Now this works to insert data directly  in the table that the view references either in Enterprise Manager or a Stored Proc. But it doesnt work if I try to DTS data into the table from another source. Or if I try to use Replication to poulate from another source. I still get the same error message under these circumstances. Ive read that ARITHABORT needs to bet set ON during the createion of the Procs that insert data into the table. But how can that happen for DTS or Replication? Do people just not use these tools for a scenario like this?

TIA, ChrisR

 
0
rseraji
Asked:
rseraji
  • 4
  • 3
  • 2
1 Solution
 
AaronAbendCommented:
This is just a guess, but the DTS may convert empty values to zero instead of null, which might cause the problem.  select 10/0 gives an error, select 10/null returns a null without an error
0
 
AaronAbendCommented:
More - check the "keep null values" in the options of your transformation. That might fix it
0
 
rserajiAuthor Commented:
Im really looking more to find if this is always going to be a problem with every tool I ever use to load data into the db?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
AaronAbendCommented:
Assuming the problem is nulls being converted to zeros, there are some business issues in just letting them go. However, another approach woud be to put a trigger on the underlying table to convert inbound zeros to nulls.

I need more info to provide any more meaningful answer. Essential table structures, etc.
0
 
rserajiAuthor Commented:
I tried the "keep null values" idea and it still happens.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
Actually, I use DTS quite a lot, however I NEVER load directly to the final table(s). I ALWAYS load data to some staging tables first, do my validation and then load to the finaly tables.

For you, the solution using the SET ARTIHTABORT ON is exactly there. create a stored procedure that transfers the data from the staging tables to the final tables, which will be perfectly able to use the required setting !!!

Cheers
0
 
rserajiAuthor Commented:
I appreciate your response. But how does anyone live with this? If I was only going to do one or two tables this way I could probably manage. But I need to do this on 30 - 40 tables. What do I do if I want to use Replication? Or what if I need to populate from another unforseen source? Are these actions always going to be required?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
Actually, if you have to do it for more that 1-2 tables, you should start developing/using some scripts to "automate" this kind of processing. That's what people do when they work every with the same things over and over again.
QUOTE: lazy people are rarly silly :-)
0
 
rserajiAuthor Commented:
I guess the problem that Im having here is all the potential tools I would need to rule out because of this setting. Basically, I could never use any GUI because of this. Think I'll look into using Customer Sync Objects for my Replication before I commit too much to this way of doing things. Thanks.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

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