Solved

Insert failed... Arithabort

Posted on 2004-08-09
9
5,092 Views
Last Modified: 2008-01-09
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
Comment
Question by:rseraji
  • 4
  • 3
  • 2
9 Comments
 
LVL 10

Expert Comment

by:AaronAbend
Comment Utility
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
 
LVL 10

Expert Comment

by:AaronAbend
Comment Utility
More - check the "keep null values" in the options of your transformation. That might fix it
0
 
LVL 3

Author Comment

by:rseraji
Comment Utility
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
 
LVL 10

Expert Comment

by:AaronAbend
Comment Utility
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 3

Author Comment

by:rseraji
Comment Utility
I tried the "keep null values" idea and it still happens.
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
Comment Utility
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
 
LVL 3

Author Comment

by:rseraji
Comment Utility
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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
 
LVL 3

Author Comment

by:rseraji
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

APEX (Application Express) is used to develop a web application from Oracle. SQL Workshop is one of the tools that comes with Oracle APEX to query or modify the database objects or to make any changes to the structure.
Read about achieving the basic levels of HRIS security in the workplace.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now