MS Excel 2010 64-bit Windows 7 Pro 64-bit on the local machine SQL Server 2012
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:
{Call dbo.MySP(?,?,?,)}
Where dbo.MySP 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.
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 DATETIMES and an NVARCHAR(4000). As usual, I convert the dates in Excel to text strings in the form yyyy-mm-dd to pass them, but the text string is just a string of characters with individual values delimited by a pipe (|).
When I attempt to select the cell containing a text string less than 255 characters all is well. If I go over 255 I get an error from Excel complaining:
Bad parameter type. Microsoft Excel was expecting a different kind of value that was was provided.
Changing the delimiter has no impact.
- Is that a hard limit on the length a parameter passed from Excel can handle?
- Is there a workaround of any sort?
Note that is is clearly an Excel limitation, running the same query with any length parameter works fine in SSMS.