.net - Msg 6522, Level 16 but no error message selecting from sys.all_columns -

i'm running sqlclr function sqlconnection("context connection=true").

in cases, exception

system.data.sqlclient.sqlexception (0x80131904)
@ system.data.sqlclient.sqlconnection.onerror(sqlexception exception, boolean breakconnection, action`1 wrapcloseinaction)
@ system.data.sqlclient.sqlinternalconnectionsmi.eventsink.dispatchmessages(boolean ignorenonfatalmessages)
@ system.data.sqlclient.sqldatareadersmi.internalread(boolean ignorenonfatalerrors)
@ objdb.functions.objdb(string db, string schema, string obj, string col, string val)

clientconnectionid:00000000-0000-0000-0000-000000000000
error number:200,state:4,class:25

that happens every time same query @ same record number.

with connection

sqlconnection(@"server=" + @"localhost\sqldeveloper16" + ";database=" + db + ";integrated security=true;connect timeout = 50") 

i never error, , fine.

i don't understand why.

then tried same clr on sql server 2008 , there no problem...what have check?!?!

could sql server 2016 bug?!

this class

using system; using system.data; using system.data.sqlclient; using system.data.sqltypes; using microsoft.sqlserver.server; using system.collections; using system.collections.generic; using system.threading; using system.xml; using newtonsoft.json; using system.xml.linq; using system.io; using system.linq; using system.text;  namespace objdb {     public partial class functions     {         [sqlfunction         (             //dataaccess = dataaccesskind.read,//serve x accedere alle tabelle del db, altrimenti accede solo qlle di sistema...             systemdataaccess = systemdataaccesskind.read,             fillrowmethodname = "columns_ok",             tabledefinition = "c1 nvarchar(max), node nvarchar(max)"         )         ]          public static ienumerable objdb(string db, string schema, string obj, string col, string val)         {             list<string> rows = new list<string>();//list<object[]> rows = new list<object[]>();             //list<tuple.t2<string, string>> rows = new list<tuple.t2<string, string>>();              sqlcommand command = null;// = new sqlcommand(query, conn);             sqlconnection conn = null;// new sqlconnection("context connection=true");              try             {                 conn = new sqlconnection("context connection=true");//new sqlconnection(@"server=" + @"localhost\sqldeveloper16" + ";database=" + db + ";integrated security=true;connect timeout = 50"); //new sqlconnection("context connection=true");//                 conn.open();                  string query;                 stringbuilder sb = new stringbuilder();                 sb.append("where 0=0");                  if ((col != null && !col.equals("")) && (val != null && !val.equals("")))                 {                     string[] cols = col.split(',');                     string[] vals = val.split(',');                      (int = 0; < math.min(cols.length, vals.length); i++)                     {                         sb.append(" , [").append(cols[i]).append("]='").append(vals[i].replace("'", "''")).append("'");                     }                     //filter = "where 0=0 " + sb.tostring();                     //filter = "where [" + col + "]='" + val + "'";                 }                 //estrazione inline dell xml (un xml x ogni riga)                 //"binary base64" -> https://stackoverflow.com/questions/8801697/xml-export-via-bcp-bulk-export - bug 2008r2, converte il varbinary in ascii                 query =                 "select (select t.* xml raw('root'),binary base64) " +                 "from [" + db + "].[" + schema + "].[" + obj + "] t " +                 sb.tostring(); ;//filter;                  command = new sqlcommand(query, conn);                 //command.commandtimeout = 0;                 sqldatareader dr = command.executereader();                  while (dr.read())                 {                     //rows.add(new tuple.t2<string, string>("", (string)dr[0]));                     rows.add((string)dr[0]);                 }                 return rows;             }             catch (exception e)             {                 rows.add(e.tostring().substring(0, math.min(4000, e.tostring().length)));                 return  rows;             }                         {                 if (command != null)                     command.dispose();                 if (conn != null)                     conn.close();             }         }         private static void columns_ok(object resultobj, out sqlstring node)         {             //string res = (string)resultobj;              node = (string)resultobj;         }     } } 

and sql

select * dbo.objdb('test','sys','all_columns',null,null) 

result (5248 records , last contains exception, posted last 3):

<root object_id="-103402673" name="similarity_index_page_count" column_id="4" system_type_id="127" user_type_id="127" max_length="8" precision="19" scale="0" is_nullable="1" is_ansi_padded="0" is_rowguidcol="0" is_identity="0" is_computed="0" is_filestream="0" is_replicated="0" is_non_sql_subscribed="0" is_merge_published="0" is_dts_replicated="0" is_x005f_xml_document="0" xml_collection_id="0" default_object_id="0" rule_object_id="0" is_sparse="0" is_column_set="0" generated_always_type="0" generated_always_type_desc="not_applicable" is_hidden="0" is_masked="0"/> <root object_id="-103085222" name="database_id" column_id="1" system_type_id="56" user_type_id="56" max_length="4" precision="10" scale="0" is_nullable="0" is_ansi_padded="0" is_rowguidcol="0" is_identity="0" is_computed="0" is_filestream="0" is_replicated="0" is_non_sql_subscribed="0" is_merge_published="0" is_dts_replicated="0" is_x005f_xml_document="0" xml_collection_id="0" default_object_id="0" rule_object_id="0" is_sparse="0" is_column_set="0" generated_always_type="0" generated_always_type_desc="not_applicable" is_hidden="0" is_masked="0"/> system.data.sqlclient.sqlexception (0x80131904)     @ system.data.sqlclient.sqlconnection.onerror(sqlexception exception, boolean breakconnection, action`1 wrapcloseinaction)     @ system.data.sqlclient.sqlinternalconnectionsmi.eventsink.dispatchmessages(boolean ignorenonfatalmessages)     @ system.data.sqlclient.sqldatareadersmi.internalread(boolean ignorenonfatalerrors)     @ objdb.functions.objdb(string db, string schema, string obj, string col, string val)  clientconnectionid:00000000-0000-0000-0000-000000000000  error number:200,state:4,class:25 

