1. Home
  2. Forum
  3. Dashboard Platform
  4. SQL Exception when running in preview mode

SQL Exception when running in preview mode

Hello,

I'm experiencing the following sqlexception when running my dashboard in preview mode or published to the server.

I've checked all three datasources and they can connect successfully to my sql backend, I have also checked in Query View using the Execute button that each datasource can retrieve live data with no issue and also update the widgets.  However, when running in preview mode I get the following exception.  The dashboard was running seamlessly.

Any ideas?


System.Data.SqlClient.SqlException (0x80131904): Incorrect syntax near the keyword 'AS'.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
   at System.Data.SqlClient.SqlDataReader.get_MetaData()
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteScalar()
   at Syncfusion.Dashboard.Base.Data.SqlQueryBuilder.ExecuteScalarQuery(String query, String connectionString, ConnectionProviderType type)
   at Syncfusion.Dashboard.Base.Data.ServerDataProvider.GetRawDataRowCount(List`1 originalSchemaInfoCollection, String userName, List`1 tableRelation)
   at Syncfusion.Dashboard.Base.Engine.RelationalEngine.GetRawDataCount(String userName)
   at Syncfusion.DashboardService.DashboardService.<LoadDashboardAction>b__133_1(DataSource dataSource)
   at System.Linq.Enumerable.WhereSelectListIterator`2.MoveNext()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at Syncfusion.DashboardService.DashboardService.LoadDashboardAction(Dictionary`2 dictionary, DashboardServiceArguments arguments, List`1 viewerActions, DashboardDataProcessor dataProcessor)
   at Syncfusion.DashboardService.DashboardService.InitializeControls(DashboardServiceArguments arguments)
   at SyncInvokeInitializeControls(Object , Object[] , Object[] )
   at System.ServiceModel.Dispatcher.SyncMethodInvoker.Invoke(Object instance, Object[] inputs, Object[]& outputs)
   at System.ServiceModel.Dispatcher.DispatchOperationRuntime.InvokeBegin(MessageRpc& rpc)
   at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage5(MessageRpc& rpc)
   at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage41(MessageRpc& rpc)
   at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage4(MessageRpc& rpc)
   at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage31(MessageRpc& rpc)
   at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage3(MessageRpc& rpc)
   at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage2(MessageRpc& rpc)
   at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage11(MessageRpc& rpc)
   at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage1(MessageRpc& rpc)
   at System.ServiceModel.Dispatcher.MessageRpc.Process(Boolean isOperationContextSet)
ClientConnectionId:970d88dd-546b-4731-a196-eb07c3b7e622
Error Number:156,State:1,Class:15
ClientConnectionId before routing:f9ee215f-6cff-4fd6-91eb-657f2e65c50d
Routing Destination:e55e911cad55.tr29.westeurope1-a.worker.database.windows.net,11192

3 Replies
KM Kavitha Murugesan Syncfusion Team December 8, 2017 12:12 PM UTC

Hi Iain, 
 
We are unable to replicate the reported issue with the provided information. Please let us know the following details to investigate the problem and provide you the solution at earliest.  
 
  1. Please confirm whether you were applied Refresh Settings in your dashboard.
 
  1. If yes, confirm whether you are using below highlighted option in Refresh Settings window.

 
 
  1. Provide your Dashboard Designer and Dashboard Server version.
 
  1. If possible, share your stored procedure definition query.
 
  1. Share the error log file which can be located in below location.
C:\ProgramData\Syncfusion\DashboardDesigner\2.3.0.32\IISExpress_DashboardService\ErrorLog  
Please replace your designer version in highlighted place if you are not using 2.3.0.32 version. 
 
Please let us know for any concerns. 
 
Regards, 
Kavitha M. 
 


IS Iain Stirzaker December 8, 2017 06:42 PM UTC

Hello Kavitha,

I have things working again now.

As you highlighted, the issue was with the Refresh Settings; essentially, I had enabled the settings as per your example, which unknowingly caused the issue.



When I later disabled 'Enable Auto Refresh' I left the seetings as per the screenshot which continued to cause the SQL Exception, even though the options are greyed-out.  Disabling the highlighted option stops the SQL Exception being thrown and everythings working fine now.



I hope this helps.  If you still need the additional you requested please let me know, thanks for the prompt response.

Cheers,
Iain

KM Kavitha Murugesan Syncfusion Team December 11, 2017 11:32 AM UTC

Hi lain, 
 
Thanks for your details.  
 
We tried to reproduce the reported issues with different version of Dashboard designer. Still we are unable to replicate your issue. Please provide the below information as we have mentioned earlier update. It will help us to provide you a prompt solution for this issue. 
 
  1. Provide your Dashboard Designer and Dashboard Server version.
 
  1. Please share the event log file. To get an event log files, please follow the below steps.
 
  • Before opening the saved report, click on “Enable Tracing” option under the Help menu.
 
 
  • Reproduce the reported issue.
  • Then click “View Log Files” option under Help Menu.
  • Finally, you got Event log files generated in below mentioned location %AppData% \Syncfusion\DashboardDesigner\2.3.0.32\Logs\EventLogs
Note: Once the above processes are over, disable the Enable Tracing option to continue your work. 
  1. Share the error log file which can be located in below location.
C:\ProgramData\Syncfusion\DashboardDesigner\2.3.0.32\IISExpress_DashboardService\ErrorLog  
Please replace your designer version in highlighted place if you are not using 2.3.0.32 version. 
 
  1. If possible, share your stored procedure definition query or share us the sample dashboard with dummy/garbage records to replicate this issue.    
 
Regards, 
Kavitha M. 


Loader.
Up arrow icon