locked
retrieve data from a stored procedure using powerpivot RRS feed

  • Question

  • Hi there. I'm new to SQL and would like to import the below stored procedure to PowerPivot. PowerPivot says the statement is valid, but fails to import the stored procedure and returns this error "OLE DB or ODBC error: Changed database context to 'SiriusV1'.; 01000.An error occurred while processing table 'Query'.The current operation was cancelled because another operation in the transaction failed." Please assist. Thanks in advance

    USE [SiriusV1]

    BEGIN TRY
    DECLARE @RC int
    DECLARE @as_at_date datetime
    DECLARE @all_versions bit
    DECLARE @include_motor bit
    DECLARE @include_reserve bit
    SET NOCOUNT ON;
    -- TODO: Set parameter values here.

    SET @as_at_date = GETDATE();
    SET @all_versions = 0;
    SET @include_motor = 1;
    SET @include_reserve = 0;

    SET NOCOUNT OFF;
    EXECUTE @RC = [dbo].[usp_claims_by_reserve] 
       @as_at_date
      ,@all_versions
      ,@include_motor
      ,@include_reserve

    END TRY

    BEGIN CATCH
    SELECT
    ERROR_NUMBER() AS ErrorNumber
    ,ERROR_SEVERITY() AS ErrorSeverity
    ,ERROR_STATE() AS ErrorState
    ,ERROR_PROCEDURE() AS ErrorProcedure
    ,ERROR_LINE() AS ErrorLine
    ,ERROR_MESSAGE() AS ErrorMessage
    END CATCH
    GO

    Monday, July 14, 2014 12:13 PM

Answers

  • Hi ,

      

      1. As you will be selecting the database while establishing the connection itself , there is no need for USE statement to switch the database.

      2. I don't think batch statements with "GO" is supported in power pivot query.

      3. If there is an error in executing the proc the power pivot will throw error by itself and no need for a begin try statement. When there is an error , the result set returned (From Begin catch) will be completely different from the columns returned from the procedure and there will be a metadata mismatch and for sure it will run into problem. Remove the try catch block.

    Please  let us know if there is any significant requirement for which  try catch block is used.


    Best Regards Sorna

    Hi Tlotlang,

    Adding to what Sorna has already mentioned, simplifying your SQL statements to the following should work:

    DECLARE @as_at_date datetime,
            @all_versions bit,
            @include_motor bit,
            @include_reserve bit;
    
    
    SET NOCOUNT ON;
    
    SET @as_at_date = GETDATE();
    SET @all_versions = 0;
    SET @include_motor = 1;
    SET @include_reserve = 0;
    
    EXEC [dbo].[usp_claims_by_reserve] 
      @as_at_date
      ,@all_versions
      ,@include_motor
      ,@include_reserve;


    Regards,

    Michael

    Please remember to mark a post that answers your question as an answer...If a post doesn't answer your question but you've found it helpful, please remember to vote it as helpful :)

    Website: nimblelearn.com, Blog: nimblelearn.com/blog, Twitter: @nimblelearn

    • Proposed as answer by Michael Amadi Wednesday, July 16, 2014 9:16 AM
    • Marked as answer by Charlie Liao Thursday, July 17, 2014 9:10 AM
    • Edited by Michael Amadi Thursday, July 17, 2014 9:14 AM Minor edit
    Wednesday, July 16, 2014 9:14 AM

All replies

  • Hi ,

      

      1. As you will be selecting the database while establishing the connection itself , there is no need for USE statement to switch the database.

      2. I don't think batch statements with "GO" is supported in power pivot query.

      3. If there is an error in executing the proc the power pivot will throw error by itself and no need for a begin try statement. When there is an error , the result set returned (From Begin catch) will be completely different from the columns returned from the procedure and there will be a metadata mismatch and for sure it will run into problem. Remove the try catch block.

    Please  let us know if there is any significant requirement for which  try catch block is used.


    Best Regards Sorna

    • Proposed as answer by Michael Amadi Wednesday, July 16, 2014 9:16 AM
    Monday, July 14, 2014 1:00 PM
  • Hi ,

      

      1. As you will be selecting the database while establishing the connection itself , there is no need for USE statement to switch the database.

      2. I don't think batch statements with "GO" is supported in power pivot query.

      3. If there is an error in executing the proc the power pivot will throw error by itself and no need for a begin try statement. When there is an error , the result set returned (From Begin catch) will be completely different from the columns returned from the procedure and there will be a metadata mismatch and for sure it will run into problem. Remove the try catch block.

    Please  let us know if there is any significant requirement for which  try catch block is used.


    Best Regards Sorna

    Hi Tlotlang,

    Adding to what Sorna has already mentioned, simplifying your SQL statements to the following should work:

    DECLARE @as_at_date datetime,
            @all_versions bit,
            @include_motor bit,
            @include_reserve bit;
    
    
    SET NOCOUNT ON;
    
    SET @as_at_date = GETDATE();
    SET @all_versions = 0;
    SET @include_motor = 1;
    SET @include_reserve = 0;
    
    EXEC [dbo].[usp_claims_by_reserve] 
      @as_at_date
      ,@all_versions
      ,@include_motor
      ,@include_reserve;


    Regards,

    Michael

    Please remember to mark a post that answers your question as an answer...If a post doesn't answer your question but you've found it helpful, please remember to vote it as helpful :)

    Website: nimblelearn.com, Blog: nimblelearn.com/blog, Twitter: @nimblelearn

    • Proposed as answer by Michael Amadi Wednesday, July 16, 2014 9:16 AM
    • Marked as answer by Charlie Liao Thursday, July 17, 2014 9:10 AM
    • Edited by Michael Amadi Thursday, July 17, 2014 9:14 AM Minor edit
    Wednesday, July 16, 2014 9:14 AM
  • Thanks Michael, worked perfectly fine.

    Onyx12

    Wednesday, July 16, 2014 9:26 AM
  • Switching NOCOUNT to ON fix this issue

    Example

    SET NOCOUNT ON;

    Tuesday, June 19, 2018 2:37 PM