title: Excel Microsoft Query parameter length limit
body: <p>MS Excel 2010 64-bit
  Windows 7 Pro 64-bit on the local machine
  SQL Server 2012</p>
  
  <p>I often call stored procedures in SQL Server to return data to Excel for analysis in PivotTables. I pass parameters to the sp's using the following syntax in Microsoft Query's SQL Editor:</p>
  
  <pre><code>{Call dbo.MySP(?,?,?,)}
  </code></pre>
  
  <p>Where <code>dbo.MySP</code> is an SP that takes three parameters. When I return the output from the query to Excel I get to select which cells to refer to to get the parameter values.</p>
  
  <p>Yesterday I wrote an SP that takes 3 arguments: a date, another date and a text string delimited by commas. The SP has them declared as two <code>DATETIMES</code> and an <code>NVARCHAR(4000)</code>. As usual, I convert the dates in Excel to text strings in the form <code>yyyy-mm-dd</code> to pass them, but the text string is just a string of characters with individual values delimited by a pipe (<code>|</code>).</p>
  
  <p>When I attempt to select the cell containing a text string less than <code>255</code> characters all is well. If I go over <code>255</code> I get an error from Excel complaining:</p>
  
  <blockquote>
    <p>Bad parameter type. Microsoft Excel was expecting a different kind of
    value that was was provided.</p>
  </blockquote>
  
  <p>Changing the delimiter has no impact.</p>
  
  <ul>
  <li>Is that a hard limit on the length a parameter passed from Excel can handle?</li>
  <li>Is there a workaround of any sort?</li>
  </ul>
  
  <p>Note that is is clearly an Excel limitation, running the same query with any length parameter works fine in SSMS.</p>