SQL Server Performance Forum – Threads Archive

Files and Folders on server

Hallo , i am working in enterprise manager. Is it possible to make with some files in folder from this with t-sql or stored procedure?
Thanks , i apologize my english Lubo

---

CREATE PROCEDURE usp_UseOA (
@File varchar(1000)
, @Str varchar(1000)
)
AS
DECLARE @FS int
, @OLEResult int
, @FileID int EXECUTE @OLEResult = sp_OACreate
‘Scripting.FileSystemObject’
, @FS OUT IF @OLEResult <> 0 BEGIN
PRINT
‘Error: Scripting.FileSystemObject’
END — Opens the file specified by the @File input parameter
execute @OLEResult = sp_OAMethod
@FS
, ‘OpenTextFile’
, @FileID OUT
, @File
, 8
, 1
— Prints error if non 0 return code during sp_OAMethod OpenTextFile execution
IF @OLEResult <> 0
BEGIN
PRINT ‘Error: OpenTextFile’
END — Appends the string value line to the file specified by the @File input parameter
execute @OLEResult = sp_OAMethod
@FileID
, ‘WriteLine’
, Null
, @Str
— Prints error if non 0 return code during sp_OAMethod WriteLine execution
IF @OLEResult <> 0
BEGIN
PRINT ‘Error : WriteLine’
END EXECUTE @OLEResult = sp_OADestroy @FileID
EXECUTE @OLEResult = sp_OADestroy @FS
Execution Script DECLARE
@file VARCHAR(255)
, @i INT SET @i = 1
SET @file = ‘c: est5.csv’ WHILE @i <= 100000
BEGIN
— executes this stored procedure for each @i value
EXEC SE..usp_UseOA @file, @i
SET @i = @i + 1
END

---

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/script56/html/jsobjFileSystem.asp will gove you all the other methods to create delete files and folders

---

Do you want to create files in a folder? Dinesh, Does your procedure open the file and write text on that?
If so, why cant we use echo? exec master..xp_cmdshell ‘echo Sample text >> d: est.txt’ Madhivanan Failing to plan is Planning to fail

---

Will also work. However FileSystemObject is used in most cases to fulfill the files and folders creation deletion issues

---

]]>