Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data return from SQL Stored procedure... | Excel Discussion (Misc queries) | |||
hyperlink, macro, stored procedure? | Excel Worksheet Functions | |||
Extract data from Stored procedure | Excel Discussion (Misc queries) | |||
How do I access data stored in a SQL server for vlookup function? | Excel Worksheet Functions | |||
Execute stored procedure using DAO | Excel Programming |