Using Stored Procedure in Power Pivot

To get data from sql server using stored procedures in power pivot, follow below points:

1. SET ANSI_WARNINGS OFF (there should not be any warnings returned by the query)
2. SET NOCOUNT ON (query should not return more than 1 count. In the below example execute SPand insert returns count, then again on select another count is returned. So totally 2, and it should not be the case).

EX: 

SET ANSI_WARNINGS OFF

SET NOCOUNT ON

CREATE TABLE #tmp

(

EmployeeID INT

, Employee VARCHAR(225)

)

INSERT INTO #tmp EXEC usp_Name

SELECT * FROM #tmp

DROP TABLE #tmp