Solved

Insert failed... Arithabort

Posted on 2004-08-09
9
5,137 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
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!

 
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

Industry Leaders: 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!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
mask value when navigating 5 24
Assigning Database Principals to Database Roles 3 41
Filemaker question - Daily Task 5 48
TDE for SQL Web Edition 1 42
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
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…

752 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