Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Excel Power Query Expand / Collapse
Author
Message
Posted Friday, August 9, 2013 2:49 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 6:42 AM
Points: 3,447, Visits: 1,475
Hi-

I am experimenting using Power Query in Excel 2010 and I would really love to use it to extract the results of a SQL Server stored procedure that I have. However, it looks to me that the only complete tables or views are available for the queries from Excel.

Does anyone have any experience or some tricks that might work to utilize a stored procedure?

Thanks,
Scott
Post #1482938
Posted Tuesday, August 13, 2013 7:04 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 6:42 AM
Points: 3,447, Visits: 1,475
I couldn't determine a way to use my existing stored procedure in Power Query, so I converted it to a view. Works great that way.
Post #1483708
Posted Tuesday, August 13, 2013 7:09 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:55 PM
Points: 13,357, Visits: 10,222
Scott Arendt (8/13/2013)
I couldn't determine a way to use my existing stored procedure in Power Query, so I converted it to a view. Works great that way.


Good to know, thanks for posting the solution.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1483712
Posted Tuesday, August 13, 2013 3:15 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 6:42 AM
Points: 3,447, Visits: 1,475
While I can get the Power Query to work great for me, as soon as I send it to someone else, they are prompt to enter credentials to access the data.

This would probably work great if the other users had accounts on the SQL Server, but they don't. I have tried setting the Excel Services Authentication Settings to None (which should use the connection string) and then saving the password in the connection string.

Every Google search on Power Query or Data Explorer comes up with the same examples (most by Pragmatic Works - which is the reason I am trying this out in the first place) but no real information on setting up the connection properties or best practices.

Any help would be greatly appreciated. BTW I am using Excel 2010 and SQL Server 2008
Post #1483998
Posted Wednesday, August 14, 2013 3:29 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:55 PM
Points: 13,357, Visits: 10,222
I believe you can somehow circumvent security by using the fast combine option.
Not 100% sure, it's also new for me




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1484149
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse