Search This Blog

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

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


Issue :OLE DB or ODBC error: Dec  1 2012 12:00AM; 01000.
An error occurred while processing table 'Query'.
The current operation was cancelled because another operation in the transaction failed.

Issue Description:While Working with Excel Pivot Table The complex SQL query is working fine in SQL Management but when running the Same query in excel power pivot it was  giving the error.

The excel is validating and running  the query without any issue. but when saving the query throwing below 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.


Reason:The Issue comes When working with SQL Complex queries and you are using an insert statement in your sql code.


Fix/Solution-Set Nocount On above the Insert Statement in the sql Code 

In my case i was inserting some records in temp table so added Set Nocount On above the Insert statement and No count Off after finishing Insert statement has fixed the issue.

--Below is the insert  Part of my SQL
WHILE @currct <= @Mct
       BEGIN
SET NOCOUNT ON
              INSERT INTO @MyTempTable1 (datetime, [ Role], Work)
                     SELECT DATEADD(MM, @currct, @FD) AS datetime
                           ,ru.[ Role]
                           , 0 AS Work
                           FROM Project AS ru
                         
              SET @currct = @currct + 1
              SET @CurDate = DATEADD (mm, 1, @CurDate)
 SET NOCOUNT Off
       END
  
   Adding Nocount on will fix the issue.

NOTE: If again  you will get the same error set NOCOunt on above your main Select query.

14 comments:

  1. THANKS SOOOOOOO MUCCHHHH....

    ReplyDelete
    Replies
    1. Welcome if you need any more help Regarding SQL and BI you can send me an Email at rakeshrawat.sql@gmail.com

      Delete
  2. Had same issue when using a query to EXEC a stored procedure (via linked server). Added SET NOCOUNT ON prior to EXEC statement. Problem solved. Thanks!

    ReplyDelete
    Replies
    1. Welcome if you need any help Regarding SQL and BI you can send me an Email at rakeshrawat.sql@gmail.com

      Delete
  3. Thanks. In my case the query refers to an obsolete view. And it was giving this error.
    Worked once it's fixed.

    ReplyDelete
  4. Think of a rotary hammer drill as a larger, more powerful version of a hammer drill with an added bonus. awesome blog

    ReplyDelete
  5. This is amazing!!! Worked perfectly! Thank you so much!!!!

    ReplyDelete
  6. This is Good information about this topic..I like it.. wordpress database fix ..Keep it Up!

    ReplyDelete
  7. Truly, this article is really one of the very best in the history of articles. I am a antique ’Article’ collector and I sometimes read some new articles if I find them interesting. And I found this one pretty fascinating and it should go into my collection. Very good work! power Bi | power bi vs tableau

    ReplyDelete
  8. THANK YOU, THANK YOU, THANK YOU, THANK YOU!!!!!!!

    ReplyDelete
  9. Its very informative blog and useful article thank you for sharing with us , keep posting learn more about mulesoft online Course india | mulesoft online training india

    ReplyDelete
  10. Really nice post. provided a helpful information.I hope that you will post more updates like this
    MSBI Online training with 100% job Assistance and 24 X 7 Online Support. Visit us: msbi training online | msbi online training
    Contact Information:
    USA: +1 7327039066
    INDIA: +91 8885448788 , 9550102466
    Email: info@onlineitguru.com

    ReplyDelete
  11. Thanks! NOCOUNT=ON resolved my PivotTable + stored proc problem

    ReplyDelete