I'm trying to find the cause for this error while calling a stored proc with a table valued parameter:
Error: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
My objective is to load sanitized data from a 3rd party app into a data-warehousey location for in-house apps to consume. I don't have an "updated" flag in the source, so I'm using a data flow to push all of the data into a recordset, then firing a script task to call a
MERGE proc with TVP.
I've used this pattern successfully several times, but this data set is by far the largest--over 1m rows (though not terribly wide). I can always truncate and reload the table every time within an acceptable time frame, but that just seems awfully wasteful on I/O.
I looked at the settings in the ADO.NET connection, but the timeouts there don't seem like they should apply. I'm considering slicing the recordset into smaller chunks and looping over the proc, but without a better understanding of the problem, I suspect that would just cause problems when the data set grows enough to make the chunks fail.
alter proc [sis].[Attendance_Merge] @data sisAttendance readonly as begin if object_id('tempdb..#data') is not null drop table #data; create table #data ( [SchoolYear] smallint not null, [Student] [varchar] (10) NOT NULL, [Date] [date] NOT NULL, [Period] [varchar] (10) NOT NULL, [Reason] [varchar] (32) NOT NULL, [Funding] [varchar] (1) NOT NULL, [StateReported] [varchar] (1) NOT null, [rn] int not null primary key clustered (SchoolYear,Student,[Date],Period,rn) ); insert into #data select * ,rn = row_number() over( partition by SchoolYear ,Student ,[Date] ,Period order by Student ) from @data ; with att as ( select * ,rn = row_number() over( partition by SchoolYear ,Student ,[Date] ,Period order by Student ) from sis.Attendance ) merge into att t using #data s on t.SchoolYear = s.SchoolYear and t.Student = s.Student and t.[Date] = s.[Date] and t.Period = s.Period and t.rn = s.rn when matched and (t.Reason <> s.Reason or t.Funding <> s.Funding or t.StateReported <> s.StateReported ) then update set t.Reason = s.Reason ,t.Funding = s.Funding ,t.StateReported = s.StateReported when not matched by target then insert ( SchoolYear ,Student ,[Date] ,Period ,Reason ,Funding ,StateReported ) values ( s.SchoolYear ,s.Student ,s.[Date] ,s.Period ,s.Reason ,s.Funding ,s.StateReported ) when not matched by source then delete ; end