title: Error when using XML in Stored Procedure (PDO + MS SQL 2008)
body: <p>When I use SQL Profiler, I see all the requests coming from the Debian server and I can execute them without having any error. When I execute the SP from a Apache/PHP + PDO I get the following error:</p>
  
  <blockquote>
    <p>SQLSTATE[HY000]: General error: 1934
    General SQL Server error: Check
    messages from the SQL Server [1934]
    (severity 16) [(null)]</p>
  </blockquote>
  
  <p>In fact the error is not shown properly. The real error should be like the following:</p>
  
  <blockquote>
    <p>Msg 1934, Level 16, State 1, Procedure
    CALCUL_NOTATION_XML2, Line 38 SELECT
    failed because the following SET
    options have incorrect settings:
    'CONCAT_NULL_YIELDS_NULL,
    ANSI_WARNINGS, ANSI_PADDING'. Verify
    that SET options are correct for use
    with indexed views and/or indexes on
    computed columns and/or filtered
    indexes and/or query notifications
    and/or XML data type methods and/or
    spatial index operations.</p>
  </blockquote>
  
  <p>My SP looks something like this:</p>
  
  <pre><code>CREATE PROCEDURE dbo.TEST_XML(
      @MyXML XML
  )
  AS
  BEGIN
      SET @MyXML = '&lt;SampleXML&gt;
      &lt;Colors&gt;
          &lt;Color1&gt;White&lt;/Color1&gt;
      &lt;/Colors&gt;
      &lt;Fruits&gt;
          &lt;Fruits1&gt;Apple&lt;/Fruits1&gt;
      &lt;/Fruits&gt;
      &lt;/SampleXML&gt;'
  
      SELECT
      a.b.value('Colors[1]/Color1[1]','varchar(10)') AS Color1,
      a.b.value('Fruits[1]/Fruits1[1]','varchar(10)') AS Fruits1,
      FROM @MyXML.nodes('SampleXML') a(b) 
  END
  </code></pre>
  
  <p>The error only happens because of the way PDO connect to the database. It use the following options:</p>
  
  <pre><code>-- network protocol: TCP/IP
  set quoted_identifier off
  set arithabort off
  set numeric_roundabort off
  set ansi_warnings off
  set ansi_padding off
  set concat_null_yields_null off
  set ansi_nulls off
  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 committed
  </code></pre>
  
  <p>I wonder if it would be possible to change the login setup in PDO to have something that looks more like:</p>
  
  <pre><code>set quoted_identifier on
  set arithabort on
  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 Français
  set dateformat dmy
  set datefirst 1
  set transaction isolation level read committed
  </code></pre>
  
  <p>I have read on a <a href="http://gullele.wordpress.com/2010/12/15/accessing-xml-column-of-sql-server-from-php-pdo/" rel="nofollow">site</a>, a kind of workaround, but I wonder if there is a real solution (like configuring PDO I don't know how to make it work).</p>