Post reply

arithabort error when queying a view

  • June 16, 2009 at 2:44 am

    #73720

    Hi

    Newbie here i'm afraid -

    I have a view in SQL 2000 that works (ie displays without error in Ent Manager). However when I try and query the view in excel - I get the following message "select failed because the following SET options have incorrect settings ARITHABORT Statements(s) could not be prepared"

    Any ideas/suggestions - this view used to work prior to a software/database update issues by the software providers whoose database I am querying.

    The view itself is not using arithabort

    Thanks in advance for any

    Guy

  • June 16, 2009 at 3:50 am

    #1010080

    Please post the code.

    karthik

  • June 16, 2009 at 3:56 am

    #1010082

    Here's the code for the view:

    SELECT ISNULL(dbo.Contact.FName + ' ', '') + ISNULL(dbo.Contact.LName, ' ') AS companyFullName, dbo.ClientSupplier.ClientID, dbo.Contact.ContactID,

    dbo.ContactType.ContactTypeID, dbo.ContactType.ContactType, dbo.ClientSupplier.CreatedDate, dbo.ClientSupplier.ParentClientID,

    dbo.ClientSupplier.DefaultRecoveryRate, dbo.ClientSupplier.Notes, dbo.ClientSupplier.OfficeID, dbo.ClientSupplier.DepartmentID,

    dbo.ClientSupplier.ClientCode, dbo.ClientSupplier.PeriodEndDate, dbo.ClientSupplier.Vattable, dbo.ClientSupplier.CliFileID, dbo.ClientSupplier.Internal,

    dbo.Contact.RespNotes, dbo.ClientSupplier.ClientVATTypeID, dbo.ClientSupplier.ZID, dbo.ClientSupplier.CreatedBy, dbo.ClientSupplier.DateCreated,

    dbo.ClientSupplier.DefaultCurrency, dbo.ClientSupplier.Closed, dbo.Contact.Pref, dbo.Contact.FName, dbo.Contact.Mname, dbo.Contact.LName,

    dbo.Contact.Tel, dbo.Contact.Fax, dbo.Contact.Mobile, dbo.Contact.Address1, dbo.Contact.Address2, dbo.Contact.Address3, dbo.Contact.Town,

    dbo.Contact.County, dbo.Contact.PostCode, dbo.Contact.Country, dbo.Contact.EMail, dbo.Contact.Suff, dbo.Contact.Sex, dbo.Contact.MailingName,

    dbo.Contact.Initials, dbo.Contact.FName AS Expr1, dbo.CurrentContactPartner.PartnerID, dbo.CurrentContactPartner.FNameTemp AS PartnerFirstName,

    dbo.CurrentContactPartner.SNameTemp AS PartnerSurname, dbo.Contact.Salutation, dbo.Contact.DateOfBirth, dbo.ClientUnBilledWip.UnBilledWip,

    dbo.ClientUnBilledWip.UnPostedWIP, dbo.vwBPSPCDebtors.SumOfCSumUnBilled AS Debtors, dbo.vwBPSPCVAT.CustomValue AS VAT,

    dbo.vwBPSPCNINumber.CustomValue AS NINumber, dbo.vwBPSPCPAYEref.CustomValue AS PAYEref,

    dbo.vwBPSPCDateOfIncorporation.CustomValue AS DateOfIncorporation, dbo.vwBPSPCCoHouseCode.CustomValue AS CoHouseCode,

    dbo.vwBPSPCCoNumber.CustomValue AS CoNumber, dbo.vwBPSPCCoHouseAuthCode.CustomValue AS CoHouseAuthCode,

    dbo.vwBPSPC64_8Code.CustomValue AS [64_8Code], dbo.vwiPSContactsalutation.CustomValue AS CustomSalutation, dbo.Employee.EmployeeID,

    dbo.Employee.EmployeeCode, dbo.vwIPSPerTAX.USERCODE, dbo.vwIPSPerTAX.HMRC_Ref, dbo.vwIPSPerTAX.UTR, dbo.vwIPSPerTAX.TAXYEAR,

    dbo.vwIPSPerTAX.HMRC_Name, dbo.vwIPSPerTAX.HMRC_Address, dbo.vwIPSPerTAX.HMRC_Phone, dbo.vwIPSPerTAX.HMRC_Fax,

    dbo.vwIPSPerTAX.HMRC_Postcode, dbo.vwIPSPerTAX.TOTAL_TAXDUE, dbo.vwIPSPerTAX.FIRST_PAYMENT, dbo.vwIPSPerTAX.[2ND_PAYMENT],

    dbo.vwIPSPerTAX.DOB, dbo.vwIPSPerTAX.NI_NUM

    FROM dbo.ClientUnBilledWip RIGHT OUTER JOIN

    dbo.vwIPSPerTAX RIGHT OUTER JOIN

    dbo.ClientSupplier ON dbo.vwIPSPerTAX.USERCODE = dbo.ClientSupplier.ClientCode RIGHT OUTER JOIN

    dbo.Contact ON dbo.ClientSupplier.ContactID = dbo.Contact.ContactID LEFT OUTER JOIN

    dbo.vwiPSContactsalutation ON dbo.Contact.ContactID = dbo.vwiPSContactsalutation.ContactID LEFT OUTER JOIN

    dbo.vwBPSPCDateOfIncorporation ON dbo.Contact.ContactID = dbo.vwBPSPCDateOfIncorporation.ContactID LEFT OUTER JOIN

    dbo.vwBPSPCVAT ON dbo.Contact.ContactID = dbo.vwBPSPCVAT.ContactID LEFT OUTER JOIN

    dbo.vwBPSPCPAYEref ON dbo.Contact.ContactID = dbo.vwBPSPCPAYEref.ContactID LEFT OUTER JOIN

    dbo.vwBPSPCNINumber ON dbo.Contact.ContactID = dbo.vwBPSPCNINumber.ContactID LEFT OUTER JOIN

    dbo.vwBPSPCCoNumber ON dbo.Contact.ContactID = dbo.vwBPSPCCoNumber.ContactID LEFT OUTER JOIN

    dbo.vwBPSPCCoHouseCode ON dbo.Contact.ContactID = dbo.vwBPSPCCoHouseCode.ContactID LEFT OUTER JOIN

    dbo.vwBPSPCCoHouseAuthCode ON dbo.Contact.ContactID = dbo.vwBPSPCCoHouseAuthCode.ContactID LEFT OUTER JOIN

    dbo.vwBPSPC64_8Code ON dbo.Contact.ContactID = dbo.vwBPSPC64_8Code.ContactID LEFT OUTER JOIN

    dbo.vwBPSPCDebtors ON dbo.Contact.ContactID = dbo.vwBPSPCDebtors.ContactID ON

    dbo.ClientUnBilledWip.ContactID = dbo.Contact.ContactID LEFT OUTER JOIN

    dbo.Employee INNER JOIN

    dbo.CurrentContactPartner ON dbo.Employee.EmployeeID = dbo.CurrentContactPartner.EmployeeID ON

    dbo.Contact.ContactID = dbo.CurrentContactPartner.ContactID LEFT OUTER JOIN

    dbo.ContactType ON dbo.Contact.ContactTypeID = dbo.ContactType.ContactTypeID

    WHERE (dbo.ClientSupplier.ClientSupplierType = 1)

  • June 16, 2009 at 5:28 am

    #1010099

    Add SET ARITHABORT ON to the query you call from Excel.

    _____________
    Code for TallyGenerator

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply