File output

I have read an Oracle article about the content management system they implemented, and was wondering whether a similar thing was possible with SQL Server.

The system basically stored the content of articles etc. in the database, as well as revisions etc., a stored procedure then generated an html page from the database content, my guess is that this then updated any necessary pages containing links etc.

My question is basically whether SQL Server (or T-SQL to be more precise) includes the ability to output the contents of fields into a file, so that when an SP was called it would generate the HTML for a page and output it to a file on the server. Thus avoiding the need to generate a page every time a request is received.

Thanks,
PaulI can think of a few ways to do this. I don't know of anything built in to SQL Server to handle this.

Do you want to call the sp from within the SQL server or from the command line?

Do you want the ability to (re)build only one file at a time or rebuild all files?

Do you have any metadata on the file names and locations?

Post back or "E" me directly.|||The idea would be that the stored procedure could be called when a record is inserted, thus when someone writes content it then creates the HTML page.

My guess is that there would also be the need to then update any linked pages, so if you wanted a link on the home page, the home page would also be re-written.

Anyway, it was nothing more than a thought, and was curious as to how SQL Server might support this kind of functionality.

Paul

file names in backup and restore output

I have an isql script that runs and does a full dump of the database - I
use a -i to indicate the input file and a -o for the output file -
everything works fine - but when I look at the output file - I was
expecting to see the name of the dump file that I just created ( like in
Sybase ) but it is not there - and the same is true with the load
process - in the output from the restore there is no mention of the file
name that was used as input for the restore - since these run at night it is
important to be able to look at the output and know if the right file was
used - I'm hoping there is a parameter that I need to set in order for the
file info. to be included in the output - ?
thanks,
--
JohnWhat version of SQL Server? ISQL is a really old tool which was superseded by OSQL.EXE in 7.0 and
now SQLCMD.EXE in 2005. Anyhow, check your the -e switch of ISQL.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"John" <John@.discussions.microsoft.com> wrote in message
news:83513629-DBFC-4F69-82FD-5E62F0659BD4@.microsoft.com...
>I have an isql script that runs and does a full dump of the database - I
> use a -i to indicate the input file and a -o for the output file -
> everything works fine - but when I look at the output file - I was
> expecting to see the name of the dump file that I just created ( like in
> Sybase ) but it is not there - and the same is true with the load
> process - in the output from the restore there is no mention of the file
> name that was used as input for the restore - since these run at night it is
> important to be able to look at the output and know if the right file was
> used - I'm hoping there is a parameter that I need to set in order for the
> file info. to be included in the output - ?
> thanks,
> --
> John|||This may help ? it a bit rough but you can tydy it up
-----
CREATE procedure [sp_dba_backupinfo]
@.DBName varchar(100) = '%'
AS
IF @.DBName <> '%'
set @.DBName = @.DBName+'%'
select
substring(database_name,1,24) as DATABASE_NAME,
right(backup_start_date,19) as BACKUP_START,
right(backup_finish_date,19) as BACKUP_FINISH,
--round(backup_size/1024/1024,0) as MB,
str(backup_size/1024/1024,6,0) as MB,
position,
substring(logical_device_name,1,24) as LOGICAL_DEVICE,
substring(physical_device_name,1,100) as PHYSICAL_DEVICE
from msdb..backupmediafamily a, msdb..backupset b
where a.media_set_id = b.media_set_id
--and database_name='lamda1p_interfaces_store'
and database_name like @.DBName
--database_name in ('lamdace','metadatace','security')
--and backup_start_date > '2003-09-01 01:00:00.000'
--and logical_device_name = 'lamda1p_interfaces_store'
--and physical_device_name not like '\\.%'
--and physical_device_name like 'Ba%'
--order by database_name,backup_start_date desc
order by database_name asc,backup_start_date desc
-----
CREATE procedure [sp_dba_restoreinfo]
@.DBName varchar(100) = '%'
AS
IF @.DBName <> '%'
set @.DBName = @.DBName+'%'
--select @.DBName
SET NOCOUNT ON
/*
select --xtype,
'msdb.dbo.'+name from msdb.dbo.sysobjects
where
name like '%backup%' and xtype in ('U','P')
or name like '%restore%' and xtype in ('U','P')
order by 1 desc
*/
SELECT
RH.[restore_type],
CONVERT(char(20),RH.[restore_date],120) [restore_date],
CONVERT(char(20),BS.[database_creation_date],120) [database_creation_date],
CONVERT(char(20),BS.[backup_start_date],120) [backup_start_date],
CONVERT(char(20),BS.[backup_finish_date],120) [backup_finish_date],
right(BS.[machine_name],12) [machine_name],
--right(BS.[server_name],12) [server_name],
right(BS.[database_name],35) [database_name],
right(RH.[destination_database_name],35) [destination_database_name],
str(BS.backup_size/1024/1024,6,0) as MB,
--RF.[restore_history_id],
--RF.[file_number],
--RF.[destination_phys_drive],
--RH.[restore_history_id],
--RH.[user_name],
--RH.[backup_set_id],
--RH.[replace],
--RH.[recovery],
--RH.[restart],
--RH.[stop_at],
--RH.[device_count],
--RFG.[restore_history_id],
--RFG.[filegroup_name],
--BS.[backup_set_id],
--BS.[backup_set_uuid],
--BS.[media_set_id],
--BS.[first_family_number],
--BS.[first_media_number],
--BS.[last_family_number],
--BS.[last_media_number],
--BS.[catalog_family_number],
--BS.[catalog_media_number],
--BS.[position],
--BS.[expiration_date],
--BS.[software_vendor_id],
--BS.[name],
--BS.[description],
--BS.[user_name],
--BS.[software_major_version],
--BS.[software_minor_version],
--BS.[software_build_version],
--BS.[time_zone],
--BS.[mtf_minor_version],
--BS.[first_lsn],
--BS.[last_lsn],
--BS.[checkpoint_lsn],
--BS.[database_backup_lsn],
--BS.[type],
--BS.[sort_order],
--BS.[code_page],
--BS.[compatibility_level],
--BS.[database_version],
right(RF.[destination_phys_name],45) [destination_phys_name],
+'End'
from
msdb.dbo.restorefile RF,
msdb.dbo.restorehistory RH,
msdb.dbo.restorefilegroup RFG,
msdb.dbo.backupset BS
--msdb.dbo.backupmediaset
--msdb.dbo.backupmediafamily
--msdb.dbo.backupfile
where RF.restore_history_id = RH.restore_history_id
and RH.restore_history_id = RFG.restore_history_id
and RH.backup_set_id = BS.backup_set_id
--and RH.restore_date > getdate() -31
and RH.destination_database_name like @.dbname
order by restore_date desc
-----
"Tibor Karaszi" wrote:
> What version of SQL Server? ISQL is a really old tool which was superseded by OSQL.EXE in 7.0 and
> now SQLCMD.EXE in 2005. Anyhow, check your the -e switch of ISQL.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "John" <John@.discussions.microsoft.com> wrote in message
> news:83513629-DBFC-4F69-82FD-5E62F0659BD4@.microsoft.com...
> >I have an isql script that runs and does a full dump of the database - I
> > use a -i to indicate the input file and a -o for the output file -
> > everything works fine - but when I look at the output file - I was
> > expecting to see the name of the dump file that I just created ( like in
> > Sybase ) but it is not there - and the same is true with the load
> > process - in the output from the restore there is no mention of the file
> > name that was used as input for the restore - since these run at night it is
> > important to be able to look at the output and know if the right file was
> > used - I'm hoping there is a parameter that I need to set in order for the
> > file info. to be included in the output - ?
> >
> > thanks,
> >
> > --
> > John
>

