Using SQLQuery in SSAS action leads to Error in Excel "No columns that Microsoft Excel can use were returned from this query"

Topics: User Forum
Jan 29, 2013 at 10:13 PM

I've created a stored procedure to return Detail for a pivot table cell and am attempting to use the SQLQuery function to run it from an action in my cube.  When I select the action in the pivot table I get the message "No columns that Microsoft Excel can use were returned from this query".  The procedure dumps data into a temp table and then selects that table to present the data.  

I've tried writing a test select statement directly in the actions tab and that works correctly so the syntax in my action is correct.  When I run the procedure in SSMS I get the records expected - but calling the procedure from the action throws that error in Excel.  

Anyone have any ideas on what is going on?   Is there an issue with using temp tables with this function?  I really need to get this up and running..

 

Thanks

Coordinator
Jan 29, 2013 at 11:09 PM

It depends on how you've coded your stored proc. My guess is that when SQL Query runs your proc in prepare mode that it cannot figure out the shape of the resultset which is why Excel returns the error about not finding any columns. Using temp tables or conditional logic that could return a variable number of columns can cause issue for actions. 

If you run SQL profiler while executing your action you should be able to see the sp_prepare calls that are made. Running those in SSMS might give you some hints as to why this is not working. (you may need to create a test sproc that just does a simple SELECT to how this differs)  If you are doing a "SELECT INTO" You might need to create your temp table manually first and then insert into it. Or maybe create a table variable and insert into that. 

Jan 30, 2013 at 8:49 PM

Thanks for the follow up.

The proc does create the temp table structure first and then do an insert. The last line runs a select statement (using field names not *) against that table. I thought that the metadata may be an issue so I tried to eliminate any ambiguity when I created it.

I tried a simple proc which dumped one field into a temp table and then selected from that temp table and the result was the same. Similarly when I tried a table variable the error remained. When the proc ran a select statement against a physical table it worked.

Which profiler options would I turn on to see the parsing messages – I can’t seem to find them?

Thanks for the help!

Linda

From: dgosbell [email removed]
Sent: Tuesday, January 29, 2013 6:10 PM
To: Linda Hart
Subject: Re: Using SQLQuery in SSAS action leads to Error in Excel "No columns that Microsoft Excel can use we... [ASStoredProcedures:431330]

From: dgosbell

It depends on how you've coded your stored proc. My guess is that when SQL Query runs your proc in prepare mode that it cannot figure out the shape of the resultset which is why Excel returns the error about not finding any columns. Using temp tables or conditional logic that could return a variable number of columns can cause issue for actions.

If you run SQL profiler while executing your action you should be able to see the sp_prepare calls that are made. Running those in SSMS might give you some hints as to why this is not working. (you may need to create a test sproc that just does a simple SELECT to how this differs) If you are doing a "SELECT INTO" You might need to create your temp table manually first and then insert into it. Or maybe create a table variable and insert into that.

IMPORTANT NOTICE: This e-mail message is intended to be received only by persons entitled to receive the confidential information it may contain. E-mail messages to clients of Vizion Solutions LLC may contain information that is confidential and legally privileged. Please do not read, copy, forward, or store this message unless you are an intended recipient of it. If you have received this message in error, please forward it to the sender and delete it completely from your computer system.
Coordinator
Jan 30, 2013 at 9:59 PM
If you run SQL Profiler against the relational engine I think you should see it issuing sp_prepare calls to get the metadata before it runs the actual proc when you call the proc via SQLQuery from SSAS.

Do you have SET NOCOUNT ON in your proc and have you made sure there are no PRINT statements. Record counts and print messages add a second "stream" to the output which could be confusing the parsing of the metadata.

