Create File without xp_cmdShell

Question

Create File without xp_cmdShell

info 58414

Hall of Fame

Points: 3386
More actions
March 7, 2013 at 12:54 pm

#274970

Hello,
Is there a way to create a text file in the file system (Win) without xp_cmdshell / sp_configure?
Regards
Nicole 😉

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

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

Jared SSC Guru Points: 61793 More actions · Answer 1

We'll need a bit more information. I can assume you are trying to export a result set, but I don't like to assume. Simple answer... is yes.

Jared
CE - Microsoft

info 58414 Hall of Fame Points: 3386 More actions · Answer 2

The Last Statement in a Stored-Procedure is "Create File C:\tmp\trigger.txt in FileSystem".

This File is a TriggerFile for a Job. This Job ping every Minute ( if File exists then Start Processing a another Job).

That's all.

Regards

Nicole

😉

Jared SSC Guru Points: 61793 More actions · Answer 3

Well, in your case... Why not just have the procedure start the job? Other option is to execute an SSIS package, but that seems to be a lot of work for something so simple.

Jared
CE - Microsoft

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 4

A SQLCLR object can access the file system too, and it would be the lesser of two evils in this case, when compared to enabling xp_cmdshell, but still not ideal. Accessing the file system from T-SQL is just bad form in my opinion.

I like the idea of having the proc start a job, if in fact "the job" you mentioned is an Agent Job and you were not referring to some external process to SQL Server watching a directory for a file to arrive.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

From the sounds of it, this is something that I'd prefer to have monitored from the task scheduler or from the OS or a service that is fairly lightweight.

That said, you can create the file, and/or kick off a sql agent job if you chose to use Powershell instead.

Powershell is lightweight and seems like the right tool for this kind of requirement.

Here is a simple article on creating a file

http://blogs.technet.com/b/heyscriptingguy/archive/2012/08/24/powertip-create-an-ascii-file-from-inside-powershell.aspx

And here is one on running a SQL Agent job from a Powershell script

http://kevine323.blogspot.com/2012/01/starting-sql-agent-job-with-powershell.html

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events