Need to xport SQL Stored procs to MS Excel from ADO.net 
Author Message
 Need to xport SQL Stored procs to MS Excel from ADO.net

Actually, there's a little more to it;

SQL Server = SQLSvr01
3 Stored Procedures = SQLSP01, SQLSP02, SQLSP03

I'd like to create a WinForm with a Listbox and a button which would perform
the following;

1.  I want to populate the listbox with the Stored Procedure's names,
residing on the server, beginning with the prefix SQL (example;
Server=SQLSvr01, SP=SQLSP01).
2.  I want to be able to select any or all of the Stored Procedures from the
list in the ListBox, and press the button, which would then run those stored
procedures and export the results to an excel spreadsheet (each on a
seperate tab).

Even if I could figure out 1., I'd be a happy camper.  The only way I've
been able to do anything close to this so far, has been to create a static
SQLConnection and several static SQL Command classes and DataAdapters.  I
would like the code to connect to the SQL server and bring back the names of
the current stored procedures with the above criteria.  Does anyone have
this, or a webpage with a similar task?
Thanks!
Steve



Thu, 22 Sep 2005 22:20:49 GMT  
 Need to xport SQL Stored procs to MS Excel from ADO.net
Steve,

You could get names of all stored procedures from the system SQL Server
table which has name SYSOBJECTS. You query, which willl select names of all
SPs, which begin from SQL will look like

SELECT [NAME] FROM SYSOBJECT WHERE TYPE='P' AND [NAME] LIKE 'SQL%'

--
Val Mazur
Microsoft MVP


Quote:
> Actually, there's a little more to it;

> SQL Server = SQLSvr01
> 3 Stored Procedures = SQLSP01, SQLSP02, SQLSP03

> I'd like to create a WinForm with a Listbox and a button which would
perform
> the following;

> 1.  I want to populate the listbox with the Stored Procedure's names,
> residing on the server, beginning with the prefix SQL (example;
> Server=SQLSvr01, SP=SQLSP01).
> 2.  I want to be able to select any or all of the Stored Procedures from
the
> list in the ListBox, and press the button, which would then run those
stored
> procedures and export the results to an excel spreadsheet (each on a
> seperate tab).

> Even if I could figure out 1., I'd be a happy camper.  The only way I've
> been able to do anything close to this so far, has been to create a static
> SQLConnection and several static SQL Command classes and DataAdapters.  I
> would like the code to connect to the SQL server and bring back the names
of
> the current stored procedures with the above criteria.  Does anyone have
> this, or a webpage with a similar task?
> Thanks!
> Steve



Fri, 23 Sep 2005 02:00:50 GMT  
 Need to xport SQL Stored procs to MS Excel from ADO.net
Thanks so much, Val!

I would use that to populate a list of names.
Then, when I select one or more of the names (stored procedure names), is it
possible to run each of the Stored Procedures and have the results fill
seperate datareaders and/or excel sheets (at the same time)?

My biggest hurdle has been trying to figure out how to run more than one
stored procedure and have them populate a datagrid, reader, and/or
spreadsheet at once.  I want to press a button and have results returned
from all of the stored procedures I selected.  So far, I can only run one at
a time.  I bought the Microsoft "Prog SQL 2K with MS VB.NET" by Rick Dobson,
but still cannot figure this out!

Thank you, thank you!


Quote:
> Steve,

> You could get names of all stored procedures from the system SQL Server
> table which has name SYSOBJECTS. You query, which willl select names of
all
> SPs, which begin from SQL will look like

> SELECT [NAME] FROM SYSOBJECT WHERE TYPE='P' AND [NAME] LIKE 'SQL%'

> --
> Val Mazur
> Microsoft MVP



Fri, 23 Sep 2005 02:53:59 GMT  
 Need to xport SQL Stored procs to MS Excel from ADO.net
Hi Steve,

By default you cannot get one recordset from multiple stored procedures or
SQL queries. What you could do is to open multiple recordsets, based on each
SP and populate your grid (not DataGrid) using your own code. In that case
you would need to use unbound mode with FlexGrid or some other sort of grid
control, which supports unbound mode. Another possible way is to prepare one
SP, which will call multiple SPs and will prepare one result, which will be
returned to one recordset.

--
Val Mazur
Microsoft MVP


Quote:
> Thanks so much, Val!

> I would use that to populate a list of names.
> Then, when I select one or more of the names (stored procedure names), is
it
> possible to run each of the Stored Procedures and have the results fill
> seperate datareaders and/or excel sheets (at the same time)?

