Hi guys,

I need help with this one. I have WCF self hosted service on server without DB installed. I tried numerous solutions found on the net but nothing helpful.

Im calling sp and getting exception here:

....
SqlHierarchyId test = new SqlHierarchyId();  // this get just fine and added for test

            using(StoredProcedureCall call = CreateSpGetSubordinateUserParentCall(dataAccessProvider, userId))
            {
                DataTable toReturn = call.FillDataTable();   // <--  exception thrown here
                return toReturn;
            }
....

Exception error message: "DataReader.GetFieldType(3) returned null"

This is clearly problem with column no.3 which in my case is HierarchyTypeId. I did reference Microsoft.SqlServer.Types assembly v.12 (as lines with hierarchyId pass well i presume there is problem a bit lower I can get).

Here is stack trace of exception:

   at System.Data.ProviderBase.SchemaMapping.SetupSchemaWithoutKeyInfo(MissingMappingAction mappingAction, MissingSchemaAction schemaAction, Boolean gettingData, DataColumn parentChapterColumn, Object chapterValue)
   at System.Data.ProviderBase.SchemaMapping..ctor(DataAdapter adapter, DataSet dataset, DataTable datatable, DataReaderContainer dataReader, Boolean keyInfo, SchemaType schemaType, String sourceTableName, Boolean gettingData, DataColumn parentChapterColumn, Object parentChapterValue)
   at System.Data.Common.DataAdapter.FillMappingInternal(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 schemaCount, DataColumn parentChapterColumn, Object parentChapterValue)
   at System.Data.Common.DataAdapter.FillMapping(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 schemaCount, DataColumn parentChapterColumn, Object parentChapterValue)
   at System.Data.Common.DataAdapter.FillFromReader(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue)
   at System.Data.Common.DataAdapter.Fill(DataTable[] dataTables, IDataReader dataReader, Int32 startRecord, Int32 maxRecords)
   at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.CallRetrievalStoredProcedure(String storedProcedureToCall, DbParameter[] parameters, DataTable tableToFill) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v4.0\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\AdapterSpecific\DataAccessAdapterBase.cs:line 176
   at SD.LLBLGen.Pro.ORMSupportClasses.StoredProcedureCall.FillDataTable() in c:\Myprojects\VS.NET Projects\LLBLGen Pro v4.0\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\Persistence\StoredProcedureCall.cs:line 135
...
(removed start as this is the call point, irrelevant)

I need further ideas how to resolve this, and yes ORM assembly is 4.0.0.0. (this might be also problem?)

With Regards, K.

P.S. didn't know how to address this problem so put it in this category

What's the database type of HierarchyTypeId and the .NET type?

And what is the SqlHierarchyId?

Ups, sorry... In both cases I was reffering to Sql (2008R2, 2012, 2014) type HierarchyId (url: https://msdn.microsoft.com/en-us/library/bb677173.aspx).

.Net type defined in assembly "Microsoft.SqlServer.Types" is SqlHierarchyId structure.

Walaa wrote:

What's the database type of HierarchyTypeId and the .NET type?

And what is the SqlHierarchyId?

K.

AFAIK, LLBLGen doessn't support that field. I think it will try to return it as string. If you are open to workarounds: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=14060

Anyway, your exact exception is occurring at SQL client, not at LLBLGen. This might be because you didn't mark the Microsoft.SqlServer.Types assembly as "Copy Local". Can you confirm that?

Copy local is set to true, and "Specific Version" property is set to False. To confirm that proper assembly is referenced in level of my code, I have added a line of code just above the call to SP returning record set with this column type. At that line of code exception is not raised.

Although to my knowledge "Copy local" property is just for coping referenced assembly to "build" folder, .NET is loading assemblies 1st from local application folder then from GAC. So the same result would be as if I copied this assembly version(in case "Specific Version" = true) manually to "application" folder. Do you agree with that, or maybe I missed something?

The same build works fine on development machine and on machine with SQL 2008/2014 instance, because of that I'm sure wrong assembly is being referenced for some reason although proper version of assembly "Microsoft.SqlServer.Types" exists in application folder.

As for type mapping to LLBLGEN, in my opinion this should be binary not string because the underlying SQL type is structure.

With regards, K.

P.S. I didn't had time to confirm your statement about Sql client but I will do this today P.S.S. I tried every possible solution I did find on the net binded with this particular exception type, the same error is binded to SQL spatial type

daelmo wrote:

AFAIK, LLBLGen doessn't support that field. I think it will try to return it as string. If you are open to workarounds: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=14060

Anyway, your exact exception is occurring at SQL client, not at LLBLGen. This might be because you didn't mark the Microsoft.SqlServer.Types assembly as "Copy Local". Can you confirm that?

P.S. I didn't had time to confirm your statement about Sql client but I will do this today

We'll be waiting your feedback.

ok

Walaa wrote:

P.S. I didn't had time to confirm your statement about Sql client but I will do this today

We'll be waiting your feedback.

I confirmed your stament, problem lies in SQL client. Don't know why but it seems that what ever I do get the same result.

Tried with "Copy local = true" etc. but nothing seems to work with WCF self hosted.. wrong assemblies get referenced or none of required ones below my code level and this hierarchyid sql type seems to make problem when accessed through DataReader(or "Sql client").

If anyone has suggestion I would appriciate the info, for now I really don't need this data so I have removed it from query, but I'm affraid I could use it in the future. I would realy like to get native data as is.

With Regards, K.

Copy local copies the dll in the output folder of the application. However if the dll is also in the gac (or a dll which matches the type of the element returned by sqlclient), then that dll is loaded, even if there's a dll in your applications bin folder.

If you write some raw ado.net, and execute the proc with cmd.ExecuteReader(), could you do that and call Read() once on the reader? After that the 3rd field contains the value, so if you then do: reader.GetValue(2) it should return the hierarchyID.

Then, in the debugger, check the type of the object returned there and the assembly and its location. If a raw datareader crashes too, I can't help you, I'm afraid, other than using a typedview mapped onto the stored procedure resultset (generated as poco) and use that at runtime.

Thank you Otis on your feedback, that's what I did .net reader is actually breaking up, the same thing I concluded, so tried to copy System.Data assembly locally as everything is going out of it, but still it seems GAC is being referenced, but actually what I didn't try is to manually register assembly (Microsoft.SqlServer.Types) to GAC, that might resolve the issue... I will keep in mind this, in case I will need hierarchyid type from server in .net code.

Thank you all for brainstorming for resolution about this issue with me.

K.

P.S. code is working fine on Dev environment, so it's not the issue with LLBLGEN at all (I was not targeting at that at all, but as didn't had chance to see LLBLGEN source, just wanted an insight of people coding that layer )

