This is the mini project that I undertook to use SQL Server to manipulate the data for my web site. With little modification, this can be used to do the same for you! The contents of the files (SQL scripts, CMD scripts, VFP programs) are stored in SQL Server tables. This enabled me to use the Web Assistant jobs to generate the required web pages. The template files use the FrontPage themes so that it is easy for me to modify any particular attribute later using FrontPage. I couldn't use just MS Index Services 2.0 to get the directory / file information because I wanted to preserve the case of the file names / directories. I couldn't find any setting that enabled me to do this. Anyway I finally ended up using the DIR commands. If you have any suggestions / questions, please send an email.
Hope you find these useful!
Click here to download the SQL scripts.
1) This script demonstrates how to use MS Index Services to do distributed queries against my resources. I didn't use this mainly b'cos the case of directories, filenames was not preserved.
2) This small script shows how to enumerate directories, files using NT DIR commands. This was my initial script & i moved this logic to a SP now.
3) This is the main table that stores all my web content. I plan to add more statistics to this table for each file. Right now this contains the directory, filename, description, contents, created, last modified datetime values.
4) This SP formats the contents of each file & copies them over to the table.
5) This SP is used to generate the HTML pages for each URL under my "Resources" page & the links to the scripts. A batch file for zipping all the source code into the necessary directories is also generated by this program.
6) This SP can be used to update the data in this table. If executed without parameters, this just reports the files that are new / modified / deleted from my directories.
7) This is the main template used to generate all the HTML pages under my "Resources" link. I format the pages using FrontPage again to add say configure the navigation bars or apply themes etc.
11) This view is used to read the required directories, files for my web site. The distributed query is performed against the MS Index Service 2.0 catalog on my machine.
11) Just a wrapper SP for the CD database views. Will add more functionality to this.
11) This is the view for my Windows 2000 Deluxe CD player music database. This displays all my CD titles.
11) This is the view for my Windows 2000 Deluxe CD player music database. This displays all my CD tracks.
12) This script adds the linked server for the MS Index Services catalog.
13) This script adds the linked server to the Windows 2000 Deluxe CD database. You can use this script on any WIN2K m/c with SQL Server after modifying the path of the database.
14) This template is used to display the T-SQL scripts.
16) This template file is used by the Web Assistant job to display the CMD Script source code under my "CMD Scripts" page.
16) This template is used to display the VFP programs.
17) This SP is used to generate the HTML pages for my books database. This SP uses the FrontPage templates and SQL Server Web Assistant jobs.
18) This template file is used to generate the content for each book category.
19) This template file is used to generate the TOC for the books page.
20) This table contains the list of authors for my books database.
22) This table contains the categories for my books.
22) This table contains a list of all my books.
23) This table contains the list of artists from my CD collection & comments on each one.
25) This table maintains a list of titles from the Windows 2000 DeluxeCD database. Used to generate web pages.
25) This table contains the music categories.
26) This SP refreshes the artist data from the Windows 2000 DeluxeCD database.
27) This SP refreshes the title list from the Windows 2000 DeluxeCD database.
28) This SP refreshes the URLs for the artists from a HTML file. The HTML file contains the bookmarks exported from IE.
29) This table contains my URLs for the music groups.
30) This SP is used to generate the HTML pages for my CDs database. The CD titles are retrieved from the Windows 2000 DeluxeCD player database. This SP uses the FrontPage templates and SQL Server Web Assistant jobs.
32) This template file is used to generate the content for each link in my music page.
32) This template file is used to generate the TOC for the CDs / My Music page.
33) This table contains the settings used in the SPs that generates the HTML pages. It is easy to change say the directory or filenames etc from this table. The SPs can work without any modification too.
|
