January 13, 2003 at 11:28 am
Is it possible for SQL Server to access the file system and create or modify text files?
I have a unique problem where I need to open a text file and modify it on a remote server using SQL Server if this is possible. Any help would be greatly appreciated.
January 13, 2003 at 11:38 am
You may try to use the OLE Automation stored procedures to create and use an SQL-DMO SQLServer object for this kind of purpose. See BOL for details.
January 13, 2003 at 12:10 pm
Without proper access, no. You might try several things but there has to be sufficient rights on the other end to access the text file.
January 13, 2003 at 12:13 pm
OK, getting the appropriate rights is not a problem. How would I handle creating a trigger for an update to wite out a long text value to a specified file?
January 13, 2003 at 12:34 pm
Ok, first I would like to get the details of what you are trying to do to make sure you are getting what you need and limit some performance areas you may impact within a trigger. Keep in mind that if you wrtie to the same file you could potentially lock causing a cascade of failures after that point, plus the trigger must complete before the record is commited so I am concerned with what may happen and would like to help make sure we help you as best as possible to avoid common mistakes.
January 13, 2003 at 12:55 pm
1. Currently the asp on the local server reads and writes local files, would prefer to update the database and have SQL create/update the text files to the local drive (these are basic configuration files), only one use at a time so should not have locking problems.
2. A trigger should be appropriate because it only needs to update the .txt file when the record is updated, updates would be small and infrequent, and change would basically replace the existing file. We need to be able to specify the directory and file name to create, the source would simply be one long text field.
3. Once the file is updated, the asp would re-query the record from SQL to show the updated data.
January 16, 2003 at 1:05 am
quote:
try File System Object in Stored Procedurei used File System Object to Move one folder to another location with some different name. you can use the "OpenTextfile" method of file system object to write in a file.
DECLARE @FSO int
DECLARE @hr int
EXEC @hr = sp_OACreate 'Scripting.FileSystemObject', @FSO OUT
Print @Hr
select @OldName = 'C:\ABC'
select @NewName = 'C:\XYZ'
EXEC @hr = sp_OAMethod @FSO, MOVEFolder, null, @OldName, @NewName
PRINT @HR
EXEC @hr = sp_OADestroy @FSO
Good Luck
January 16, 2003 at 6:52 am
there is an extended stored procedure that is available on this sight that will let you update text files
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply