-3

I am running a sql server function below on 5 different databases on sql 2016 instance:

select * FROM fn_myfunction('', 0, 0,'30 Apr 2015','1', 0)

And am getting this error message:

Msg 10313, Level 16, State 51, Line 1
An error occurred while using the .NET Framework during IL compilation. The server may be running out of resources. Try running the query again. If the problem persist, contact a support professional. 
System.NullReferenceException: Object reference not set to an instance of an object.
System.NullReferenceException:

I have made a backup of these databases and restored to a different sql 2016 instance and the error disappeared. So I now think that this has nothing to do with my databases or sql code. This could be an environment issue?

Any suggestions please?

Panagiotis Kanavos
  • 120,703
  • 13
  • 188
  • 236
Immortal
  • 1,133
  • 1
  • 15
  • 35
  • 3
    You didn't post your code so it's not possible to help. The NullReferenceException is clear though - somewhere in your SQLCLR method you tried to access a null value – Panagiotis Kanavos Jun 11 '19 at 07:28
  • 1
    Possible duplicate of [What is a NullReferenceException, and how do I fix it?](https://stackoverflow.com/questions/4660142/what-is-a-nullreferenceexception-and-how-do-i-fix-it) – Panagiotis Kanavos Jun 11 '19 at 07:28
  • `and the error disappeared.` that doesn't mean much. NREs are always raised when trying to access a `null` value. It could be that the *data* used by the function is different, or that the locale used to parse the date is different. Passing dates as strings is a bad idea in the first place. Perhaps the a failed date parse, eg with `DateTime.TryParse` skipped over the initialization of a string variable – Panagiotis Kanavos Jun 11 '19 at 07:33
  • @PanagiotisKanavos am thinking it has nothing to do with my code because I have restored the same database to a different instance and did run the query and could not get this error message again. Do you still need the code in this case? – Immortal Jun 11 '19 at 07:34
  • NREs are *alway*s about the code. They can only happen when the code tries to access a null. It's the code that didn't initialize that variable or check that input. In a database, the function may have failed to load any data and the code didn't handle this. – Panagiotis Kanavos Jun 11 '19 at 07:34
  • 1
    Using a string instead of an actual date value, especially with such a localized format, would cause even a SQL query to fail if that string can't be parsed as a date. For example `SELECT * from Table1 Where someDate='30 Apr 2015'` won't return any results if the server locale's date format doesn't match that string. The only unambiguous date string format is `YYYYMMDD` – Panagiotis Kanavos Jun 11 '19 at 07:37
  • @PanagiotisKanavos I have restarted my instance and this worked. The issue here was not db or code related as you suggested because I used the same db on a different instance and could not reproduce the error. This was down to Memory being fully utilized. So I changed my memory settings and restarted the instance and worked like a hammer!!!!!!!!!!!!!! – Immortal Jun 11 '19 at 10:01

1 Answers1

-2

I have restarted my instance and this worked. The issue here was not db or code related as suggested by @Panagiotis because I used the same db on a different instance and could not reproduce the error. This was down to Memory being fully utilized. So I changed my memory settings and restarted the instance and worked like a hammer!!!!!!!!!!!!!!

Immortal
  • 1,133
  • 1
  • 15
  • 35
  • No it didn't. It means your code has a memory leak. Perhaps you allocate too many strings? Or add items to a list in an inefficient way? That wouldn't result in an NRE in any case, unless the *code itself* did something creative like use a `catch{}` to hide errors and then try to access uninitialized variables. What you describe is *not* normal behavior. BTW if you post the *full* exception, including the call stack, you'll see where the error was raised and which series of calls led to it – Panagiotis Kanavos Jun 11 '19 at 10:01
  • @PanagiotisKanavos There are no memory leaks on my code whatsoever. This is the same code am running on the same database on a different instance. Whatever the case is, am done with this question because I believe this is sorted – Immortal Jun 11 '19 at 10:05
  • Restarting a database instance is the nuclear option. It's not a fix, it's a quick workaround to get production back up. Sooner or later you'll encounter the same problem – Panagiotis Kanavos Jun 11 '19 at 10:05
  • This might not be a memory leak as the error message "_The server **may** be running out of resources_" is very strongly emphasizing the "may" part. Still, @PanagiotisKanavos is entirely correct in stating that there is not enough info here to determine anything. If the problem only occurred one time, then there is absolutely no way to know what the problem is, or what solved it. Sure, it could be environmental, but it seems like there is a flaw in the code that allowed it to happen. This question and answer should either be updated to include code and error message, or else deleted. – Solomon Rutzky Jun 11 '19 at 17:47