SQL Server 2005: Problem updating XML column from .NET TableAdapter

Question

I'm trying to update an XML column in a SQL Server 2005 table. Access to this column is through a stored procedure. I'm using a .NET tableAdapter to call this procedure. The problem: The value of the xml column does not get updated.

Some observations:

• I used SqlProfiler to see if the stored procedure call is actually sent to the SQL Server. This is the case.
• I also executed the stored procedure call directly from within SQL Server Management Studio. When done this way, the stored procedure is executed.

Could this be a problem with the parameters the .NET tableAdapter uses? That would be the only difference with the SQL Server Management Studio calls in my view.

Here is the list:

  -- network protocol: TCP/IP
     set quoted_identifier on
     set arithabort off
     set numeric_roundabort off
     set ansi_warnings on
     set ansi_padding on
     set ansi_nulls on
     set concat_null_yields_null on
     set cursor_close_on_commit off
     set implicit_transactions off
     set language us_english
     set dateformat mdy
     set datefirst 7
     set transaction isolation level read uncommitted

Answer 1

When you tried executing the stored proc manually, did you use the same user credentials that your application's table adapter is using? If not try impersonating the user and see whether you then can see an error which your app isn't currently catching.

Speaking of which, do you have exception handling in your app code around the database request? It could be that an error is being raised but ignored because you aren't trapping it.