thank in advance!!

update: don't know why, problem column "collation_name" on sql 2016.

i tried remove query , no exceptions...

the error coming 1 particular row in sys.system_columns, 1 of 2 internal tables queried in sys.all_columns system catalog view.

this error appears happen if collation_name field used in query selecting either sys.system_columns or sys.all_columns.

testing subsets of rows, able determine error occurs 1 row. object is:

sys.pdw_nodes_pdw_physical_databases

this row not present in sql server 2012 (i have not tested besides 2012 sp3 , 2016 sp1), , there no errors there @ all.

the o.p. mentioned error occurs when collation_name field selected. looking @ field particular row, null. there nothing wrong null since many other rows shown null collation_name. different 1 row datatype sysname, alias nvarchar(128), string type , hence should have non-null collation. i'm not sure how column, physical_name, has null collation, string column null collation in sql server 2016, , there none in sql server 2012.

why error? well, real error is:

msg 6522, level 16, state 1, line 406
a.net framework error occurred during execution of user-defined routine or aggregate "objdb":
  system.data.sqlclient.sqlexception:
  system.data.sqlclient.sqlexception:
    @ system.data.sqlclient.sqlconnection.onerror(sqlexception exception, boolean breakconnection, action`1 wrapcloseinaction)
    @ system.data.sqlclient.sqlinternalconnectionsmi.eventsink.dispatchmessages(boolean ignorenonfatalmessages)
    @ system.data.sqlclient.sqldatareadersmi.internalread(boolean ignorenonfatalerrors)
    @ system.data.sqlclient.sqldatareadersmi.read()
    @ objdb.functions.objdb(string db, string schema, string obj, string col, string val)

notice how there no actual error message! weird. not sure how happens, collation_name field following expression:

convert(sysname, columnpropertyex(object_id, name, 'collation')) collation_name

the columnpropertyex function undocumented, there isn't go on.

i suspect bug, of sorts, columnpropertyex raises low-level error, low enough not show error in ssms. however, in-process connection (i.e. context connection = true) seems sensitive , catches whatever happening columnpropertyex.

now, avoiding should simple matter of adding following condition where clause if schema name sys , object_name all_columns or system_columns:

not (user_type_id in (231, 256) , collation_name null) 

however, since views, seems process rows before apply filters.

so, 1 way found around filter on id fields using following:

not (t.[object_id] = -103085222 , t.[column_id] = 2) 

the real problem don't know if object_id same across versions of sql server 2016, nor in sql server 2017 (i haven't checked there yet).

what / real bug?

while seems columnpropertyex function should not behaving is, think primary problem 3 columns pdw_nodes_pdw_physical_databases should returning sys.all_columns in first place. pdw_nodes_pdw_physical_databases isn't available outside of azure sql data warehouse , parallel data warehouse. in fact, strange enough object name pdw_nodes_pdw_physical_databases returned object_name function, yet not show in sys.all_objects ;-).