> My biggest hurdle has been trying to figure out how to run more than one
> stored procedure and have them populate a datagrid, reader, and/or
> spreadsheet at once.  I want to press a button and have results returned
> from all of the stored procedures I selected.  So far, I can only run one
at
> a time.  I bought the Microsoft "Prog SQL 2K with MS VB.NET" by Rick
Dobson,
> but still cannot figure this out!

> Thank you, thank you!



> > Steve,

> > You could get names of all stored procedures from the system SQL Server
> > table which has name SYSOBJECTS. You query, which willl select names of
> all
> > SPs, which begin from SQL will look like

> > SELECT [NAME] FROM SYSOBJECT WHERE TYPE='P' AND [NAME] LIKE 'SQL%'

> > --
> > Val Mazur
> > Microsoft MVP



Fri, 23 Sep 2005 19:20:31 GMT  
 Need to xport SQL Stored procs to MS Excel from ADO.net
Thanks again Val.
I'm not trying to get one recordset from multiple SPs, actually.  I want to
fire off multiple SPs and have them all fill seperate recordsets which will
poplute seperate spreadsheets.  Some day when I get THAT figured out, I'm
going to set up a service to autmatically run it every day.

I'm trying to do this in Winforms, but it's starting feel like I'd have a
better chance with ASP.net or Excel 2K VBA.


Hi Steve,

By default you cannot get one recordset from multiple stored procedures or
SQL queries. What you could do is to open multiple recordsets, based on each
SP and populate your grid (not DataGrid) using your own code. In that case
you would need to use unbound mode with FlexGrid or some other sort of grid
control, which supports unbound mode. Another possible way is to prepare one
SP, which will call multiple SPs and will prepare one result, which will be
returned to one recordset.

--
Val Mazur
Microsoft MVP


Quote:
> Thanks so much, Val!

> I would use that to populate a list of names.
> Then, when I select one or more of the names (stored procedure names), is
it
> possible to run each of the Stored Procedures and have the results fill
> seperate datareaders and/or excel sheets (at the same time)?

> My biggest hurdle has been trying to figure out how to run more than one
> stored procedure and have them populate a datagrid, reader, and/or
> spreadsheet at once.  I want to press a button and have results returned
> from all of the stored procedures I selected.  So far, I can only run one
at
> a time.  I bought the Microsoft "Prog SQL 2K with MS VB.NET" by Rick
Dobson,
> but still cannot figure this out!

> Thank you, thank you!



> > Steve,

> > You could get names of all stored procedures from the system SQL Server
> > table which has name SYSOBJECTS. You query, which willl select names of
> all
> > SPs, which begin from SQL will look like

> > SELECT [NAME] FROM SYSOBJECT WHERE TYPE='P' AND [NAME] LIKE 'SQL%'

> > --
> > Val Mazur
> > Microsoft MVP



Sat, 24 Sep 2005 09:35:14 GMT  
 Need to xport SQL Stored procs to MS Excel from ADO.net
Steve,

Do you know how to open one recordset from SP? I am pretty sure you know
that. Multiple recordsets will work exact same way, but you would need to
get list of SPs somehow into your procedure, which will loop through list of
those SPs and will call them one-by-one. It could be an array of SP names

--
Val Mazur
Microsoft MVP


Quote:
> Thanks again Val.
> I'm not trying to get one recordset from multiple SPs, actually.  I want
to
> fire off multiple SPs and have them all fill seperate recordsets which
will
> poplute seperate spreadsheets.  Some day when I get THAT figured out, I'm
> going to set up a service to autmatically run it every day.

> I'm trying to do this in Winforms, but it's starting feel like I'd have a
> better chance with ASP.net or Excel 2K VBA.



> Hi Steve,

> By default you cannot get one recordset from multiple stored procedures or
> SQL queries. What you could do is to open multiple recordsets, based on
each
> SP and populate your grid (not DataGrid) using your own code. In that case
> you would need to use unbound mode with FlexGrid or some other sort of
grid
> control, which supports unbound mode. Another possible way is to prepare
one
> SP, which will call multiple SPs and will prepare one result, which will
be
> returned to one recordset.

> --
> Val Mazur
> Microsoft MVP



> > Thanks so much, Val!

> > I would use that to populate a list of names.
> > Then, when I select one or more of the names (stored procedure names),
is
> it
> > possible to run each of the Stored Procedures and have the results fill
> > seperate datareaders and/or excel sheets (at the same time)?

