title: Stored procedure not returning value when executed from code, works fine from management studio
body: <p>I have a proc like </p>
  
  <pre><code>CREATE PROCEDURE [dbo].[ExportTestAsXML]
      @TestId         int
  AS
  BEGIN
      SET NOCOUNT ON;
  
      declare @x xml;
      set @x = (
         select
            (select * from table1 where testid = @TestId FOR XML auto, Type, Elements),
            (select * from table2 where testid = @TestId FOR XML auto, Type, Elements),
            (select * from table3 
             where objectiveid in 
                 (select objectiveid from objectives where testid = @TestId) 
             FOR XML auto, Type, Elements),
            (select * from table4 
             where objectiveid in 
                 (select objectiveid from objectives where testid = @TestId) 
             FOR XML auto, Type, Elements),
            (select * from table5 
             where questionid in 
                 (select questionid from questions 
                  where objectiveid in 
                        (select objectiveid from objectives where testid = @TestId)
                 ) 
             FOR XML auto, Type, Elements)
         for xml path(''), type
      );
  
     select @x as my_xml
  END
  </code></pre>
  
  <p>When I run it from SQL Server 2005 Management Studio I get a table with a single record containing the combined XML from the select statements. When I run it from my web service code and use the datatable visualizer to check the table it is empty.  Here is the code I use to execute the proc</p>
  
  <pre><code>SqlParameter[] Parameters = new SqlParameter[1];
  Parameters[0] = new SqlParameter();
  Parameters[0].ParameterName = "@TestId";
  Parameters[0].Value = TestId;
  Parameters[0].SqlDbType = SqlDbType.Int;
  Parameters[0].Size = 50;
  
  DataTable data = ExecuteDataSet("ExportTestAsXML", Parameters);
  
  private DataTable ExecuteDataSet(string storedProcName, SqlParameter[] parameters)
  {
      SqlCommand command = new SqlCommand();
      command.CommandText = storedProcName;
      command.Parameters.AddRange(parameters);
      command.CommandType = CommandType.StoredProcedure;
      command.Connection = (SqlConnection)dcMUPView.Connection;
  
      command.Connection.Open();
      command.Prepare();
  
      SqlDataAdapter adapter = new SqlDataAdapter(command);
      DataTable ds = new DataTable();
      adapter.Fill(ds);
  
      command.Connection.Close();
      return ds;
  }
  </code></pre>
  
  <p>Any idea what is going on?</p>