SQL query returns value in SSMS, value is DBNull when loaded into DataTable

[Miss Amber Clark]

I'm extending an open source project that already documents table and definitions to include stored procedures, user defined functions, etc., but I've run into a perplexing snag. Once I get the name and the parameters, I go to fetch the definition of the module.

Select [definition], [uses_ansi_nulls], [uses_quoted_identifier],
[is_schema_bound], [uses_database_collation], [is_recompiled],
[null_on_null_input], [execute_as_principal_id],
[uses_native_compilation]
From [sys].[sql_modules]
Where [object_id] = @object_Id


In SQL Server Management Studio, using my Windows credentials, all of the fields comeback populated as I would expect. However, when I run the query in the C# code as a console app running with a trusted SQL connection.

static void RenderDefinition(int objectId, XmlElement parentElement)
{
var dt = new DataTable();

// GetDefinition is a SQL script that contains the SQL query from above.
using (var da = new SqlDataAdapter(Resources.Commands.GetDefinition, connectionString))
{
da.SelectCommand.Parameters.Add("@object_Id", SqlDbType.Int).Value = objectId;
da.Fill(dt);
}

var row = dt.Rows[0];

// Create object element
var e = parentElement.AppendChild(parentElement.OwnerDocument.CreateElement("definition")) as XmlElement;

foreach (DataColumn col in dt.Columns)
{
var value = row[col].ToString();
if (string.IsNullOrWhiteSpace(value)) continue;

if (col.ColumnName == "definition")
{
// The SQL code
e.InnerText = value; // <-- Value is always DBNull, never the actual value
}
else
{
// Other defining attributes
e.SetAttribute(col.ColumnName, value);
}
}
}


The value of "definition" always comes back as DBNull but all the other fields come back as expected. This is for every stored procedure and user defined function including the short ones and the monstrous ones (one sproc is 450+ lines and over 24,000 characters).

Continue reading...