> > My biggest hurdle has been trying to figure out how to run more than one
> > stored procedure and have them populate a datagrid, reader, and/or
> > spreadsheet at once.  I want to press a button and have results returned
> > from all of the stored procedures I selected.  So far, I can only run
one
> at
> > a time.  I bought the Microsoft "Prog SQL 2K with MS VB.NET" by Rick
> Dobson,
> > but still cannot figure this out!

> > Thank you, thank you!



> > > Steve,

> > > You could get names of all stored procedures from the system SQL
Server
> > > table which has name SYSOBJECTS. You query, which willl select names
of
> > all
> > > SPs, which begin from SQL will look like

> > > SELECT [NAME] FROM SYSOBJECT WHERE TYPE='P' AND [NAME] LIKE 'SQL%'

> > > --
> > > Val Mazur
> > > Microsoft MVP



Sat, 24 Sep 2005 20:04:31 GMT  
 Need to xport SQL Stored procs to MS Excel from ADO.net
I'll try adding an array list which is populated by a table.  It will either
look directly at the system objects in the SQL database, or I'll have a
table which comprises an SP Id and name.

Thanks, Val!


Steve,

Do you know how to open one recordset from SP? I am pretty sure you know
that. Multiple recordsets will work exact same way, but you would need to
get list of SPs somehow into your procedure, which will loop through list of
those SPs and will call them one-by-one. It could be an array of SP names

--
Val Mazur
Microsoft MVP


Quote:
> Thanks again Val.
> I'm not trying to get one recordset from multiple SPs, actually.  I want
to
> fire off multiple SPs and have them all fill seperate recordsets which
will
> poplute seperate spreadsheets.  Some day when I get THAT figured out, I'm
> going to set up a service to autmatically run it every day.

> I'm trying to do this in Winforms, but it's starting feel like I'd have a
> better chance with ASP.net or Excel 2K VBA.



> Hi Steve,

> By default you cannot get one recordset from multiple stored procedures or
> SQL queries. What you could do is to open multiple recordsets, based on
each
> SP and populate your grid (not DataGrid) using your own code. In that case
> you would need to use unbound mode with FlexGrid or some other sort of
grid
> control, which supports unbound mode. Another possible way is to prepare
one
> SP, which will call multiple SPs and will prepare one result, which will
be
> returned to one recordset.

> --
> Val Mazur
> Microsoft MVP



> > Thanks so much, Val!

> > I would use that to populate a list of names.
> > Then, when I select one or more of the names (stored procedure names),
is
> it
> > possible to run each of the Stored Procedures and have the results fill
> > seperate datareaders and/or excel sheets (at the same time)?

> > My biggest hurdle has been trying to figure out how to run more than one
> > stored procedure and have them populate a datagrid, reader, and/or
> > spreadsheet at once.  I want to press a button and have results returned
> > from all of the stored procedures I selected.  So far, I can only run
one
> at
> > a time.  I bought the Microsoft "Prog SQL 2K with MS VB.NET" by Rick
> Dobson,
> > but still cannot figure this out!

> > Thank you, thank you!



> > > Steve,

> > > You could get names of all stored procedures from the system SQL
Server
> > > table which has name SYSOBJECTS. You query, which willl select names
of
> > all
> > > SPs, which begin from SQL will look like

> > > SELECT [NAME] FROM SYSOBJECT WHERE TYPE='P' AND [NAME] LIKE 'SQL%'

> > > --
> > > Val Mazur
> > > Microsoft MVP



Sun, 25 Sep 2005 01:53:30 GMT  
 
 [ 7 post ] 

 Relevant Pages 

1. Executing MS SQL Server 2000 stored procs via MS Access 2000

2. SQL Server stored procs w/ ADO

3. SQL Stored Procs & ADO Recordsets

4. Using SQL Stored Procs to perform inserts/upates/deletes from ADO

5. Passing Params To SQL Server Stored Procs On ADO Data Control RecordSource Property Page

6. Calling Stored Procs from SQL Server

7. Locking in VB5 and SQL Server 6.5 using stored procs

8. ADO, Oracle, Resutlsets and Stored Procs

9. VB Sybase/SQL Server Stored Procs Question

10. can't see all stored procs using sql driver

11. Can't see all stored procs in SQL server

12. Stored Procs in SQL Server

 

 
Powered by phpBB® Forum Software