SQL Server error: Conversion of XML characters

I hit an interesting error in SQL Server today. A user had somehow entered a unicode character that could not be converted to ASCII. In a stored procedure, the XML column was being converted to a varchar and this error occurred:

"Conversion of one or more characters from XML to target collation impossible"

You can recreate the scenario with the following TSQL code:

DECLARE @xml xml, @nvc nvarchar(64)
SELECT @nvc = 'Coptic letter: ' + nchar(997) + ''
SELECT @nvc as 'before conversion'
SELECT @xml = @nvc
SELECT convert(varchar, @xml) -- fails with conversion error

The conversion from XML -> varchar fails because the Unicode value could not be represented. There are a couple options here. You can use nvarchar instead of varchar. This would support the unicode value:

SELECT convert(nvarchar, @xml) as 'xml -> nvarchar conversion'

You can also convert it to nvarchar and then to varchar. This results in any non-mappable characters being changed to a question mark, like so:

SELECT convert(varchar, convert(nvarchar, @xml)) as 'xml -> nvarchar -> varchar conversion'

In my particular situation I added some code to clean up the data before it is sent to the database. I specified that the stream reader that was producing the data to be inserted into the XML column should use the ASCII encoding. This results in the non-mappable characters being translated to question marks before it goes to the database. That is just what I did in this particular case. It may not apply in your situation.

EF error: Unable to update the EntitySet 'tablename' because it has a DefiningQuery and no

I got a strange Entity Framework error today at runtime. I was trying to insert a new entity/row into the database and I got the following error:

"Unable to update the EntitySet '[tablename]' because it has a DefiningQuery and no element exists in the element to support the current operation."

It turns out the table did not have a primary key defined. Whoops. Because of this, EF did something a little different. It basically treated the table as a view and it seemed to use a combination of all the foreign keys in the table as a composite key, I think.

The solution was to define the primary key for the table and then update the model. This fixed most of the problem, but it also introduced a compile time error about the model. It turned out I then had to go and manually mark the foreign key fields as not part of the primary key.