Skip to content


No data for a smartlist that uses a view

Dominic Beland asked 4 years ago
I usually create a view and add the DYNGRP database role security to it.
IT usually works fine. 
However, for a particular view,  “sa” is unable to retrieve data from the smartlist, eventhough we’re able to retreive data directly from the view in SQL.
I’m pretty sure the the tables used have the DYNGRP assigegnd to it as well. 
What could prevent “sa” from being able to see data in a smartlist?
Nicole Albertson Staff answered 4 years ago
If the “sa” user isn’t returning data as you would expect in SmartList, then there is an error happening behind the scenes.  My recommendation would be to run a SQL Trace with the errors enabled for the “sa” user and see what error is being returned when it runs the select statement for the SmartList.  This should help figure out why SmartList isn’t able to return the results when you know the view works in Management Studio directly.
Dominic replied 4 years ago

OK, so we did the profiler.

It turns out that it’S not a view but an actual table that is causing the problem.

Smartlist is doing à Select field1, field2, isnull(field3,”), etc… instead of a simple “Select * from table”

SQl is unable to convert a field as a string. So we tried to display a single field and the smartlist works.

So we will have to find the field that’s causing the problem and exclude it from the smartlist. (or cast it as a different type in the view)

If you would like to submit an answer or comment, please sign in to the eOne portal.