PowerPivot cannot load from procedure with info messages

Data can be loaded into PowerPivot from a SQL Server stored procedure, but the procedure cannot contain:

• PRINT statements, like print 'lets start'
• informational RAISERROR events, like raiserror ('lets start', 0, 1)

Both return messages to the client. Tools like Management Studio can handle these messages and display them in a separate window. PowerPivot handles them like the returned data and fails reporting an error. The error is not very helpful:

OLE DB or ODBC error: lets start; 01000.
An error occurred while processing table 'Query'.
The current operation was cancelled because another operation in the transaction failed.

The only solution is to remove these instructions from the procedure and any procedures it might call. There is no way to suppress this on the OLEDB connection during the call to the procedure.

It is also advisable to have the following two instructions at the start of the procedure:

• set fmtonly off
• set nocount on