How do I set this option without SQL Server? (Arithabort)

[idbands]

We have a 3rd party application that we use that uses SQL Server. I don't know too much about SQL Server; I think we only have the client version installed?

I can access the SQL Server tables through Microsoft Access (via the SQL Server ODBC driver) and run lots of queries and updates which I do often.

Currently, I have run into an issue though. I need to insert a record into a table and I am getting an error about Arithabort not allowing me to do the insert. I've been told and read online that I just need to set Arithabort to "OFF" somewhere. . .where/how do I do this?? I think it's somewhere in the ODBC connection setup but I'm having no luck.

Can anyone help?

 

[Remou]

It seems likely that there is an error in your SQL. Perhaps you could post it here? In general, Arithabort is a good thing, from what I understand.

http://lessthandot.com

 

[idbands]

Here's the basic insert statement I have (see below). I talked to a developer of the 3rd party application and he said "On to the ARITHABORT problem - "SET ARITHABORT ON" needs to be executed on the connection being used. I'm not familiar enough with Access, can that be done in your scenario?"

But now I'm really confused as I thought the default was "ON"??

insert into dbrders
(OrderNumber,StoreID,CustomerID,OrderDate,ShipFirstName,ShipLastName,
ShipCompany,ShipAddress1,ShipAddress2,ShipAddress3,ShipCity,
ShipStateProvinceCode,ShipPostalCode,ShipCountryCode,ShipEmail,ShipPhone,
ShipFax,BillFirstName,BillLastName,BillCompany,BillAddress1,BillAddress2,
BillAddress3,BillCity,
BillStateProvinceCode,BillPostalCode,BillCountryCode,BillEmail,BillPhone,
BillFax,CustomerComments,RequestedShipping,Total,Notes,Status,IsManual,
OrderNumberPrefix,OrderNumberPostfix,OnlineLastModified,eBayOrderID,
eBayOrderCreated,eBayBuyerID,eBayBuyerFeedbackScore,
eBayBuyerFeedbackPrivate,eBayPaymentStatus,
eBayPaymentMethod,eBayCheckoutStatus,eBayCompleteStatus,eBayLeftFeedback,
eBayLeftFeedbackType,eBayLeftFeedbackComments,eBayReceivedFeedbackType,
eBayReceivedFeedbackComments,eBayMyEbayStatus,eBaySellerPaidStatus,
eBayAllowEdit,eBaySellingManagerRecord,PayPalAddressStatus,
PayPalTransactionID,
MarketWorksUserNumber,MarketWorksInvoiceNumber,MarketWorksBuyerNumber,
MivaBatchID,MarketWorksParcelID,osCommerceCustomerID,osCommerceStatusCode,
YahooOrderID,ProStoresStatus,ProStoresConfirmation,ChannelAdvisorReportID,
ChannelAdvisorResellerID,ChannelAdvisorDistributionCenter,
InfopiaCustomerID,InfopiaStatus,AmazonOrderID,
AmazonStatusDocumentID,AmazonCommission,XCartStatus,OrderMotionShipmentID)

Values

(107299,4,82214,'01/06/2009',"Jane","Smith","","100 Main Street","","","Cranbrook","British Columbia","V1C6W1","CA","janesmith@hotmail.com","555-555-5555","","Jane",
"Smith","","500 Main Street","","","Cranbrook","British Columbia","V1C6W1","CA","janesmith@hotmail.com","555-555-5555","","",
"USPS Global Airmail",57.35,"","",0,"","",'01/06/2009',"0",'01/06/2009',"",0,0,0,0,0,0,0,0,"",0,"",0,-1,0,0,0,"",0,"",0,0,0,0,0,"","",
"",0,"","",0,"","","0",0,"",0)

I did a ton of copying and pasting of field names for the statement and triple counted all of the fields and values to make sure everything matched up. It shouldn't be a misspelling of one of the fields. Not sure what else it could be and I'm having a horrible time troubleshooting as all fields are required in order for the insert statement to be executed.

 

[Remou]

You have a lot of zero-length strings ("") these could well be a problem. Are you sure that all fields are required? Perhaps the zero-length string fields could be skipped?

http://lessthandot.com

 

[idbands]

I finally made a little more progress, however, I'm still stuck in the same place. I copied and pasted the (structure, not data) and tried my "insert" on that table and it works. But when I try the same statement on the original table, I get the same Arithabort issue.

Any ideas?

 

[Remou]

According to

http://msdn.microsoft.com/en-us/library/aa259212(SQL.80).aspx

[tt]SET ARITHABORT

Terminates a query when an overflow or divide-by-zero error occurs during query execution.[/tt]

As both these things should be avoided, setting arithabort off should not be the answer. It is possible that there is a trigger of some description on one or more of the fields and that is causing the problem.

You say that all fields are required, if that is the case, "" may be a problem, in that "" can be converted to null, it is also possible that 0 is not allowedin some fields.

It may be worth trying an insert that does not include fields for which you do not have a value, if nothing else, you can see if you get a differet error.

insert into dbo_Orders (OrderNumber,
StoreID,
CustomerID,
OrderDate,
ShipFirstName,
ShipLastName,
ShipCompany,
ShipAddress1,
ShipAddress2,
ShipAddress3,
ShipCity,
ShipStateProvinceCode,
ShipPostalCode,
ShipCountryCode,
ShipEmail,
ShipPhone,
BillFirstName,
BillLastName,
BillAddress1,
BillCity,
BillStateProvinceCode,
BillPostalCode,
BillCountryCode,
BillEmail,
BillPhone,
RequestedShipping,
Total,
IsManual,
OnlineLastModified,
eBayOrderID,
eBayOrderCreated,
eBaySellerPaidStatus,
eBayAllowEdit)

Values (107299,
 4,
 82214,
 '01/06/2009',
 "Jane",
 "Smith",
 "",
 "100 Main Street",
 "",
 "",
 "Cranbrook",
 "British Columbia",
 "V1C6W1",
 "CA",
 "janesmith@hotmail.com",
 "555-555-5555",
 "Jane",
 "Smith",
 "500 Main Street",
 "Cranbrook",
 "British Columbia",
 "V1C6W1",
 "CA",
 "janesmith@hotmail.com",
 "555-555-5555",
 "USPS Global Airmail",
 57.35,
 0,
 '01/06/2009',
 "0",
 '01/06/2009',
 -1,
 0)