none
SQL Server 2008 column_Default returns NULL in .Net 2.0 RRS feed

  • Question

  • We recently updated from SQL Server 2005 to SQL Server 2008 and have run into a problem when running the following query through the built in .Net 2.0 SQL classes.

    SELECT COLUMN_DEFAULT FROM INFORMATION_SCHEMA.Columns WHERE TABLE_NAME = 'TRIP' AND COLUMN_NAME = 'ISMULTILOAD'

    When run connected to a SQL Server 2005 server the above query returns what we expect, the default value for that column.  When run connected to a SQL Server 2008 server the query returns NULL.  The .Net install is fully updated.  When run through SQL Server Management Studio the query returns correctly on both 2005 and 2008, so we know it has something to do with .Net 2.0 when connected to a SQL 2008 server.

    Any ideas?
    • Moved by eryang Tuesday, October 6, 2009 3:47 AM (From:.NET Base Class Library)
    Friday, October 2, 2009 8:29 PM

Answers

  • Hi Jake,

    Welcome to ADO.NET Data Providers forum!

    I attempt to reproduce this issue, but with no avail.  I am using such ADO.NET 2.0 codes to query the data and it works fine on both SQL Server 2005 and 2008.  Also, based on MSDN documentation, the COLUMN_DEFAULT column should be similar in SQL Server 2005 and SQL Server 2008.
    http://msdn.microsoft.com/en-us/library/ms188348.aspx
    http://msdn.microsoft.com/en-us/library/ms188348(SQL.90).aspx

    ===================================================================
                string connStr = @"Data Source=(local);Initial Catalog=Test;Integrated Security=True";

                using (SqlConnection conn = new SqlConnection(connStr))

                {

                    SqlCommand cmd = new SqlCommand("SELECT COLUMN_DEFAULT FROM INFORMATION_SCHEMA.Columns WHERE TABLE_NAME = 'DefaultValueTest' AND COLUMN_NAME = 'DefaultValue'", conn);

                    conn.Open();

     

                    using (var reader = cmd.ExecuteReader())

                    {

                        string text = null;

                        while (reader.Read())

                        {

                            text = reader.GetString(0);

                            Console.WriteLine("{0}", text);

                        }

                    }

                }
    ===================================================================

    Could you please provide us with more detailed information about your source codes and the data type and default value of the column “ISMULTILOAD”? 


    Have a nice day!

    Best Regards,
    Lingzhi Sun


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    • Marked as answer by Yichun_Feng Friday, October 9, 2009 5:23 AM
    Wednesday, October 7, 2009 10:02 AM
    Moderator

All replies

  • I assume by "built in .Net SQL classes" that you mean ADO.NET. Show the code you're using to execute the select statement.

    *edit - and by 'execute' I mean execute and read.

    Saturday, October 3, 2009 8:22 PM
  • Hi Jake,

    Welcome to ADO.NET Data Providers forum!

    I attempt to reproduce this issue, but with no avail.  I am using such ADO.NET 2.0 codes to query the data and it works fine on both SQL Server 2005 and 2008.  Also, based on MSDN documentation, the COLUMN_DEFAULT column should be similar in SQL Server 2005 and SQL Server 2008.
    http://msdn.microsoft.com/en-us/library/ms188348.aspx
    http://msdn.microsoft.com/en-us/library/ms188348(SQL.90).aspx

    ===================================================================
                string connStr = @"Data Source=(local);Initial Catalog=Test;Integrated Security=True";

                using (SqlConnection conn = new SqlConnection(connStr))

                {

                    SqlCommand cmd = new SqlCommand("SELECT COLUMN_DEFAULT FROM INFORMATION_SCHEMA.Columns WHERE TABLE_NAME = 'DefaultValueTest' AND COLUMN_NAME = 'DefaultValue'", conn);

                    conn.Open();

     

                    using (var reader = cmd.ExecuteReader())

                    {

                        string text = null;

                        while (reader.Read())

                        {

                            text = reader.GetString(0);

                            Console.WriteLine("{0}", text);

                        }

                    }

                }
    ===================================================================

    Could you please provide us with more detailed information about your source codes and the data type and default value of the column “ISMULTILOAD”? 


    Have a nice day!

    Best Regards,
    Lingzhi Sun


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    • Marked as answer by Yichun_Feng Friday, October 9, 2009 5:23 AM
    Wednesday, October 7, 2009 10:02 AM
    Moderator
  • I ran into a similar problem. It turned out the user did not have the VIEW DEFINITION permission on the database.
    • Proposed as answer by Petr Kadlec Monday, June 20, 2011 4:13 PM
    Saturday, January 23, 2010 8:38 PM