PowerPivot troubleshooting: "SQL statement is not valid. There are no columns detected in the statement"

From Kate Baroni, Senior Database Administrator at Microsoft: 

"I recently added one column to an existing and working powerpivot model (Excel 2013) and started receiving the error ‘SQL statement is not valid.  There are no columns detected in the statement’.  The SQL logic already included a statement for ‘SET NOCOUNT ON’ and I manually verified that none of the statements in the sproc were returning any rows except the select statement.  So the validation should have succeeded but it kept failing.   I modified the SELECT logic in the sproc to NOT alias any column names and now the validation and query run."

To get the data connection to validate and return results, Kate modified the SELECT statement was from this:  

SELECT [Division] as 'Division', [Domain] as 'Domain', [Organization] as 'Organization',

       [Team] as 'Team', [Environment] as 'Environment',

       [Server] as 'Server', InstanceName as 'InstanceName',

       DatabaseName as 'DatabaseName', [LogFile] as 'LogFile', [VLF Count] as 'VLF Count',

       [LogFileSize] as 'LogFileSize',

       [Number of Log Files] as 'Number of Log Files', CollectionDate as 'CollectionDate'

FROM #TempSQLInstances

WHERE [Server] IS NOT NULL

To this:

 

SELECT [Division], [Domain], [Organization], [Team], [Environment],  

       [Server], [InstanceName], [DatabaseName], [LogFile], [VLF Count],  

       [LogFileSize], [Number of Log Files], [CollectionDate] 

FROM #TempSQLInstances

      WHERE [Server] IS NOT NULL

Thanks Kate, for the great troubleshooting tip!

An alternative solution is to include the following statement in your stored procedure, especially if you need the ALIASes or other feature giving PowerPivot problems:
SET FMTONLY OFF