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.
THANKS SOOOOOOO MUCCHHHH....
ReplyDeleteWelcome if you need any more help Regarding SQL and BI you can send me an Email at rakeshrawat.sql@gmail.com
DeleteHad 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!
ReplyDeleteWelcome if you need any help Regarding SQL and BI you can send me an Email at rakeshrawat.sql@gmail.com
DeleteGreat Worked for me
ReplyDeleteThanks. In my case the query refers to an obsolete view. And it was giving this error.
ReplyDeleteWorked once it's fixed.
Think of a rotary hammer drill as a larger, more powerful version of a hammer drill with an added bonus. awesome blog
ReplyDeleteThis is amazing!!! Worked perfectly! Thank you so much!!!!
ReplyDeleteThis is Good information about this topic..I like it.. wordpress database fix ..Keep it Up!
ReplyDeleteTruly, 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
ReplyDeleteTHANK YOU, THANK YOU, THANK YOU, THANK YOU!!!!!!!
ReplyDeleteIts 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
ReplyDeleteReally nice post. provided a helpful information.I hope that you will post more updates like this
ReplyDeleteMSBI 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
Thanks! NOCOUNT=ON resolved my PivotTable + stored proc problem
ReplyDelete