1

We have a number of assemblies that are compiled with either .NET 2.0 or .NET 3.5.

Is there a way, via an undocumented table, function, etc - that one could query the list of assemblies in a database and get the .NET version of the assembly?

We have queried sys.assemblies which does contain a variety of assembly information, but does not include the target framework version.

We are upgrading our 08R2 instance to Windows Server 2012 and need to understand which assemblies are currently targeting the .NET 2.0 framework, not the 3.5 framework, so we can target recompiling them (or replacing, as we have a number of assemblies from NuGet packages and it is not clear whether they target .NET 3.5 or not).

Improve this question

2 Answers 2

Reset to default
1

No, there is no mechanism for getting this info (at least not that I have ever seen or heard of). If this info is available via Reflection, then you could create a SQLCLR stored procedure or function to read the content column of sys.assembly_files and try to extract it from there.

HOWEVER, you might not need this info. If the only reason is to determine what might need recompilation as you transition from SQL Server 2008 R2 to SQL Server 2012, then:

  1. there is no real distinction between what was compiled for 2.0 and the 3.5 targeted Assemblies. 2008 R2 is linked to CLR 2.0 which covers .NET Framework versions 2.0, 3.0, an 3.5, while 2012 and newer are linked to CLR 4.0 which uses .NET Framework versions 4.0 and newer. Hence, when you move to SQL Server 2012, both the Assemblies compiled for 2.0, and the ones using 3.5, are all linked to a CLR version that you won't be using. So, if you need to recompile the 2.0 Assemblies, you would probably also need to recompile the 3.5 Assemblies as well. BUT....
  2. fortunately you most likely don't need to recompile anything at all. .NET has backwards compatibility for most classes so stuff that is compiled for 2.0 should still work across all future Framework versions. In fact, I use a target framework version of 2.0 for all but possibly one of the Assemblies in my SQL# project so that they will work across all SQL Server versions starting with 2005. I have yet to run into a problem running anything, and most of my initial testing is done on SQL Server 2012, while I do additional testing on 2014 and 2016 and no problems encountered yet. The biggest "issues" have been minor changes in default values or behaviors between Framework versions (such as the "enlist" Connection String keyword changing its default from false to true across one of the upgrades, and things like that).
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

We ran into issues with a particular assembly that failed on a test machine that had its OS upgraded to Windows Server 2012. It did not work until we changed the target framework from .NET 2.0 to 3.5.
@ElliotRodriguez In that case, I suppose it is possible that the real issue was that the machine never had the full 2.0 Framework installed on it. If you run into this again, I would make sure that 2.0 itself is installed rather than starting with 3.5. I guess it is possible that something doesn't work as expected, even with the backwards compatibility, or maybe that backwards compatibility requires that the previous Framework versions were fully installed. What exactly is your assembly doing? Any particular Classes it is using that I could test out? I don't see what changing to 3.5 would do.
0

You can check out the output of:

select * from sys.dm_clr_properties

You should get something like:

name        value
-----------------------------------
directory   C:\Windows\Microsoft.NET\Framework64\v4.0.30319\
version     v4.0.30319
state       CLR is initialized
Improve this answer

2 Comments

My question was not clear (it has been rephrased). I'm trying to get the target framework version of the assembly - not what version of the .NET CLR is on the server. We are upgrading our 08R2 instance to Windows Server 2012 and need to understand which assemblies are currently targeting the .NET 2.0 framework, not the 3.5 framework, so we can target recompiling them (or replacing, as we have a number of assemblies from NuGet packages and it is not clear whether they target .NET 3.5 or not)
SELECT * FROM sys.assemblies if this query could include one more column that defined the Target Framework Version, basically the same metadata that the VS project file contains, this is what we are looking for. Inspecting the sys.assemblies view yields the ASSEMBLYPROPERTY which is close but no target framework version. learn.microsoft.com/en-us/sql/t-sql/functions/…

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.