file names in backup and restore output

I have an isql script that runs and does a full dump of the database - I
use a -i to indicate the input file and a -o for the output file -
everything works fine - but when I look at the output file - I was
expecting to see the name of the dump file that I just created ( like in
Sybase ) but it is not there - and the same is true with the load
process - in the output from the restore there is no mention of the file
name that was used as input for the restore - since these run at night it i
s
important to be able to look at the output and know if the right file was
used - I'm hoping there is a parameter that I need to set in order for the
file info. to be included in the output - ?
thanks,
JohnWhat version of SQL Server? ISQL is a really old tool which was superseded b
y OSQL.EXE in 7.0 and
now SQLCMD.EXE in 2005. Anyhow, check your the -e switch of ISQL.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"John" <John@.discussions.microsoft.com> wrote in message
news:83513629-DBFC-4F69-82FD-5E62F0659BD4@.microsoft.com...
>I have an isql script that runs and does a full dump of the database - I
> use a -i to indicate the input file and a -o for the output file -
> everything works fine - but when I look at the output file - I was
> expecting to see the name of the dump file that I just created ( like in
> Sybase ) but it is not there - and the same is true with the load
> process - in the output from the restore there is no mention of the file
> name that was used as input for the restore - since these run at night it
is
> important to be able to look at the output and know if the right file was
> used - I'm hoping there is a parameter that I need to set in order for t
he
> file info. to be included in the output - ?
> thanks,
> --
> John|||This may help ? it a bit rough but you can tydy it up
----
--
CREATE procedure [sp_dba_backupinfo]
@.DBName varchar(100) = '%'
AS
IF @.DBName <> '%'
set @.DBName = @.DBName+'%'
select
substring(database_name,1,24) as DATABASE_NAME,
right(backup_start_date,19) as BACKUP_START,
right(backup_finish_date,19) as BACKUP_FINISH,
--round(backup_size/1024/1024,0) as MB,
str(backup_size/1024/1024,6,0) as MB,
position,
substring(logical_device_name,1,24) as LOGICAL_DEVICE,
substring(physical_device_name,1,100) as PHYSICAL_DEVICE
from msdb..backupmediafamily a, msdb..backupset b
where a.media_set_id = b.media_set_id
--and database_name='lamda1p_interfaces_store'
and database_name like @.DBName
--database_name in ('lamdace','metadatace','security')
--and backup_start_date > '2003-09-01 01:00:00.000'
--and logical_device_name = 'lamda1p_interfaces_store'
--and physical_device_name not like '\\.%'
--and physical_device_name like 'Ba%'
--order by database_name,backup_start_date desc
order by database_name asc,backup_start_date desc
----
--
CREATE procedure [sp_dba_restoreinfo]
@.DBName varchar(100) = '%'
AS
IF @.DBName <> '%'
set @.DBName = @.DBName+'%'
--select @.DBName
SET NOCOUNT ON
/*
select --xtype,
'msdb.dbo.'+name from msdb.dbo.sysobjects
where
name like '%backup%' and xtype in ('U','P')
or name like '%restore%' and xtype in ('U','P')
order by 1 desc
*/
SELECT
RH.[restore_type],
CONVERT(char(20),RH.[restore_date],120) [restore_date],
CONVERT(char(20),BS.[database_creation_date],120) [database_creation
_date],
CONVERT(char(20),BS.[backup_start_date],120) [backup_start_date],
CONVERT(char(20),BS.[backup_finish_date],120) [backup_finish_date],
right(BS.[machine_name],12) [machine_name],
--right(BS.[server_name],12) [server_name],
right(BS.[database_name],35) [database_name],
right(RH.[destination_database_name],35) [destination_database_name]
,
str(BS.backup_size/1024/1024,6,0) as MB,
--RF.[restore_history_id],
--RF.[file_number],
--RF.[destination_phys_drive],
--RH.[restore_history_id],
--RH.[user_name],
--RH.[backup_set_id],
--RH.[replace],
--RH.[recovery],
--RH.[restart],
--RH.[stop_at],
--RH.[device_count],
--RFG.[restore_history_id],
--RFG.[filegroup_name],
--BS.[backup_set_id],
--BS.[backup_set_uuid],
--BS.[media_set_id],
--BS.[first_family_number],
--BS.[first_media_number],
--BS.[last_family_number],
--BS.[last_media_number],
--BS.[catalog_family_number],
--BS.[catalog_media_number],
--BS.[position],
--BS.[expiration_date],
--BS.[software_vendor_id],
--BS.[name],
--BS.[description],
--BS.[user_name],
--BS.[software_major_version],
--BS.[software_minor_version],
--BS.[software_build_version],
--BS.[time_zone],
--BS.[mtf_minor_version],
--BS.[first_lsn],
--BS.[last_lsn],
--BS.[checkpoint_lsn],
--BS.[database_backup_lsn],
--BS.[type],
--BS.[sort_order],
--BS.[code_page],
--BS.[compatibility_level],
--BS.[database_version],
right(RF.[destination_phys_name],45) [destination_phys_name],
+'End'
from
msdb.dbo.restorefile RF,
msdb.dbo.restorehistory RH,
msdb.dbo.restorefilegroup RFG,
msdb.dbo.backupset BS
--msdb.dbo.backupmediaset
--msdb.dbo.backupmediafamily
--msdb.dbo.backupfile
where RF.restore_history_id = RH.restore_history_id
and RH.restore_history_id = RFG.restore_history_id
and RH.backup_set_id = BS.backup_set_id
--and RH.restore_date > getdate() -31
and RH.destination_database_name like @.dbname
order by restore_date desc
----
--
"Tibor Karaszi" wrote:

> What version of SQL Server? ISQL is a really old tool which was superseded
by OSQL.EXE in 7.0 and
> now SQLCMD.EXE in 2005. Anyhow, check your the -e switch of ISQL.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "John" <John@.discussions.microsoft.com> wrote in message
> news:83513629-DBFC-4F69-82FD-5E62F0659BD4@.microsoft.com...
>