Another possible approach may be to build a table valued function in the relational engine instead of a stored proc and do a select against that...
Feb 1, 2013 at 1:32 PM
<div> <p><span style="font-size:10.0pt; font-family:">I added Setting Nocount on to the test procedure and it now looks like this:</span></p> <p><img width="263" height="245" id="Picture_x0020_2" src="cid:image004.jpg@01CE0056.973CE160"></p> <p></p> <p><span style="font-size:10.0pt; font-family:">The profiler isn’t giving me much information…turned on all the options under the stored procedure and T-SQL sections of the profiler settings…</span></p> <p></p> <p><img width="898" height="236" id="Picture_x0020_1" src="cid:image003.png@01CE0054.FFDA42F0" alt="cid:image003.png@01CE0054.FFDA42F0"><span style="font-size:11.0pt; font-family:"></span></p> <p><span style="font-size:11.0pt; font-family:">I’m still getting the same issue in Excel. I’ll try a table function…I’ve never written one of those before so it may take me a bit to get it right – I’ll let you know if that works.</span></p> <p><span style="font-size:11.0pt; font-family:"></span></p> <p><span style="font-size:11.0pt; font-family:">If you have any other ideas – I’m all ears </span><span style="font-size:11.0pt; font-family:Wingdings; color:#1F497D">J</span><span style="font-size:11.0pt; font-family:"></span></p> <p><span style="font-size:11.0pt; font-family:"></span></p> <p><span style="font-size:11.0pt; font-family:">Thanks for all your help,</span></p> <p><span style="font-size:11.0pt; font-family:"></span></p> <p><span style="font-size:11.0pt; font-family:">Linda</span></p> <div> <div style="border:none; border-top:solid #E1E1E1 1.0pt; padding:3.0pt 0in 0in 0in"> <p><b><span style="font-size:11.0pt; font-family:">From:</span></b><span style="font-size:11.0pt; font-family:"> dgosbell [email removed] <br> <b>Sent:</b> Wednesday, January 30, 2013 4:59 PM<br> <b>To:</b> Linda Hart<br> <b>Subject:</b> Re: Using SQLQuery in SSAS action leads to Error in Excel &quot;No columns that Microsoft Excel can use we... [ASStoredProcedures:431330]</span></p> </div> </div> <p></p> <p><span style="font-size:9.0pt; font-family:">From: dgosbell</span></p> <div id="ThreadNotificationPostBody" style="margin-bottom:24.0pt"> <p><span style="font-size:9.0pt; font-family:">If you run SQL Profiler against the relational engine I think you should see it issuing sp_prepare calls to get the metadata before it runs the actual proc when you call the proc via SQLQuery from SSAS. Do you have SET NOCOUNT ON in your proc and have you made sure there are no PRINT statements. Record counts and print messages add a second &quot;stream&quot; to the output which could be confusing the parsing of the metadata. Another possible approach may be to build a table valued function in the relational engine instead of a stored proc and do a select against that...</span></p> </div> </div> IMPORTANT NOTICE: This e-mail message is intended to be received only by persons entitled to receive the confidential information it may contain. E-mail messages to clients of Vizion Solutions LLC may contain information that is confidential and legally privileged.
Please do not read, copy, forward, or store this message unless you are an intended recipient of it. If you have received this message in error, please forward it to the sender and delete it completely from your computer system.
Feb 1, 2013 at 1:51 PM
<div> <p><span style="font-size:10.0pt; font-family:">Oops – didn’t realize the pictures were going to look like that when sent….. Here it is without the pictures:</span></p> <p><span style="font-size:10.0pt; font-family:"></span></p> <p><span style="font-size:10.0pt; font-family:">I added Setting Nocount on to the test procedure and it now looks like this:</span></p> <p style="margin-left:.5in"><span style="font-size:9.0pt; font-family:">SET ANSI_NULLS ON</span></p> <p style="margin-left:.5in"><span style="font-size:9.0pt; font-family:">GO</span></p> <p style="margin-left:.5in"><span style="font-size:9.0pt; font-family:"></span></p> <p style="margin-left:.5in"><span style="font-size:9.0pt; font-family:">SET QUOTED_IDENTIFIER ON</span></p> <p style="margin-left:.5in"><span style="font-size:9.0pt; font-family:">GO</span></p> <p style="margin-left:.5in"><span style="font-size:9.0pt; font-family:"></span></p> <p style="margin-left:.5in"><span style="font-size:9.0pt; font-family:">set noCount on</span></p> <p style="margin-left:.5in"><span style="font-size:9.0pt; font-family:">Go</span></p> <p style="margin-left:.5in"><span style="font-size:9.0pt; font-family:"></span></p> <p style="margin-left:.5in"><span style="font-size:9.0pt; font-family:">ALTER procedure [dbo].[vp_rpt_test2]</span></p> <p style="margin-left:.5in"><span style="font-size:9.0pt; font-family:">as </span> </p> <p style="margin-left:.5in"><span style="font-size:9.0pt; font-family:">--drop table #temp</span></p> <p style="margin-left:.5in"><span style="font-size:9.0pt; font-family:">create table #temp ([Year] int)</span></p> <p style="margin-left:.5in"><span style="font-size:9.0pt; font-family:"></span></p> <p style="margin-left:.5in"><span style="font-size:9.0pt; font-family:">Insert into #temp ([Year])</span></p> <p style="margin-left:.5in"><span style="font-size:9.0pt; font-family:">select 2013 </span></p> <p style="margin-left:.5in"><span style="font-size:9.0pt; font-family:">--as Declare @test table ([Year] smallint)</span></p> <p style="margin-left:.5in"><span style="font-size:9.0pt; font-family:"></span></p> <p style="margin-left:.5in"><span style="font-size:9.0pt; font-family:">--insert into @test ([Year])</span></p> <p style="margin-left:.5in"><span style="font-size:9.0pt; font-family:">--select [YEAR] from DateTable</span></p> <p style="margin-left:.5in"><span style="font-size:9.0pt; font-family:"></span></p> <p style="margin-left:.5in"><span style="font-size:9.0pt; font-family:">select [Year] from #temp</span></p> <p style="margin-left:.5in"><span style="font-size:9.0pt; font-family:">GO</span></p> <p><span style="font-size:10.0pt; font-family:"></span></p> <p><span style="font-size:10.0pt; font-family:">The profiler isn’t giving me much information…turned on all the options under the stored procedure and T-SQL sections of the profiler settings…</span></p> <p style="margin-left:.5in"><span style="font-size:10.0pt; font-family:"></span></p> <p style="margin-left:.5in"><span style="font-size:9.0pt; font-family:">SP:CacheMiss exec vp_rpt_test2 </span></p> <p style="margin-left:.5in"><span style="font-size:9.0pt; font-family:">SQL:BatchStarting exec vp_rpt_test2 </span></p> <p style="margin-left:.5in"><span style="font-size:9.0pt; font-family:">SQL:StmtStarting exec vp_rpt_test2 </span></p> <p style="margin-left:.5in"><span style="font-size:9.0pt; font-family:">SP:CacheHit Microsoft SQL Server Analysis Services </span></p> <p style="margin-left:.5in"><span style="font-size:9.0pt; font-family:">SP:Starting exec vp_rpt_test2 </span></p> <p style="margin-left:.5in"><span style="font-size:9.0pt; font-family:">SP:StmtStarting create table #temp ([Year] int) </span></p> <p style="margin-left:.5in"><span style="font-size:9.0pt; font-family:">SP:StmtCompleted create table #temp ([Year] int)</span></p> <p style="margin-left:.5in"><span style="font-size:9.0pt; font-family:">SP:StmtStarting Insert into #temp ([Year])</span></p> <p style="margin-left:.5in"><span style="font-size:9.0pt; font-family:">select 2013 </span></p> <p style="margin-left:.5in"><span style="font-size:9.0pt; font-family:">--as Declare @test table ([Year] smallint)</span></p> <p style="margin-left:.5in"><span style="font-size:9.0pt; font-family:"></span></p> <p style="margin-left:.5in"><span style="font-size:9.0pt; font-family:">--insert into @test ([Year])</span></p> <p style="margin-left:.5in"><span style="font-size:9.0pt; font-family:">--select [YEAR] from DateTable</span></p> <p style="margin-left:.5in"><span style="font-size:9.0pt; font-family:"></span></p> <p style="margin-left:.5in"><span style="font-size:9.0pt; font-family:">SP:StmtCompleted Insert into #temp ([Year])</span></p> <p style="margin-left:.5in"><span style="font-size:9.0pt; font-family:">select 2013 </span></p> <p style="margin-left:.5in"><span style="font-size:9.0pt; font-family:">--as Declare @test table ([Year] smallint)</span></p> <p style="margin-left:.5in"><span style="font-size:9.0pt; font-family:"></span></p> <p style="margin-left:.5in"><span style="font-size:9.0pt; font-family:">--insert into @test ([Year])</span></p> <p style="margin-left:.5in"><span style="font-size:9.0pt; font-family:">--select [YEAR] from DateTable</span></p> <p style="margin-left:.5in"><span style="font-size:9.0pt; font-family:"></span></p> <p style="margin-left:.5in"><span style="font-size:9.0pt; font-family:">SP:StmtStarting select [Year] from #temp </span></p> <p style="margin-left:.5in"><span style="font-size:9.0pt; font-family:">SP:StmtCompleted select [Year] from #temp </span></p> <p style="margin-left:.5in"><span style="font-size:9.0pt; font-family:">SP:Completed exec vp_rpt_test2 </span></p> <p style="margin-left:.5in"><span style="font-size:9.0pt; font-family:">SQL:StmtCompleted exec vp_rpt_test2 </span></p> <p style="margin-left:.5in"><span style="font-size:9.0pt; font-family:">SQL:BatchCompleted exec vp_rpt_test2 </span></p> <p style="margin-left:.5in"><span style="font-size:9.0pt; font-family:">Audit Logout</span></p> <p><span style="font-size:10.0pt; font-family:"></span></p> <p><span style="font-size:10.0pt; font-family:"></span></p> <p><span style="font-size:10.0pt; font-family:">I’m still getting the same issue in Excel. I’ll try a table function…I’ve never written one of those before so it may take me a bit to get it right – I’ll let you know if that works.</span></p> <p><span style="font-size:10.0pt; font-family:"></span></p> <p><span style="font-size:10.0pt; font-family:">If you have any other ideas – I’m all ears </span><span style="font-size:10.0pt; font-family:Wingdings; color:#253340; background:white">J</span><span style="font-size:10.0pt; font-family:"></span></p> <p><span style="font-size:10.0pt; font-family:"></span></p> <p><span style="font-size:10.0pt; font-family:">Thanks for all your help,</span></p> <p><span style="font-size:10.0pt; font-family:">Linda</span><span style="font-size:10.0pt; font-family:"></span></p> <div> <div style="border:none; border-top:solid #E1E1E1 1.0pt; padding:3.0pt 0in 0in 0in"> <p><b><span style="font-size:10.0pt; font-family:">From:</span></b><span style="font-size:10.0pt; font-family:"> dgosbell [email removed] <br> <b>Sent:</b> Wednesday, January 30, 2013 4:59 PM<br> <b>To:</b> Linda Hart<br> <b>Subject:</b> Re: Using SQLQuery in SSAS action leads to Error in Excel &quot;No columns that Microsoft Excel can use we... [ASStoredProcedures:431330]</span></p> </div> </div> <p><span style="font-size:10.0pt"></span></p> <p><span style="font-size:10.0pt; font-family:">From: dgosbell</span></p> <div id="ThreadNotificationPostBody" style="margin-bottom:24.0pt"> <p><span style="font-size:10.0pt; font-family:">If you run SQL Profiler against the relational engine I think you should see it issuing sp_prepare calls to get the metadata before it runs the actual proc when you call the proc via SQLQuery from SSAS. Do you have SET NOCOUNT ON in your proc and have you made sure there are no PRINT statements. Record counts and print messages add a second &quot;stream&quot; to the output which could be confusing the parsing of the metadata. Another possible approach may be to build a table valued function in the relational engine instead of a stored proc and do a select against that...</span></p> </div> </div> IMPORTANT NOTICE: This e-mail message is intended to be received only by persons entitled to receive the confidential information it may contain. E-mail messages to clients of Vizion Solutions LLC may contain information that is confidential and legally privileged.
Please do not read, copy, forward, or store this message unless you are an intended recipient of it. If you have received this message in error, please forward it to the sender and delete it completely from your computer system.
Coordinator
Feb 1, 2013 at 3:47 PM
I believe you wrote your own .NET assembly. Can you post the code you used?
Feb 1, 2013 at 3:52 PM
Edited Feb 1, 2013 at 3:55 PM
<div>
<p><span style="font-size:11.0pt; font-family:">No – I grabbed the ASSP.dll and SQLQuery.dll off <span>Codeplex</span> and installed those</span></p> <p><span style="font-size:11.0pt; font-family:"></span></p> <p><span style="font-size:11.0pt; font-family:">Linda</span></p> <p><span style="font-size:11.0pt; font-family:"></span></p> <div> <div style="border:none; border-top:solid #E1E1E1 1.0pt; padding:3.0pt 0in 0in 0in"> <p><a name="_MailOriginal"><b><span style="font-size:11.0pt; font-family:">From:</span></b></a><span style=""><span style="font-size:11.0pt; font-family:"> furmangg [email removed] <br> <b>Sent:</b> Friday, February 1, 2013 10:48 AM<br> <b>To:</b> Linda Hart<br> <b>Subject:</b> Re: Using SQLQuery in SSAS action leads to Error in Excel &quot;No columns that Microsoft Excel can use we... [ASStoredProcedures:431330]</span></span></p> </div> </div> <p><span style=""></span></p> <p style=""><span style=""><span style="font-size:9.0pt; font-family:">From: furmangg</span></span></p> <div id="ThreadNotificationPostBody" style="margin-bottom:24.0pt">
<p><span style=""><span style="font-size:9.0pt; font-family:">I believe you wrote your own .NET assembly. Can you post the code you used?</span></span></p>
Feb 1, 2013 at 3:54 PM
I'm sorry - I don't understand why my email is formatting my responses like that....sorry
My response:
No – I grabbed the ASSP.dll and SQLQuery.dll off Codeplex and installed those
Coordinator
Feb 1, 2013 at 9:24 PM
The SET NOCOUNT ON needs to go inside your proc (after the "AS" keyword)
Feb 5, 2013 at 2:20 PM
Eureka! That did it! Thank you so much for your patience and advice. I really appreciate you taking the time to help me out.

Linda