Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

File Handling A Different Way Expand / Collapse
Author
Message
Posted Tuesday, April 22, 2008 6:50 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, March 13, 2014 1:33 PM
Points: 4, Visits: 81
I cannot read the sample code on the form. All I see is a horizontal scroll bar.
Post #488567
Posted Tuesday, April 22, 2008 6:53 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 4:30 AM
Points: 5,344, Visits: 1,388
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
Posted Tuesday, April 22, 2008 7:13 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr 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
Posted Tuesday, April 22, 2008 7:29 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 5:03 AM
Points: 579, Visits: 2,520
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
Posted Tuesday, April 22, 2008 7:31 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 1:27 PM
Points: 139, Visits: 617
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
Posted Tuesday, April 22, 2008 11:43 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, April 4, 2014 4:40 PM
Points: 751, Visits: 917
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
Posted Tuesday, April 22, 2008 2:03 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 3:39 PM
Points: 150, Visits: 615
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
Posted Wednesday, April 23, 2008 9:09 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, February 4, 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
Posted Wednesday, April 23, 2008 9:53 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 11:13 AM
Points: 65, Visits: 366
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
Posted Wednesday, April 23, 2008 4:20 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, February 25, 2014 11:28 AM
Points: 145, Visits: 193
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 »

Add to briefcase ««123»»

Permissions Expand / Collapse