I ran into a sql related .net error today.  If you read the title of the post you’ve probably guessed what it is.  If not here’s the error:

The specified cast from a materialized ‘System.Int64’ type to the ‘System.Int32’ type is not valid.

Google was marginally helpful, but if you’re like me when you google an error you’re hoping for that post that says:  If you see ABC, then you have done XYZ, do 123 to fix the error.  In this case the error is saying there’s a datatype problem.  Something about a Long and an Int.  My app only has ints.  No longs in the schema at all.  This specific error came out of an entity framework method, so I couldn’t easily pinpoint it to a given column.  98% of my app is pure entity framework, mostly code-first (though I do write out transactional schema patches to update the database in a scripted manner.)  There is one stored procedure in the app, and this stored proc I had just changed to add some new features specifically paging from the sproc.

In this case, the sproc looked like this:

Reports_MyReport SomeGuidID

it returned data of the report, field1,field2, field3 etc..

My change was to add paging directly to the sproc, reduce the amount of data leaving the box as this report was going to get hit a lot.

Reports_MyReport SomeGuid, PageNumber, PageSize

it returns data like RowNumber, Field1, Field2, Field3, TotalRows

I tested out the changes, they worked great, no nulls where they weren’t expected.

Upon running the new sproc through my app, i got the error listed above.  It turned out that my sproc, which had code like this:

select RowNumber, Field1, Field2, Field3, @totalRows as TotalRows  ….

was the culprit.  @totalRows was being interpeted as a int64, as that was comming from an @@ROWCOUNT function.  I know i’ll never have more than int32 rows in that table, so for me switching by casting to Int32 solved the problem:

select RowNumber, Field1, Field2, Field3, cast(@totalRows as int) as TotalRows  ….

Problem solved, error gone!

Hopefully by the time I have completely forgotten about this, and make the exact same mistake again – in six months – this post will be living in the googles.  Hopefully this helps someone else as well.

Scott