Wednesday, May 30, 2012 9:48 AM
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.
Wednesday, May 30, 2012 10:22 AM
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
Wednesday, May 30, 2012 10:39 AM
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
Fetch FROM My_Cursor into @DB
WHILE @@FETCH_STATUS = 0
Set @sql = N'Insert #tablename
Select (fields needed) from #table
Fetch next FROM My_Cursor into @DB
Select * from #tablename
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.
Wednesday, May 30, 2012 11:02 AM
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
Wednesday, May 30, 2012 11:13 AM
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?
Wednesday, May 30, 2012 11:25 AM
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?
Wednesday, May 30, 2012 11:45 AM
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.
Wednesday, May 30, 2012 11:52 AMThat'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
Wednesday, May 30, 2012 1:17 PMYes, 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?
Wednesday, May 30, 2012 1:53 PMNo, 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.
Wednesday, May 30, 2012 3:00 PM
Thank you so much for looking into this, but this is my PowerPivot version. I assume it is the latest one.
Friday, June 01, 2012 9:07 AM
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.
- Marked As Answer by Elvis LongMicrosoft Contingent Staff, Moderator Friday, June 08, 2012 2:45 AM
Friday, June 01, 2012 2:55 PM