How can I grab data from a SQL Server stored procedure
 

Hi,

I want to place an excel sheet on our corporate portal site that connects to
a stored procedure on SQL Server 2000, passess two parameters and grabs
data.

When I connected to SQL Server, I was able to see tables and views but not
stored procedures. How can I connect to the DB and grab data from a stored
procedure? Thanks,

Sam

How can I grab data from a SQL Server stored procedure
 

Hey Patrick,

Thanks for your response. I'm connecting to the data source through a DSN.

My parameters will have different values every time user runs the SP. The
question is will I be able to pass these paramaters to Excel so that it will
pass them to the SP and receive the results

Thanks,

Sam
"Patrick Molloy" wrote in message
...
you don't explain how you connected tot he DB, but it
sounds like you're using Query Analyser.

A VIEW works much like a table, though it allows a
greater degree of complexity. A VIEW is also, a "view"
like a table...hence the rather obvious name.
Stored Procedures on the other hand need executing...much
a you would a procedure in VB/VBA
SP's offer a high degree of flexibility - its rather like
writing VB code, but using TSQL instead of VB, but its
very similar.
In Query Analyser type in the SP name, followed by any
parameters then hit F5

In VB/VBA once you've set an ADO connection to the
database, you can populate a recordset by Opening it with
some sql code
eg
"SELECT * FROM MyTable"
or if it's an SP
"usp_My_Stored_Proc"

if the sp returns a table, then your recordset will be
populated


Patrick Molloy
Microsoft Excel MVP

-----Original Message-----
Hi,

I want to place an excel sheet on our corporate portal
site that connects to
a stored procedure on SQL Server 2000, passess two
parameters and grabs
data.

When I connected to SQL Server, I was able to see tables
and views but not
stored procedures. How can I connect to the DB and grab
data from a stored
procedure? Thanks,

Sam


.

How can I grab data from a SQL Server stored procedure
 

That's a bit confusing.
A DSN is simply th econnection.
You pass parameters TO the database SP FROM Excel

SQL = "sp_MyStoredProc " & param1 [ & "," & Param2 ]...

OR

SQL = "SELECT * From " & sTableName
SQL = SQL & " WHERE " & param1 & "='" & condition1 & "';"


Patrick Molloy
Microsoft Excel MVP

-----Original Message-----
Hey Patrick,

Thanks for your response. I'm connecting to the data
source through a DSN.

My parameters will have different values every time user
runs the SP. The
question is will I be able to pass these paramaters to
Excel so that it will
pass them to the SP and receive the results

Thanks,

Sam
"Patrick Molloy" wrote in
message
...
you don't explain how you connected tot he DB, but it
sounds like you're using Query Analyser.

A VIEW works much like a table, though it allows a
greater degree of complexity. A VIEW is also, a "view"
like a table...hence the rather obvious name.
Stored Procedures on the other hand need
executing...much
a you would a procedure in VB/VBA
SP's offer a high degree of flexibility - its rather
like
writing VB code, but using TSQL instead of VB, but its
very similar.
In Query Analyser type in the SP name, followed by any
parameters then hit F5

In VB/VBA once you've set an ADO connection to the
database, you can populate a recordset by Opening it
with
some sql code
eg
"SELECT * FROM MyTable"
or if it's an SP
"usp_My_Stored_Proc"

if the sp returns a table, then your recordset will be
populated


Patrick Molloy
Microsoft Excel MVP

-----Original Message-----
Hi,

I want to place an excel sheet on our corporate portal
site that connects to
a stored procedure on SQL Server 2000, passess two
parameters and grabs
data.

When I connected to SQL Server, I was able to see
tables
and views but not
stored procedures. How can I connect to the DB and
grab
data from a stored
procedure? Thanks,

Sam


.



.

How can I grab data from a SQL Server stored procedure
 

Using ADO, you can use a Command object's Parameters collection.

Alternatively, you may be able to use the Connection object directly.
I've seen evidence (but can't recall where!) that this approach has
performance benefits. However the syntax seems a bit hit-and-miss. For
details see the section 'Execute a command as a native method of a
Connection object' in the documentation:-

ADO 2.8 API Reference: Connection Object:
http://msdn.microsoft.com/library/de...mdaobj01_7.asp

--

"Sam" wrote in message ...
Hey Patrick,

Thanks for your response. I'm connecting to the data source through a DSN.

My parameters will have different values every time user runs the SP. The
question is will I be able to pass these paramaters to Excel so that it will
pass them to the SP and receive the results

Thanks,

Sam
"Patrick Molloy" wrote in message
...
you don't explain how you connected tot he DB, but it
sounds like you're using Query Analyser.

A VIEW works much like a table, though it allows a
greater degree of complexity. A VIEW is also, a "view"
like a table...hence the rather obvious name.
Stored Procedures on the other hand need executing...much
a you would a procedure in VB/VBA
SP's offer a high degree of flexibility - its rather like
writing VB code, but using TSQL instead of VB, but its
very similar.
In Query Analyser type in the SP name, followed by any
parameters then hit F5

In VB/VBA once you've set an ADO connection to the
database, you can populate a recordset by Opening it with
some sql code
eg
"SELECT * FROM MyTable"
or if it's an SP
"usp_My_Stored_Proc"

if the sp returns a table, then your recordset will be
populated


Patrick Molloy
Microsoft Excel MVP

-----Original Message-----
Hi,

I want to place an excel sheet on our corporate portal
site that connects to
a stored procedure on SQL Server 2000, passess two
parameters and grabs
data.

When I connected to SQL Server, I was able to see tables
and views but not
stored procedures. How can I connect to the DB and grab
data from a stored
procedure? Thanks,

Sam


.