PowerPivot Error When Using Custom SQL Query

Question

I built a query in SQL Server Management Studio 2012, which runs fine when tested. However, when attempting to use this code in Power Pivot I receive the following error:

OLE DB or ODBC error.

An error occurred while processing table 'Query'.
The current operation was cancelled because another operation in the transaction failed.

Any thoughts on what could be invoking this error?

Here is my query:

DECLARE @beginDate as DATE;
DECLARE @endDate as DATE;
DECLARE @tempTable AS TABLE(BusinessUnitID INTEGER, BusinessUnitName VARCHAR(255), SkillCode VARCHAR(255), 
                    Actuals INTEGER, DateCounted DATE)

DECLARE @count as INTEGER;

SET @beginDate = (SELECT MAX(DateCounted) FROM mmapps.dbo.t_EMS_BM_ActualsHistory);
SET @endDate = DATEADD(week,1,@beginDate);
SET @count = 0;

WHILE DATEADD(week,@count,@beginDate) < DATEADD(year,1,@beginDate)
BEGIN
    IF (@count = 0)
    BEGIN
        INSERT INTO @tempTable (BusinessUnitID,BusinessUnitName,SkillCode, Actuals, DateCounted)
        SELECT a.BusinessUnitID, a.BusinessUnitName, a.SkillCode, a.TotalHeads + 
            (
            SELECT COUNT(*) 
            FROM mmapps.dbo.t_EMS_BM_ChartData AS b 
            WHERE a.BusinessUnitID = b.BusinessUnitID AND a.SkillCode = b.SkillCode 
                AND b.ActivityTypeID BETWEEN 6 AND 10 AND b.SkillCode IS NOT NULL
                AND b.Date < @endDate) -
            (
            SELECT COUNT(*) 
            FROM mmapps.dbo.t_EMS_BM_ChartData AS b 
            WHERE a.BusinessUnitID = b.BusinessUnitID AND a.SkillCode = b.SkillCode 
                AND b.ActivityTypeID BETWEEN 6 AND 10 AND b.SkillCode IS NOT NULL
                AND b.Date < @endDate
            )
            AS Actuals

        ,DATEADD(week,@count,@beginDate) AS DateCounted
        FROM mmapps.dbo.t_EMS_BM_ActualsHistory AS a
        WHERE a.DateCounted = @beginDate
    END

    IF (@count > 0)
    BEGIN
        INSERT INTO @tempTable (BusinessUnitID,BusinessUnitName,SkillCode, Actuals, DateCounted)
        SELECT a.BusinessUnitID, a.BusinessUnitName, a.SkillCode, a.Actuals + 
            (
            SELECT COUNT(*) 
            FROM mmapps.dbo.t_EMS_BM_ChartData AS b 
            WHERE a.BusinessUnitID = b.BusinessUnitID AND a.SkillCode = b.SkillCode 
                AND b.ActivityTypeID BETWEEN 6 AND 10 AND b.SkillCode IS NOT NULL
                AND b.Date >=@beginDate AND b.Date < @endDate) -
            (
            SELECT COUNT(*) 
            FROM mmapps.dbo.t_EMS_BM_ChartData AS b 
            WHERE a.BusinessUnitID = b.BusinessUnitID AND a.SkillCode = b.SkillCode 
                AND b.ActivityTypeID BETWEEN 6 AND 10 AND b.SkillCode IS NOT NULL
                AND b.Date >=@beginDate AND b.Date < @endDate
            )
            AS Actuals

        ,DATEADD(week,@count,@beginDate) AS DateCounted
        FROM @tempTable AS a
        WHERE a.DateCounted = (SELECT MAX(DateCounted) FROM @tempTable)
    END
    SET @count = @count + 1
END

SELECT * FROM @tempTable
ORDER BY DateCounted DESC

Answer 1

After a bit more research and trial and error, a simple addition of SET NOCOUNT ON; seemed to cure all my woes.