Re: Table Contents Last Changed

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Patrick Fogarty (padraig_fogarty_at_spam.hotmail.no.com)
Date: 02/25/04 

Date: Wed, 25 Feb 2004 08:43:34 -0500

Baisong -

Many apologies for not responding sooner. I have been away.

I see what you're doing here and appreciate the example. The only problem
is that you're still saving something to the file system. When I said that
I wanted to bypass the file system, I didn't only mean the routines in .NET
that access it. I also meant that I NEVER want the content to touch the
file system in any way (recognizing the exception that a database is just a
big file.) By using your method, I still have to decide on a place to
store the files. I have to apply security. I have to make sure that
systems administrators can't access the directory, perhaps using EFS, etc.

I'm probably just stuck with having to deal with the file system issues of a
push or do a pull... at least until Yukon is here and I can use remoting or
a managed COM+ event to push the information directly in.

- Patrick

"Baisong Wei[MSFT]" <v-baiwei@online.microsoft.com> wrote in message
news:dGAJZNu7DHA.3736@cpmsftngxa07.phx.gbl...
| Hi Patrick,
|
| Thank you for your update.
|
| First, I just want to say that all the work should be based on specific
| situation and the workload of the project. As the PUSH method I and Aaron
| suggested, when a change is make to the content in the SQL Server table,
it
| push the content out and replace the old one. It has many advantages when
| the content is less modified by frequently visited; As for PULL method, if
| the file is less frequently visited but the modification frequency is
| relatively more. That is between the file's sequent visit, maybe it has
| been changed serveral times, and the PULL method will have less traffic
| than PUSH. It is a question of balance.
|
| As for your question of how to push and bypass the file system. There is a
| method I always use to save my files and pictures in the SQL Server and
| output them into the file system. That is, by using testcopy.exe and
| xp_cmdshell.
|
|
| 1) Create a stored procedure that you use to copy files into a image
column
| in a table.
|
| if exists (select * from dbo.sysobjects where id =
| object_id(N'[dbo].[sp_textcopy]') and OBJECTPROPERTY(id, N'IsProcedure') =
| 1)
| drop procedure [dbo].[sp_textcopy]
| GO
|
| CREATE PROCEDURE sp_textcopy
| @srvname varchar (30), --Server Name
| @login varchar (30), --Login Name
| @password varchar (30), --password
| @dbname varchar (30), --Database Name
| @tbname varchar (30), --Table Name
| @colname varchar (30), --Name of the column to save the large object
| @filename varchar (30), --Name of the file to save into SQL Server
| @whereclause varchar (40), --criteria
| @direction char(1) --I: save to database, O: read it out
| AS
| DECLARE @exec_str varchar (255)
| SELECT @exec_str =
| 'c:\textcopy /S ' + @srvname +
| ' /U ' + @login +
| ' /P ' + @password +
| ' /D ' + @dbname +
| ' /T ' + @tbname +
| ' /C ' + @colname +
| ' /W "' + @whereclause +
| '" /F ' + @filename +
| ' /' + @direction
| EXEC master..xp_cmdshell @exec_str
|
| 2) create a table to save it
| Use testbak
| Go
| create table tablex(bid int, bcontent image)
| insert tablex values(1,0x)
| insert tablex values(2,0x)
| go
|
| NOTE: The textcopy.exe is located at C:\Program Files\Microsoft SQL
| Server\MSSQL\Binn, please copy to c:\ for this test
|
|
| 3) run the stored procedure sp_textcopy to save c:\c.bmp to SQL Server
| sp_textcopy
|
'baiwei','sa','your_password','testbak','tablex','bcontent','c:\c.bmp','wher
| e bid=1','I'
| sp_textcopy
|
'baiwei','sa','your_password','testbak','tablex','bcontent','c:\c.doc','wher
| e bid=2','I'
|
| Again, every decision is make by your specific situation. I thinks there
| could be other ways for you to use the xp_cmdshell, I just shell the one I
| with you for you reference. Hope this helps. Thanks
|
| Best regards
|
| Baisong Wei
| Microsoft Online Support
| ----------------------------------------------------
| Get Secure! - www.microsoft.com/security
| This posting is provided "as is" with no warranties and confers no rights.
| Please reply to newsgroups only. Thanks.
|

Relevant Pages

  • Re: receive ckdsk error upon startup
    ... Type of file system is NTFS ... To skip, push any key. ... This should bring up the Dell Diagnostics where you can test your hard disk ...
    (microsoft.public.windowsxp.help_and_support)
  • Re: Filesystems
    ... > don't know quite where to look in regards of a push of "what needs ... > to be done" to make a file system. ... What did the distributed filesystem you used fail to provide? ... Do any of the other filesystems fail to provide that feature? ...
    (comp.unix.bsd.freebsd.misc)
  • Re: commiting the transaction log?
    ... How much data is stored in memory ... As long as SQL Server has a file open for a long time, ... Is that the reason for the log file (in the file system) having time ... But even if I run the FULL backup, and then the T-log backup, I can see ...
    (microsoft.public.sqlserver.server)
  • Re: Atachments in File System
    ... If you can afford MOSS 2007 you can afford to use SQL Server. ... If you are looking for the cheapest solution, use WSS 3.0 with its built-in ... I think he wants MOSS 2007 to store attachments in the file system - ...
    (microsoft.public.sharepoint.portalserver)
  • Re: Blob in Access
    ... runs down each file mentioned in the document table verifying ... 2a) It might be nice to store date/time and size of file to see if ... How about SQL Server? ... becomes A451O3 - and stored on the server file system. ...
    (comp.databases.ms-access)