File Handling A Different Way

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by shashank bhide » File Handling A Different Way

23 posts, Page 2 of 3

««1 2 3 »»

File Handling A Different Way

Rate Topic

Display Mode

Topic Options

Author

Message

rsconnolly

Posted Tuesday, April 22, 2008 6:50 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, April 15, 2013 1:07 PM
Points: 4, Visits: 73

I cannot read the sample code on the form. All I see is a horizontal scroll bar.

Post #488567

Anipaul

Posted Tuesday, April 22, 2008 6:53 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: 2 days ago @ 1:35 AM
Points: 4,789, Visits: 1,336

Hi Shashank

I felt something missing in the article. Overall the article seems incomplete. The process may not the best but you tried something different. :)

Post #488573

JMI

Posted Tuesday, April 22, 2008 7:13 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, June 28, 2011 12:06 PM
Points: 557, Visits: 775

Does it work for everybodey?
When I run it form QA it keeps creating an empty file.

exec master..xp_cmdshell 'echo "XXX-XXX" >> c:\code\QueryResults.txt'

Thanks,
Rob

Post #488590

Phil Factor

Posted Tuesday, April 22, 2008 7:29 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Sunday, May 19, 2013 4:00 AM
Points: 533, Visits: 2,285

There are a lot of uses for this technique. Here is an example from Nigel Rivett for doing FTP in a stored procedure
http://www.nigelrivett.net/FTP/s_ftp_GetFile.html
he also has a clever way of writing format files for BCP in the same way. Naturally, one wouldn't give the normal application user any access to this!.

Best wishes,

Phil Factor
Simple Talk

Post #488613

Your Name Here

Posted Tuesday, April 22, 2008 7:31 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 1:39 PM
Points: 130, Visits: 513

I have to agree with Phil. "New" is kind of misleading since any DBAs worth their paycheck knew about using xp_cmdshell about 15 minutes after it was introduced back around SQL 4.21 .

Given the very narrow constraint of reserving this file creation to the handful of people blessed with membership in the sysadmins role, used for specialty troubleshooting sessions and subsequent mandatory file cleanup I'd say the article's suggestion has merit. In my environments that contain HIPAA data, this would be enabled only after that Ghost Whisperer lady put me at peace by shoving me into "the light" because saying "over my dead body" wouldn't be enough.

Also, it's pretty shallow-minded to overlook that fact that while one execution of this command may seem harmless and convenient, I look at it from the fact that I have thousands of concurrent connections 7x24. Simply "appending to a file" can run my server out of space in a heartbeat if poor choices are made. In my humble opinion, [absolutely nothing] should consume space on my server's hard drives that I don't have control over.

Sound harsh? I'm the "nice guy." My security counterparts would have a field day. Shashank would get hit harder than a bag of jellybeans at a daycare.

Post #488616

timothyawiseman

Posted Tuesday, April 22, 2008 11:43 AM

Right there with Babe

Group: General Forum Members
Last Login: Thursday, February 14, 2013 12:01 PM
Points: 743, Visits: 900

There is nothing inherently wrong with doing file handling from within a T-SQL script when needed , and it is possible to set the security on the windows system and on the SQL Server in such a way that it would be just as secure as using .NET code. I have in fact used precisely the technique this article describes to create schema.ini files on an as needed basis in conjunction with using opendatasource to read text files on a non-production system.

While there are certain situations where this is a good technique though, it will not often be the best technique. Depending on the amount and type of information being processed there will very often be a better answer. If it is information the user will need immediately, it is probably better to return it immediately through whatever interface is used. If it is say a report meant to be picked up and read the script could be sent to e-mail it instead of leaving it in a file. If it is a large amount of information then something along the lines of DTS, SSIS, or BCP would be more appropriate.

The technique does have its place, but it is rarely the best one.

---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/

Post #488794

Dave Vroman

Posted Tuesday, April 22, 2008 2:03 PM

SSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 11:17 AM
Points: 46, Visits: 206

xp_cmdshell is such a dangerous extention that we've removed it from the SQL servers.
This sounds nice, but it is very dangerous for any web application.

Dave
Sr. Software Engineer - www.geeks.com

Post #488893

Tom Garth

Posted Wednesday, April 23, 2008 9:09 AM

SSC Eights!

Group: General Forum Members
Last Login: Friday, February 04, 2011 7:20 AM
Points: 977, Visits: 1,499

Samuel Clough (4/22/2008)

I'm surprised by this article. For one thing, as already stated, this stored proc should never be enabled. Secondly, why use a database to do file i/o? I wouldn't want someone writing files on my database server. It sorta smells like a hack.

To me ...

I hate infinitives.

Security is a separate issue that always must be dealt with, but there is nothing at all unsecure about using xp_cmdshell in your code.

The article is not in depth enough to make a judgement regarding whether or not using xp_cmdshell was the best method. However when weighing the pros and cons, one must always consider the maintainability of the code. It doesn't get much more maintainable than a stored procedure.

Tom Garth
Vertical Solutions

"There are three kinds of men. The one that learns by reading. The few who learn by observation. The rest of them have to pee on the electric fence for themselves." -- Will Rogers

Post #489367

Brandon Forest

Posted Wednesday, April 23, 2008 9:53 AM

Valued Member

Group: General Forum Members
Last Login: Yesterday @ 5:25 PM
Points: 60, Visits: 304

There are times when it is necessary to use xp_cmdshell, but this isn't one of them. As stated in a post above the real issue is securing credentials in the web config files. Using hashing functions is one approach that works there. Other approaches use the built in security of ASP.NET. Several good articles are:

Enhanced and Secure Connection Strings in Web.Config by Vasudevan Deepak Kumar
http://www.codeproject.com/KB/web-security/secure_connectionstrings.aspx

Security Features in ASP.NET - Authentication by Cynthia Carolina
http://www.asp101.com/articles/cynthia/authentication/default.asp

How to use the ASP.NET utility to encrypt credentials and session state connection strings.
http://support.microsoft.com/kb/329290

Brandon Forest

Post #489392

Mike DiRenzo

Posted Wednesday, April 23, 2008 4:20 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 12:11 PM
Points: 143, Visits: 183

Hmm, Xp_cmdShell huh? Hmmm. If it works, it works. Although....ah forget it. This one has been beat to pulp.

Thanks for the article.

-M

Post #489628

« Prev Topic | Next Topic »

23 posts, Page 2 of 3

««1 2 3 »»

Permissions