Creating a folder and a txt file in T-SQL without usign xp_cmdshell

Question

Creating a folder and a txt file in T-SQL without usign xp_cmdshell

pmscorca

SSCrazy

Points: 2349
More actions
November 11, 2010 at 4:17 am

#231231

Hi.
I need to create a (shared) folder and a txt file in T-SQL 2005 without using xp_cmdshell for security reasons.
To create the folder I can use xp_create_subdir, that I have tested with success. For creating a txt file, please?
Many thanks for your suggests

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

Since it looks like you're using SQL 2005, based on the forum you posted in, the easiest and most secure way to do that is via a CLR stored procedure. Much more secure than xp_cmdshell.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

OTF SSCertifiable Points: 6104 More actions · Answer 2

/*

* You will find a very simple example of creating a folder and file via the sql clr below.

*

* please note the following:

*

* There is no error handling included.

* You will need to ensure that the clr is enabled for use in your environment (sp_configure).

* You may need elevated privileges to create files/folders in the area of your choice.

* You should explore the System.IO .net library further which contains the classes for file/folder manipulation.

*/

-- Use Visual Studio or whichever editor you prefer to create the following CLR Procedure:

public static void CreateFolderAndFile(string folder, string file)

{

// Create a Folder

Directory.CreateDirectory(folder);

// Create and Write to a Text File in the new Folder

using (StreamWriter sw = new StreamWriter(folder + "\\" + file, false))

{

sw.Write("This text has been written as a result of a clr call from sql server");

}

-- Deploy the Assembly via VS or use the TSQL Commands below:

CREATE ASSEMBLY CLR_Project

FROM 'drive:\path to Assembly\Assembly.dll'

WITH PERMISSION_SET = EXTERNAL_ACCESS;

GO

-- Create the following Procedure within Sql Server

CREATE PROCEDURE dbo.CreateFolderAndFile

(

@Folder NVARCHAR(100),

@File NVARCHAR(50)

)

AS EXTERNAL NAME AssemblyName.ClassName.CreateFolderAndFile;

GO

-- Test it by running the code below:

EXEC CreateFolderAndFile 'folder', 'file.txt';

/*

*-- Clean Up

DROP PROCEDURE CreateFolderAndfile;

DROP ASSEMBLY AssemblyName

*/

Hopefully, this might help. 🙂

WayneS SSC Guru Points: 95460 More actions · Answer 3

You could use the sp_OA procedures, with "Scripting.FileSystemObject", to create files.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

pmscorca SSCrazy Points: 2349 More actions · Answer 4

I think that create a CLR procedure isn't the easiest solution and requires VSTO programming. I would like using T-SQL language.

Thanks

GSquared SSC Guru Points: 260824 More actions · Answer 5

WayneS (11/11/2010)
You could use the sp_OA procedures, with "Scripting.FileSystemObject", to create files.

Part of the reason CLR was added into SQL 2005 is that the sp_OA procedures are a pain to use, and don't clean up well after themselves (resulting in system instability being a frequent issue).

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

GSquared SSC Guru Points: 260824 More actions · Answer 6

pmscorca (11/11/2010)
I think that create a CLR procedure isn't the easiest solution and requires VSTO programming. I would like using T-SQL language.
Thanks

T-SQL doesn't have a file creation command for general files. You can create a database or log file with it, but not a general file such as a text file.

SSIS can create files for you. Would you prefer that over CLR or command-line?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon