Solved

Insert failed... Arithabort

Posted on 2004-08-09
9
5,143 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
9 Comments
 
LVL 10

Expert Comment

by:AaronAbend
ID: 11754705
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
ID: 11754749
More - check the "keep null values" in the options of your transformation. That might fix it
0
 
LVL 3

Author Comment

by:rseraji
ID: 11755704
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 10

Expert Comment

by:AaronAbend
ID: 11755817
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
 
LVL 3

Author Comment

by:rseraji
ID: 11756554
I tried the "keep null values" idea and it still happens.
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 11760170
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
ID: 11763973
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 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 11763991
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
ID: 11764400
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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
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…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

617 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