April 7, 2014

I had this situation where I had a XML column in a table within a Sql Server database which allowed NULL and I had to change this to must have values. Changing this is trivial when it is a new database with some test data but in my situation, I had to modify an existing database and therefore - the quickest way to change was to add the default xml to all fields that had NULL and then to apply a default value for this column. However, when I add the default value via Sql Server Management Studio, it complained - "Error validating the default for column - column name".

image

 

I wasn’t sure what the real error was but after trying for a little while I thought this might actually be an error with Sql Server Management Studio itself. So, to test my theory, I tried adding the default value via t-sql and it worked fine. Here’s the t-sql that I tried.

Sql to add default value
  1. alter table [Table_1]
  2. add constraint DF_XML
  3. default '<a><b/></a>'
  4. for XmlData

Once the query is executed, the default value is added as can be seen from the constraints section.

image

0 comments:

Reference: Shahed Kazi at AspNetify.com