Otis wrote:

Copy local copies the dll in the output folder of the application. However if the dll is also in the gac (or a dll which matches the type of the element returned by sqlclient), then that dll is loaded, even if there's a dll in your applications bin folder.

If you write some raw ado.net, and execute the proc with cmd.ExecuteReader(), could you do that and call Read() once on the reader? After that the 3rd field contains the value, so if you then do: reader.GetValue(2) it should return the hierarchyID.

Then, in the debugger, check the type of the object returned there and the assembly and its location. If a raw datareader crashes too, I can't help you, I'm afraid, other than using a typedview mapped onto the stored procedure resultset (generated as poco) and use that at runtime.

The issue is familiar to me as I ran into the same kind of issue (at least it looks like it) when testing UDTs on sqlserver: gac had a different assembly than what was referenced in sqlserver, reading a row crashed and it was a long bumpy road to finally understanding why it failed.

To the same outcome I also came, and that was also my conclusion. I will list things I've tried without result, maybe it will help someone else:

Solution 1. installed Spatial redistributable on production server - still wrong assemblies get referenced in "Sql client" code - also tried to put code for initialization of the same but without success - referencing sql server types in my code went without problem and could use them - this installation should install Microsoft.Sqlserver.Types (THE Assembly further in text) in GAC and resolve the problem but nope eg.

SqlHierarcyId id = new SqlHierarcyId();
....
// lines below don't work
SqlCommand cmd = new SqlCommnad("Select hierarchyId from Table", cnn);
SqlReader reader = cmd.ExecuteReader();
while(reader.Read())  // here it will break with exception mention in initial message
....

Note: as Win 8 server, and the latest .NET installed I was not bothering further with .NET stuff, maybe I should but didn't had time to investigate, as something in dev environment was deployed to "production-test" and had to be fixed instantly

Solution 2. manually reference THE assembly with copy local (a lot of threads across the net are targeting this with IIS/web solutions mainly targeting Azure platform which is not my case btw) but the same thing happens as mention in the code above - this solution is a long shot as this options are not bounding referenced assembly only making copy to "bin" folder - assembly loader is looking 1st for assemblies first locally then in GAC ( the same thing Otis mentioned above), that's the reason I thought if I copy System.Data to local folder I would be safe, but seems loader is looking for DLL in GAC, but then again solution no.1 would suffice

Solution 3. as a last resort (and a big nonsense, you should basically not do this even if you have access to production server, but I was now very curious about this problem and did that) installed VS 2013 Express, hoping to create mini dev environment just to test my hunch. Unfortunately didn't resolve me of my pain either

So my conclusion after all this "DLL hell" still exists!, and I'm very long in development and.. "I've seen things you people wouldn't believe...Attack ships on fire off the shoulder of Orion. I watched c-beams glitter in the dark near the Tannhäuser Gate. All those… moments… will be lost in time, like tears… in… rain. Time… to die" ( to be a bit poetic at the end)

Cheers, K.

P.S. my compliments to "the chef", you have great product with reasonable price and I can say EF is currently not the match for LLBLGEN

could you check whether the database server has the same assembly version installed as you're using in your code? As the assembly version of the type in the DB is used by the client.

Keiser wrote:

So my conclusion after all this "DLL hell" still exists!, and I'm very long in development and.. "I've seen things you people wouldn't believe...Attack ships on fire off the shoulder of Orion. I watched c-beams glitter in the dark near the Tannhäuser Gate. All those… moments… will be lost in time, like tears… in… rain. Time… to die" ( to be a bit poetic at the end)

Heh I feel your pain...

P.S. my compliments to "the chef", you have great product with reasonable price and I can say EF is currently not the match for LLBLGEN

thank you!