17

In my db table Layout, there's one column whose type is hierarchyid (column index=4). When trying to set-up new environment (a virtual web-server, created from XEN server), then running the site, I've met with this issue:

Exception message: DataReader.GetFieldType(4) returned null. Exception data: System.Collections.ListDictionaryInternal

I've made some search and found out there are already some topic on it (such as on MSDN).

But even when I added the C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SqlServer.Types.dll library, it seems like db type in structure SqlHierarchyId doesn't get recognized.

"Exception at DataReader.GetFieldType(4) returned null" is still thrown out.

Note: The issue will be solved if I made installation of C# package in VS2010 onto the environment (Windows Server 2008 RC2), but my boss didn't accept that, because this is purely a simple web-server.

jonsca
  • 9,342
  • 26
  • 53
  • 60
Undefined Identity
  • 395
  • 3
  • 5
  • 13

6 Answers6

24

Reference the Microsoft.SQLServer.Types dll from the project and for the reference set it as "Copy Local" in the properties of the reference. This will package that DLL up with the website when you deploy it. Then you don't need all of SQL Server installed on your web box in order to use the SQL Server data types. I did this for my website because it was using the geography data type columns and I was getting the same error.

Paul Mendoza
  • 5,494
  • 9
  • 47
  • 80
  • Thanks. That's the same way I've done: I'd already added library Microsoft.SQLServer.Types.dll from SQL Server into Reference section of my solution. – Undefined Identity Aug 05 '11 at 17:31
  • 1
    Future searchers - the `Microsoft.SQLServer.Types` in Nuget is v11 and won't work. See OP's post for the DLL path; add it to your project manually as shown here; you can then `GetFieldType` and it will return `Microsoft.SqlServer.Types.SqlHierarchyId` – Mendhak Mar 25 '15 at 18:57
  • 2
    Is this the "correct" way of solving the problem? There is a new data type in sql server and then I have to use "copy local"? – Anders Lindén Jan 12 '17 at 07:12
17

I tried to resolve this issue by adding the Microsoft.SqlServer.Types NuGet package to my project, but that alone did not help. I also had to add the following to the <assemblyBinding> element of my project's App.config:

<runtime>
  <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
  .
  .
  .
    <dependentAssembly>
      <assemblyIdentity name="Microsoft.SqlServer.Types" publicKeyToken="89845dcd8080cc91" culture="neutral" />
      <bindingRedirect oldVersion="0.0.0.0-14.0.0.0" newVersion="14.0.0.0" />
    </dependentAssembly>
  .
  .
  .
  </assemblyBinding>
</runtime>
bgh
  • 1,647
  • 1
  • 23
  • 35
11

The solution that works for me is add "Type System Version=SQL Server 2012" to connection string.

Example: string connectionString = "Data Source=myserver;Initial Catalog=mydatabase;User ID=sa;Password=*******;Type System Version=SQL Server 2012;";

user3190933
  • 136
  • 1
  • 3
  • 1
    Saved my day! Thanks! Got this issue after migrating an old app to a newer server which used a newer sql server. adding this to the connection string will make it fallback to these older sql server types. (which you can download via Microsoft's web platform installer). – Joel Harkes Aug 14 '18 at 08:22
  • This is a VERY good answer! I am using a SqlConnectionStringBuilder and set the Property TypeSystemVersion to "SQL Server 2012" -- I was able to retrieve table schema information for AdventureWorks2017, as well as another DB that I included -- trying to encompass two databases in a single schema and this removed my last blocker. I upvoted your answer because it seems to be the least intrusive and most correct answer to this issue. Thanks! – jinzai Mar 05 '20 at 18:59
  • Don't know why, but this saved my day as well! – uqji Mar 20 '20 at 07:57
1

Rather than changing your project and adding a reference to Microsoft.SQLServer.Types dll you could just put it into the GAC.

In my case I had three versions on my dev machine and added them to the server GAC:

 gacutil.exe -i .\10.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Types.dll
 gacutil.exe -i .\11.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Types.dll
 gacutil.exe -i .\12.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Types.dll

I had other SQL related third party .NET tools on the server that had the same error. After adding the assemblies to the GAC, they all worked fine.

While I don't put my own assemblies into the GAC, I think it is okay to put some Microsoft SQL-Server assemblies there because they affect multiple applications.

Peter Hahndorf
  • 9,472
  • 4
  • 39
  • 57
1

After other solution if you still have error try to delete in web.config

<add assembly="Microsoft.SqlServer.Types, Version=12.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"/>

copy file Microsoft.SqlServer.Types.dll to bin folder (or/and add reference)

or/and add "Type System Version=SQL Server 2012;" in sqlconnectionstring

Sergey Shamanayev
  • 43
  • 6
0

For those experiencing this problem with PowerShell, I was able to fix my problem by installing the SQLSysClrTypes.msi package from Microsoft for SQL Server 2016 and restarting powershell. The download page is confusing to navigate, click "Download" and search the page for SQLSysClrTypes. Select the right architecture.

Exception calling "Fill" with "1" argument(s): "DataReader.GetFieldType(24) 
returned null."
At MyScript.ps1:15 char:5
+     $adapter.Fill($ds) | Out-Null
+     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], 
ParentContainsErrorRecordException
    + FullyQualifiedErrorId : InvalidOperationException

In my case, the offending data type was Microsoft.SqlServer.Types.SqlGeography in position (24) This field worked fine inside AppVeyor with SQL Server 2016 installed, but wouldn't run on my local environment.

I found several articles explaining why this occurs and some specified to use NuGet to fetch the latest Microsoft.SqlServer.Types.dll, but in my case, this didn't help, nor did any attempt to replace the assembly\GAC_... version with one from Install-Package and friends.

Note: Installing the .msi wasn't as straight forward as one would expect because through trial and error, I had installed several older "Microsoft SQL Server System CLR Types". This resulted in the MSI only offering "Repair" and "Remove". If this occurs, chose "Remove" and the run the installer again.

Installing the correct version and restarting PowerShell did the trick.

tresf
  • 3,691
  • 3
  • 26
  • 78