Unable to refresh dataset in PowerBI service using on-prem gateway

[Cullan]

-Refreshing the data model works fine in the power bi desktop (which is also installed on the same server at the on-premise gateway)

-Publishing the PBIX works fine but then refreshing data just results in the loading icon of doom on the dataset for 20mins then a warning sign but no specific error even in the refresh history logs.

-I managed to find this in the gateway log "System.InvalidCastException: Unable to cast GuidColumn to type String" I can't seem to find anything else regerding the issue since error reporting is basically non-existent.

-Re-installed both Desktop and Gateway to latest version.

-I am pulling tables from one SQL server for the dataset and I have tried creating a new dataset with different tables which uploads and refreshed fine.

-I wish there were more detailed error logging so I could track this down but even the verbose logging is terrible.

[Cullan · Accepted solution]

Hi Qiuyun,

I checked the SQL logs but couldn't find anymore detail but I managed to solve the issue anyway. I removed table by tables from my dataset untill the refresh worked and I found the problematic table which had a column full of GUID. When viewing the table in the query editor the braces around the GUID disappear so im not sure it that is a bug or not, maybe part of the problem. 

Anyway I just ran the UPPERCASE transformation on the GUID column and now it refreshes ok.

Thanks,

Cullan

[v-qiuyu-msft]

Hi @Cullan,

It seems the refresh issue happens to the one specific table in the same database, right? Can you please let SQL administrator check SQL log at the same time to see if any issue happened on SQL side? 

Best Regards,
Qiuyun Yu

[Cullan]

DM.EnterpriseGateway Error: 0 : 2017-03-22T20:53:35.9449293Z DM.EnterpriseGateway	2d8dcc9c-c0db-4d84-b79a-dcd0c4261d26	6df7d4b6-b98b-7025-236d-da33707bf2b7	MGPS	cbe98385-2f4b-aa18-f698-73a20f10293b	A1054F00 [DM.Pipeline.Common] Non-gateway exception encountered in activity scope: System.InvalidCastException: Unable to cast GuidColumn to type String.
   at Microsoft.OleDb.Column.GetString(Int32 row)
   at Microsoft.OleDb.Serialization.PageReaderDataReader.GetString(Int32 ordinal)
   at Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.Serialization.AdoBinaryWriterSerializer.<Read>d__4.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.Serialization.AdoBinaryWriterSerializer.<Serialize>d__0.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.Serialization.GatewaySerializer.<SerializeAdoQueryResponse>d__18.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.Serialization.GatewaySerializer.<SerializeImpl>d__14.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.Serialization.GatewaySerializer.<>c__DisplayClass2.<<Serialize>b__0>d__4.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.PowerBI.DataMovement.Pipeline.Common.Diagnostics.PipelineTelemetryService.<ExecuteInActivity>d__3.MoveNext()