first off I'd like to state that I am pretty new to ms sql and stored procedures tho I have worked with sql for quite some time.
My employer would like me to write a script which takes html content from a mssql db (originally delivered dynamically) and create static pages from them and also would like those tables locked until the script is run again. Now not sure if this will (should) fly because it may be limiting the admins to make changes to their content only during specific times.
But nonetheless, it has been requested. I just found out that ms sql can have access to the servers filesystem, which is cool because the web site and mssql are hosted on the same server.
Now I came a across a simple stored procedure that writes contents to a specified file:
CREATE PROCEDURE sp_OutputtoFile(@FileName varchar(255), @Text1 varchar(255)) AS DECLARE @FS int, @OLEResult int, @FileID int EXECUTE @OLEResult = sp_OACreate 'Scripting.FileSystemObject', @FS OUT IF @OLEResult <> 0 PRINT 'Scripting.FileSystemObject' --Open a file execute @OLEResult = sp_OAMethod @FS, 'OpenTextFile', @FileID OUT, @FileName, 8, 1 IF @OLEResult <> 0 PRINT 'OpenTextFile' --Write Text1 execute @OLEResult = sp_OAMethod @FileID, 'WriteLine', Null, @Text1 IF @OLEResult <> 0 PRINT 'WriteLine' EXECUTE @OLEResult = sp_OADestroy @FileID EXECUTE @OLEResult = sp_OADestroy @FS
What I would like is to have content from certain tables in their own folder and have an html file for each record in the tables. So for instance, say I have five tables, Article, Resource, Advice, Job, Company, then I would like to create folders (or goto predefined folders) for each table and have files for each record so that it's labled with it's primary key ID. So in the Articles folder I'd like to have article_1234.html, article_1235.html, article_1236.html ....
I'm sure you get the just of it. Is this possible with a stored procedure and if so, can anyone post some sample code I can use as a base?