MS SQL

MS SQL RSS

Create stored procedure to make satic html content

Please support our MS SQL advertiser: Intel Parallel Studio Home

Thread Solved

•

Join Date: Jun 2009

Posts: 13

Reputation: fayola is an unknown quantity at this point

Solved Threads: 0

fayola

Offline

Newbie Poster

Create stored procedure to make satic html content

Jun 16th, 2009

hey,
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:

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
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
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?

•

Join Date: Apr 2006

Posts: 143

Reputation: cutepinkbunnies is an unknown quantity at this point

Solved Threads: 8

cutepinkbunnies cutepinkbunnies is offline

Offline

Junior Poster

Re: Create stored procedure to make satic html content

Jun 16th, 2009

This old function I made may help as it demonstrates the concepts of looping with SQL (without a yucky cursor!).

 Help with Code Tags 
 SQL Syntax (Toggle Plain Text) 
CREATE   function [dbo].[fn_Is_Uppercase]
    (@string VARCHAR(1000))
RETURNS BIT
as
 
BEGIN
	DECLARE @r BIT
	DECLARE @l INT
	DECLARE @i INT
 
	--trim it up
	SET @string = mrm.dbo.TRIM(@string)
 
	--need to check every character
	SET @l = len(@string)
	SET @i = 0
	SET @r = 1
 
	while @i <= @l + 1 AND @r = 1
		BEGIN
			if ASCII(SUBSTRING(@string,@i - 1, 1)) <> ASCII(UPPER(SUBSTRING(@string,@i - 1, 1)))
				BEGIN
					SET @r = 0
				END
			SET @i = @i + 1
		END
 
	return @r
END
CREATE   function [dbo].[fn_Is_Uppercase]
    (@string varchar(1000))
returns bit
as

begin
	declare @r bit
	declare @l int
	declare @i int
	
	--trim it up
	set @string = mrm.dbo.trim(@string)
	
	--need to check every character
	set @l = len(@string)
	set @i = 0
	set @r = 1
	
	while @i <= @l + 1 and @r = 1
		begin
			if ascii(substring(@string,@i - 1, 1)) <> ascii(upper(substring(@string,@i - 1, 1)))
				begin
					set @r = 0
				end
			set @i = @i + 1
		end

	return @r
end

This may not be the most efficient way to iterate through the characters, but it gets the job done.

I would suggest setting up a table variable with the columns you want, along with an identity (1,1) or (0,1) column to iterate through it. For each pass through the loop, write to a file in a given directory. You can check which directory/file to write to on each iteration and while properly incrementing the LCV.

Hope this helps.

Last edited by cutepinkbunnies; Jun 16th, 2009 at 7:59 pm.

•

Join Date: Jun 2009

Posts: 13

Reputation: fayola is an unknown quantity at this point

Solved Threads: 0

fayola

Offline

Newbie Poster

Re: Create stored procedure to make satic html content

Jun 18th, 2009

Thanks this helps a lot! Like you said I just need to set it to loop the set of records for each table type that will be mapped it it's own folder, so loop within a loop. Marking as solved!