-1

I have a complex CLR function which returns a single row. I can use Cross Apply to select results which works fine but an Update or Insert fails. The select always works however insert,select into and update fails with below error, this error doesnt shows up in select queries.

Msg 6522, Level 16, State 1, Line 1
A .NET Framework error occurred during execution of user-defined routine or aggregate "AppendCLR": 
System.ArgumentOutOfRangeException: Index was out of range. Must be non-negative and less than the size of the collection.
Parameter name: index
System.ArgumentOutOfRangeException: 
   at System.Collections.ArrayList.get_Item(Int32 index)
   at AppendCLR.ProcessData(String EnrollmentID,String InstituteCode)
   at AppendCLR.UserDefinedFunctions(SqlString EnrollmentID,SqlString InstituteCode)
.
The statement has been terminated.

TestCLR is the table which I need to update with the results of the CLR Function AppendCLR. #TestCLR_TEMP and @TestCLR_TEMP are temp and table variables respectively.

This doesnt works :

update a set
a.ID=b.ID,
a.Branch=b.Branch,
a.CourseID=b.CourseID, 
a.EnrolDate=b.EnrolDate, 
a.CourseTag=b.CourseTag, 
a.Micro=b.Micro, 
a.StudentTag=b.StudentTag FROM TestCLR a cross apply 
dbo.AppendCLR([EnrollmentID],[InstituteCode]) b

This doesnt works too :

select *  into #TestCLR_TEMP from  dbo.AppendCLR([EnrollmentID],[InstituteCode])

OR

select *  into TestCLR_TEMP from  dbo.AppendCLR([EnrollmentID],[InstituteCode])

However this works :

declare table @TestCLR_TEMP (ID bigint,
Branch varchar(100),
CourseID bigint,
EnrolDate DateTime,
CourseTag varchar(100),
Micro varchar(100),
StudentTag varchar(100))

insert into @TestCLR_TEMP select 
a.ID,
b.Branch,
b.CourseID, 
b.EnrolDate, 
b.CourseTag, 
b.Micro, 
b.StudentTag FROM TestCLR  a cross apply 
dbo.AppendCLR([EnrollmentID],[InstituteCode]) b
--I have now my results in @TestCLR_TEMP, I can perform an inner join to append these results back to my original table TestCLR

This always works :

Select * from dbo.AppendCLR(123,456)

Basically all insert, updates fails when I try them on real tables.The error in SSMS doesnt says anything important but I can post it if needed. Thanks!

Anand Verma
  • 263
  • 4
  • 10
  • 1
    Sometimes we important details are overlooked in error messages. Please _always_ post error message. If there is nothing truly helpful, then no harm done. – Solomon Rutzky Oct 08 '15 at 21:45
  • 1
    Actually, yes, please post the error message(s). Also is `TestCLR` a table or another TVF? – Solomon Rutzky Oct 08 '15 at 21:52
  • 1
    And what version of SQL Server? – Solomon Rutzky Oct 08 '15 at 21:53
  • Did some updates, Please check, thankyou. – Anand Verma Oct 09 '15 at 06:16
  • Thanks for posting the error message. It is rather clear from that message that the issue is your .NET code, specifically related to ArrayList. So please post your code. – Solomon Rutzky Oct 09 '15 at 10:24
  • This is the strange part, this error doesnt shows up when running the select queries.The CLR works fine with selects but fail with update or insert commands – Anand Verma Oct 09 '15 at 10:59
  • Yes, that info will help find the issue. The exact problem is a combination of your code and the data. So I need to see both, but we can start with the code. – Solomon Rutzky Oct 09 '15 at 11:06
  • 1
    Also, I assume that `[EnrollmentID]` and `[InstituteCode]` are fields in the `TestCLR` table? I assume you were going to rename all of these objects once you get this working, right? Since "TestCLR" and "AppendCLR" are meaningless names that will only cause maintenance problems in the future. – Solomon Rutzky Oct 09 '15 at 13:37
  • `[EnrollmentID]` and `[InstituteCode]` are fields in the `TestCLR` table? This is correct. `TestCLR`, `AppendCLR` etc.. are just Test Names. I am working on debugging the CLR function, I hope I will find the error else I will work on sharing the code with you, I appreciate your help so far. – Anand Verma Oct 10 '15 at 11:59

1 Answers1

0

I feel little stupid but the original error was Transaction Context In Use By Another Session while reading results of a select query into a DataTable and I was getting System.ArgumentOutOfRangeException when reading this empty DataTable.

The fix was using Enlist=false in the connection string.

I understand I should have focused towards error reporting, but thanks guys for your efforts.

Anand Verma
  • 263
  • 4
  • 10