Powerpivot with stored procedure

Question

There's an older thread regarding stored procedures being used as a source in PowerPivot, but I still can't get it to work. I get the 'OLE DB or ODBC error' when trying to run it.

I use Powerpivot V2. For my stored procedure I use the SET NOCOUNT ON option. I create a temp table and then run a cursor to collect data from our various databases to put into this single temp table. When the cursor finishes, I select everything from that temp table. The procedure works fine in Management Studios and contains no parameters. I internally declare some variables for the cursor though.

Validating the store procedure works fine. Importing though gives me that error.

Any help will be welcomed. I tried creating a view for my data but the number of joint tables needed are to many for setting up a series of 'union' commands.

Thank you

Answer 1

Hello,

It tested it with the SP below and it works without any problems. May the code of your SP is there reason or you haven't installed the latest release from SQL Server 2012 RTM feature pack.

CREATE PROCEDURE dbo.spPPTest
AS
BEGIN
    SET NOCOUNT ON;
    
    CREATE TABLE #test (ID int NOT NULL, wert varchar(20));
    INSERT INTO #test VALUES (1, 'Hello');
    INSERT INTO #test VALUES (2, 'World');
    
    SELECT * FROM #test;
END

---

Olaf Helper
* cogito ergo sum * errare humanum est * quote erat demonstrandum *
Wenn ich denke, ist das ein Fehler und das beweise ich täglich
Blog Xing

Answer 2

Hello Olaf,

thanks for that. I tried a simple stored procedure like that and it works fine in my PowerPivot. However, since I have a series of databases to take data from, my sql generally looks like that:

Declare @sql nvarchar(4000), @DB nvarchar(3)

Create table #tablename ( fields needed)

Declare My_Cursor CURSOR FOR Select DB from DBTable

Open My_Cursor

Fetch FROM My_Cursor into @DB

WHILE @@FETCH_STATUS = 0

BEGIN

Set @sql = N'Insert #tablename

Select (fields needed) from #table

exec(sql)

Fetch next FROM My_Cursor into @DB

END

Select * from #tablename

close MY_Cursor

deallocate MY_Cursor

I quickly typed how my code works, please forgive any typos. I hope you can get a picture of what I need to do. Because of the number of the tables were the data is stored, I really need the cursor. Can you suggest something for me? Thank you in advance.

Answer 3

I tested it with a similar logic (see below) and it works. Are you really only using EXEC (@sql) to exec your dynamic sql or also sp_executesql?

ALTER PROCEDURE dbo.spPPTest
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @id int;
    DECLARE @name nvarchar(128);
    DECLARE @sql nvarchar(2000);
    
    CREATE TABLE #test (obj_id int NOT NULL, name nvarchar(128));
    
    DECLARE My_Cursor CURSOR 
        FOR Select object_id, name from sys.objects;
    
    Open My_Cursor;
    FETCH FROM My_Cursor INTO @id, @name;
    WHILE @@FETCH_STATUS = 0 
    BEGIN 
        SET @sql = N'INSERT INTO #test VALUES (' + CONVERT(varchar(10), @id) + ', ''' + @name + ''')';
        EXEC sp_executesql @sql;
        FETCH FROM My_Cursor INTO @id, @name;    
    END    
    close MY_Cursor;
    deallocate MY_Cursor;
       
    SELECT * FROM #test;
 
END
GO

---

Olaf Helper
* cogito ergo sum * errare humanum est * quote erat demonstrandum *
Wenn ich denke, ist das ein Fehler und das beweise ich täglich
Blog Xing

Answer 4

I only use exec(@sql), but I tried now the exec sp_executesql @sql with the same result:

'OLE DB or ODBC error: (my first insert command)

An error occurred while the partition, with the ID of 'Query_e4f659a5-78bd-461e-bbdc-19a2c603c213', Name of 'Query' was being processed.

The current operation was cancelled because another operation in the transaction failed.

Out of line object 'DataSource', referring to ID(s) '11db6c62-995e-4d14-8530-92d9b4a0fe0d', has been specified but has not been used.

Out of line object 'DataSourceView', referring to ID(s) 'Temp_DSV', has been specified but has not been used.'

Does this error message mean anything to you? What do you put in the PowerPivot 'Specify a SQL Query' window?

Answer 5

I entered only "EXEC dbo.spPPTest" in the query window.

DataSource/DataSourceView; are you maybe working in a "Tabular BI Semantic Model" or really in PowerPivot for Excel?

---

Olaf Helper
* cogito ergo sum * errare humanum est * quote erat demonstrandum *
Wenn ich denke, ist das ein Fehler und das beweise ich täglich
Blog Xing

Answer 6

I open Excel > Powerpivot Window>  From Database > From SQL Server. I go through the 'Table Import Wizard', give Database name etc. On second screen I select 'Write a query that will specify the data to import' and then set the SQL Statement.

I've tried another similar stored procedure I have, with the same results. I'm thinking of automatically exporting my results in a text file to load in PowerPivot.

Answer 7

That's the same I done it; so back to my first question, have you installed this release? http://www.microsoft.com/en-us/download/details.aspx?id=29074

---

Olaf Helper
* cogito ergo sum * errare humanum est * quote erat demonstrandum *
Wenn ich denke, ist das ein Fehler und das beweise ich täglich
Blog Xing

Answer 8

Yes, this is the version of PowerPivot I'm running. Could I be missing something from the SQL side? I have to confirm with my Infrastructure guys, but what about SQL versions? Patches?

Answer 9

No, I don't think so, when you execute the SP in SSMS, then it work, so I think it's more resided on client side; that's why I ask again about your PP version.

---

Olaf Helper
* cogito ergo sum * errare humanum est * quote erat demonstrandum *
Wenn ich denke, ist das ein Fehler und das beweise ich täglich
Blog Xing

Answer 10

Thank you so much for looking into this, but this is my PowerPivot version. I assume it is the latest one.

Answer 11

I found the problem! It's very embarassing but I have to admit it. I had a 'print' command for debugging purposes inside the cursor. I completely missed it in all the code. I'm sorry for that.

Thank you Olaf for all the answers and putting up with me.

Answer 12

It was a pleasure for me and I am glad you found the solution.

---

Olaf Helper
* cogito ergo sum * errare humanum est * quote erat demonstrandum *
Wenn ich denke, ist das ein Fehler und das beweise ich täglich
Blog Xing