title: stored procedure return value in management studio but not in c# code
body: <p>I have a stored procedure that uses a linked server (ADSI) to connect to active directory. It takes in a string/varchar guid and then returns the thumbnailPhoto attribute of the user out of active directory. I am trying to call this procedure from C# code, but it keeps returning null. I've tried to make sure that the guid being passed was correct so from debug, I grabbed the guid being passed into the parameter then tried to use that to run the sproc in management studio. The result came back fine, but when I do it from my application, I get null. Here's the code:</p>
  
  <p>SPROC:</p>
  
  <pre><code>ALTER PROC [dbo].[sp_GetPhotoFromADSI] 
                  @GUID varchar(100)
  AS 
                  SET NOCOUNT ON 
                  SET XACT_ABORT ON  
  
                  BEGIN TRAN
  
                  DECLARE @sql NVARCHAR(600)
  
                  SET @sql=N'SELECT thumbnailPhoto FROM OPENQUERY(ADSI,
                                  ''SELECT thumbnailPhoto 
                                    FROM ''''LDAP://&lt;GUID=' + @GUID + '&gt;'''' 
                                    WHERE objectCategory = ''''Person'''' AND objectClass = ''''user'''''')'
  
                  EXEC SP_EXECUTESQL @sql
  
                  COMMIT
  </code></pre>
  
  <p>C#:</p>
  
  <pre><code>private byte[] GetUserPhotoByGuid(Guid userNativeGuid)
  {
      //dont judge me for not abstracting this
      var connString = "connectionStringCrap;";
      byte[] photo = null;
  
      using (var conn = new SqlConnection(connString))
      {
          try
          {
              string guidString = userNativeGuid.ToString();
  
              //var realGuid = Guid.Parse(userNativeGuid);
              using (var comm = new SqlCommand("sp_GetPhotoFromADSI", conn))
              {
                  comm.CommandType = CommandType.StoredProcedure;
                  comm.Parameters.AddWithValue("@GUID", guidString);
                  var returnParameter = comm.Parameters.Add("@Output", SqlDbType.Binary);
                  returnParameter.Direction = ParameterDirection.ReturnValue;
                  conn.Open();
                  comm.ExecuteScalar();
  
                  photo = returnParameter.Value as byte[];
                  return photo;                       
              }
          }
          catch (Exception)
          {
              throw;
          }
          finally
          {
              conn.Close();
          }
  
      }
      return null;
  }
  </code></pre>