Hi -
Firstly, I have very limited knowledge of SQL (although I am working my way through soem text books). -
So I've decideed to bin Access in favour of sql 2008 server (using express with Managemebnt studio) and I'm trying to understand the most simplistic way to pull data (query) into Excel(2007).
I had a very simplistic Access dB which contained several flat files, which consolidated the data (with various union/statement queries) which in turn was then pulled into Excel as a pivot table report.
So after migrating to SQL 2008 Express, I can execute stored procedures from Excel without issue as long as the stored procedure(SP) contains a single query. However if I attempt to run a SP which contains chained queries/nested SP executions or any other type of instruction Excel throws up a typical "..query did not run, or database table could not be opened".
The SP will execute without issue from within management studio.
Currently the means in which I pull the data into Excel is through an external connection via other sources / SQL Server and then modifying the connection properties/command type to SQL and commnad text to "exec <SP>"
I have a feeling, this is probably not the "correct" way to do this
Cheers, Paul.
EDIT - using VIEWs was my friend. Cheers!
Firstly, I have very limited knowledge of SQL (although I am working my way through soem text books). -
So I've decideed to bin Access in favour of sql 2008 server (using express with Managemebnt studio) and I'm trying to understand the most simplistic way to pull data (query) into Excel(2007).
I had a very simplistic Access dB which contained several flat files, which consolidated the data (with various union/statement queries) which in turn was then pulled into Excel as a pivot table report.
So after migrating to SQL 2008 Express, I can execute stored procedures from Excel without issue as long as the stored procedure(SP) contains a single query. However if I attempt to run a SP which contains chained queries/nested SP executions or any other type of instruction Excel throws up a typical "..query did not run, or database table could not be opened".
The SP will execute without issue from within management studio.
Currently the means in which I pull the data into Excel is through an external connection via other sources / SQL Server and then modifying the connection properties/command type to SQL and commnad text to "exec <SP>"
I have a feeling, this is probably not the "correct" way to do this
Cheers, Paul.
EDIT - using VIEWs was my friend. Cheers!
Last edited: