March 31, 2009 at 8:29 am
Hellow, I have a proc that was running fine. I decided to add a param of type XML wich I user to inser into a temp table. Later at the end of the proc I use that to join to a view to return my results.
The strange thing is that I can call this proc no problem from SSMS and I get proper results. When I run it from my C# code I get the following:
INSERT failed because the following SET options have incorrect settings: 'ARITHABORT'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or query notifications and/or xml data type methods."
? ex.Message
I have tried setting ARITHABORT On, OFF and no change. Bellow is my proc:
ALTER PROCEDURE [dbo].[get_lcm_laneclosure_Filter]
@Laneclosure_IDint = null,
@IncludeInactiveRequestSOurcesbit = null,
@Closure_Type_IDint = null,
@RequestSOurcesIDsXML = null,
@IncludeSpecialEventsbit = null ,
@DaytimeClosuresOnlybit = null,
@Status_IDint = null,
@RoadtobeClosed_IDint = null,
@BeginMPdecimal(6,3) = null ,
@EndMPdecimal(6,3) = null ,
@BICint = null,
@DirectionIDint = null,
@StartDateDateTime = null,
@EndDateDateTime = null,
@DateEnteredStartDateTime = null,
@DateEnteredEndDateTime = null
AS
Create Table #ResourceIDs
(requestsource_pk int)
Create Table #laneclosures
(IDint)
Insert into #ResourceIDs
Select T.c.value('RSourceID[1]','int')
From @RequestSOurcesIDs.nodes('/RSources/RSource') as T(c)
Insert into #laneclosures(ID)
SELECT laneclosure_pk
from dbo.[LCM_V_LaneClosureNotices_All] LCN
Left Join dbo.LCM_LaneClosure_DirectionAffected DA on LCN.laneclosure_pk = DA.laneclosure_Fk
Where
LCN.laneclosure_pk = coalesce(Nullif(@Laneclosure_ID,0),LCN.Laneclosure_pk)
and LCN.ClosureType_pk =
case when @IncludeSpecialEvents =1 then 7
else Coalesce(Nullif(@Closure_Type_ID,0),LCN.ClosureType_pk)
End
and LCN.daytime_closure_request = Coalesce(Nullif(@DaytimeClosuresOnly,0),LCN.daytime_closure_request)
and LCN.laneclosure_status_fk = coalesce(Nullif(@Status_ID,0),LCN.laneclosure_status_fk)
and LCN.roadtobeclosed_pk = coalesce(Nullif(@RoadtobeClosed_ID,0),LCN.roadtobeclosed_pk)
and (LCN.Start_Milepost between Coalesce(Nullif(@BeginMP,0),Start_MilePost) and Coalesce(Nullif(@EndMP,0),End_MilePOst))
and (End_MilePOst between Coalesce(nullif(@BeginMP,0),Start_MilePost) and Coalesce(Nullif(@EndMP,0),End_MIlePost))
and ((date_of_closure >= coalesce(@StartDate,date_of_closure)) and (date_of_closure <= coalesce(@EndDate,date_of_closure)))
and ((date_created >= coalesce(@DateEnteredStart,date_created)) and (date_created <= coalesce(@DateEnteredEnd, date_created)))
and DA.laneclosure_direction_fk = coalesce(Nullif(@DirectionID,0),laneclosure_direction_fk)
and Exists
(Select 1 From dbo.LCM_RequestSource RS
Where RS.requestsource_pk = LCN.requestsource_pk
and RS.Active_flag = coalesce(Nullif(@IncludeInactiveRequestSOurces,1),Active_Flag))
if Exists(Select 1 From #ResourceIDs)
BEGIN
Select *
from LCM_V_LaneClosureNotices_Summary LC_S
Inner Join #ResourceIDs R on LC_S.RequestSource_fk = R.requestsource_pk
where laneclosure_pk in (Select Distinct ID from #laneclosures)
order by date_Created Desc
END
else
BEGIN
Select *
from LCM_V_LaneClosureNotices_Summary LC_S
where laneclosure_pk in (Select Distinct ID from #laneclosures)
order by date_Created Desc
END
Drop Table #ResourceIDs
Drop table #laneclosures
Any help is greatly appreciated.
March 31, 2009 at 9:47 am
It's usually a default configuration problem with the .net database provider. Execute the following line in your client application:
SET ARITHABORT ON
After that it should work.
Greets
Flo
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply