NEW LISTINGS 
HOT LISTINGS
TOP RATED
EDITOR PICK
ADD A LISTING
UPDATE A LISTING
GET RATED
UPGRADE A LISTING
HOT LISTINGS
TOP RATED
EDITOR PICK
ADD A LISTING
UPDATE A LISTING
GET RATED
UPGRADE A LISTING
SQLServerTools Object
2574
SQLServerTools Object
http://www.aspemporium.com/aspEmporium/examples/SQLServerTools_object.asp
This one's like a double whammie! Welcome to the world of hard core SQL Server stored procedures and to the NextRecordset method of ADO's recordset object... Before we go on, this requires SQL Server. WON'T WORK WITH ACCESS.
That said, the goal for this example was easy... Get a list of all current tables in the database created by your's truly, get a list of dependent stored procedures for each table and do it all in a stored procedure! Easy...
There are basically 2 parts to this example. The calling application (a VBScript class called SQLServerTools) and the SQL Server stored procedure: sp_Admin_ExampleTablesDependencies (included with the class). The VBScript class is easy and contains only 1 method. That method is a great example of how to use the NextRecordset method of ADO to capture a second (or 50th) recordset returned by a procedure. For example, did you know this was valid SQL:
"SELECT * FROM table1;SELECT * FROM table2;"
Well it is, SQL Server knows that this statement should produce two separate and distinct recordsets however if you execute that and don't use NextRecordset, you will only get that first RS back. The stored procedure I wrote to do all the work is the second part of the example. That procedure returns (2 * num of tables) recordsets which is variable and based on the number of tables found in sysobjects for your particular db. Bottom line, that procedure always returns more than 1 recordset.
Right now, as you read this, I estimate that the procedure returned as many as 30 recordsets that were looped through with NextRecordset... But that's not the real magic. The class only exists to call the procedure and work with it's results. The stored procedure itself does all the work.
I've commented both the app and the stored procedure to the max but I'll give you an overview of the stored procedure... The stored procedure uses a cursor to loop through the records of a recordset returned by the system table: sysobjects. The process used is almost the same as using ADO to manipulate a recordset but right in the db itself... this means huge speed bonus and, by writing a stored procedure, all that logic is forever contained within and isn't spilled out into the class where it doesn't belong.
So anyways, while the procedure is looping through each table returned by sysobjects, it's simultaneously calling the system stored procedure: sp_depends. sp_depends is a handy function that returns a recordset of names representing every stored procedure that references the entered table! sp_depends can be used on any object, not just tables, but that is another discussion for another day... ASP > Tips and Tutorials > Database-related Oct 10, 2006
That said, the goal for this example was easy... Get a list of all current tables in the database created by your's truly, get a list of dependent stored procedures for each table and do it all in a stored procedure! Easy...
There are basically 2 parts to this example. The calling application (a VBScript class called SQLServerTools) and the SQL Server stored procedure: sp_Admin_ExampleTablesDependencies (included with the class). The VBScript class is easy and contains only 1 method. That method is a great example of how to use the NextRecordset method of ADO to capture a second (or 50th) recordset returned by a procedure. For example, did you know this was valid SQL:
"SELECT * FROM table1;SELECT * FROM table2;"
Well it is, SQL Server knows that this statement should produce two separate and distinct recordsets however if you execute that and don't use NextRecordset, you will only get that first RS back. The stored procedure I wrote to do all the work is the second part of the example. That procedure returns (2 * num of tables) recordsets which is variable and based on the number of tables found in sysobjects for your particular db. Bottom line, that procedure always returns more than 1 recordset.
Right now, as you read this, I estimate that the procedure returned as many as 30 recordsets that were looped through with NextRecordset... But that's not the real magic. The class only exists to call the procedure and work with it's results. The stored procedure itself does all the work.
I've commented both the app and the stored procedure to the max but I'll give you an overview of the stored procedure... The stored procedure uses a cursor to loop through the records of a recordset returned by the system table: sysobjects. The process used is almost the same as using ADO to manipulate a recordset but right in the db itself... this means huge speed bonus and, by writing a stored procedure, all that logic is forever contained within and isn't spilled out into the class where it doesn't belong.
So anyways, while the procedure is looping through each table returned by sysobjects, it's simultaneously calling the system stored procedure: sp_depends. sp_depends is a handy function that returns a recordset of names representing every stored procedure that references the entered table! sp_depends can be used on any object, not just tables, but that is another discussion for another day... ASP > Tips and Tutorials > Database-related Oct 10, 2006
Write a Review
Add to My Favorite
Refer it to Friend
Report Broken Link
| |
|
||
| Bookmark SQLServerTools Object: |
|||
Other links at ASP > Tips and Tutorials > Database-related
Here is the simpliest way of deleting duplicate items in a mysql database that i know of. As far a i know mysql have yet to develop a delete duplicate function.
Category:
You can use the "Compact and Repair" function in Access from ASP code. The following code is an example of how this can be done. Note that when you decide to "Compact and Repair" your Access database, some autonumbers can be changed. Access makes all autonumbers consecutive.
This code uses one database, but I'm sure the code can easily be changed so that the listbox displays, for example, all the databases in one folder
This code uses one database, but I'm sure the code can easily be changed so that the listbox displays, for example, all the databases in one folder
Category:
Allowing multiple choices of data observations enables people to customize views of your data and adds value to your applications. The following example utilizes our db on the career statistics of Hammerin? Hank Greenberg. The years he played are available to select from in a dropdown menu. When multiple years are selected they are passed comma delimited. Utilizing the where in statement in our sql the comma delimited selections are appended to our sql statement to display only those years selected. Because we are looking for numeric values in our where in statement no modification to the comma delimited input is necessary. When character input is involved a replace statement can be used to put the appropriate quotation marks into your statement. Which is also provided in this example, but must be un-commented to be used
Category:
You can test the demo for dynamically dependent dropdown lists here:
http://www.jamesfarrow.com/codeexamples/Double_Listbox_Populated_From_DB/
http://www.jamesfarrow.com/codeexamples/Double_Listbox_Populated_From_DB/
Category:
Displaying specific subsets of data from a database is easy to accomplish in .asp. The following example utilizes our Access database of Mark "The Bird" Fidrych and displays all of his season statistics for 1976 contained in the database rather than displaying the entire db.
Category:
Main Category
194
2041
824
286
175
1802
1238
913
1163
7489
70
909
59
2222
174
Join Mailing List
Joining mailing list will entitle you
to receive occasional emails informing you of news and
updates to the site and any special offers that may be
of interest to you.
Top 10
Directory Statistics
Links: 19539
Categories: 1275
Registered Users: 750
Mailing List Subscribers: 2046
Unique Outgoing Hits: 228155
Pagerank Statistics
PHP News